I am trying to avoid the "Permission denied" error message when a proc tries
to select records from one of the system tables in a SQL Server 2005 master
database.
Sepcifically I need to be able to capture the IP address of the calling user
which I can get from sys.dm_exec_connections.
I have struggled with this for some time and could not find anything
directly on point in MSDN. However, I was able to cobble something together
that "appears" to work as shown below.
I am hoping that someone who has specific experience with this issue might
be able to comment on my approach and point out any issues or deficiencies.
Thanks
Dave
/*
Creating a proc that can view server state
1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
2. Create user for special login in the User DB
3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
4. Create a cerificate in the User DB
5. Sign the proc with the cerificate
6. Create the cerificate from backup in the Master DB
7. Create an authorization login mapped to the certificate in the Master DB
8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
9. Grant EXECUTE perms on the proc to users in the User DB
*/
--0. Create a test database
USE master
CREATE DATABASE testcert
GO
--1. Create a special login and grant it VIEW SERVER STATE permissions in
Master DB
CREATE LOGIN myViewUser WITH PASSWORD = '!Pa55word!' ;
GO
USE master
GO
GRANT VIEW ANY DEFINITION TO myViewUser ;
GO
GRANT VIEW SERVER STATE TO myViewUser ;
GO
--
--2. Create user for special login in the User DB
USe testCert
GO
CREATE USER myViewUser FOR LOGIN myViewUser ;
GO
--
--3. Create proc that accesses system tables using "WITH EXECUTE AS" the
special user
IF object_id('myPROC') IS NOT NULL
DROP PROCEDURE myPROC
GO
CREATE PROCEDURE myPROC
WITH EXECUTE AS 'myViewUser'
AS
BEGIN
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
END
GO
--
--Test: cannot execute - The user does not have permission to perform this
action.
EXEC myPROC
--4. Create a cerificate in the User DB
CREATE CERTIFICATE myCERT
ENCRYPTION BY PASSWORD = '!Pa55word!'
WITH SUBJECT = 'Test cert',
EXPIRY_DATE = '12/05/2010';
GO
BACKUP CERTIFICATE myCERT TO FILE = 'myCERT.cer' ;
--5. Sign the proc with the cerificate
ADD SIGNATURE TO myPROC
BY CERTIFICATE myCERT
WITH PASSWORD = '!Pa55word!'
GO
--6. Create the cerificate from backup in the Master DB
USE master --Permissions at the server scope can only be granted when the
current database is master
CREATE CERTIFICATE myCert FROM FILE = 'myCERT.cer'
--7. Create an authorization login mapped to the certificate in the Master D
B
CREATE LOGIN myAuthLogin
FROM CERTIFICATE mycert ;
GO
--
--8. Grant AUTHENTICATE SERVER to the authorization login in the Master DB
-- WIEW permissions are server-level permissions, grant AUTHENTICATE SERVER
to certificate mapped login
GRANT AUTHENTICATE SERVER TO myAuthLogin
GO
--test
USE testCert
--I can execute proc
EXEC myPROC
--create some users
CREATE LOGIN testuser1 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser1 FOR LOGIN testuser1 ;
CREATE LOGIN testuser2 WITH PASSWORD = '!Pa55word!' ;
CREATE USER testuser2 FOR LOGIN testuser2 ;
--test
execute as login = 'testuser1'
select suser_name()
--cannot execute: EXECUTE permission denied on object 'myPROC', database
'testcert', schema 'dbo'.
EXEC myPROC
revert
--
--9. Grant EXECUTE perms on the proc to users in the User DB
GRANT EXECUTE ON myPROC TO testuser1
--test again
execute as login = 'testuser1'
select suser_name()
--now can execute
EXEC myPROC
revert
execute as login = 'testuser2'
select suser_name()
--but others cabnnnot until granted perms on the proc
EXEC myPROC
revert
--Clean UP
USE testCert
GO
DROP USER testuser1
DROP LOGIN testuser1
GO
DROP USER testuser2
DROP LOGIN testuser2
GO
DROP PROC myPROC
GO
DROP USER myViewUser
DROP LOGIN myViewUser
GO
DROP CERTIFICATE myCERT
GO
USE master
GO
DROP LOGIN myAuthLogin
DROP CERTIFICATE myCERT
GO
DROP DATABASE testcert
GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'xp_cmdshell', 1 ;
GO
RECONFIGURE ;
GO
--EXEC xp_cmdshell 'DIR "D:\SQLDATA\MSSQL.1\*.cer"' ;
--EXEC xp_cmdshell 'del "D:\SQLDATA\MSSQL.1\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\myCert.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
GO
EXEC sp_configure 'xp_cmdshell', 0 ;
GO
RECONFIGURE ;After further searching I found a cleaner way to accomplish what I need to
do. See example below.
Although I am not entirely sure why this line is necessary:
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
/*
Laurentiu Cristofor's blog
SQL Server 2005: procedure signing demo
http://blogs.msdn.com/lcris/archive.../15/429631.aspx
*/
create database demo;
use demo;
CREATE LOGIN alice WITH PASSWORD = '!Pa55word!' ;
CREATE USER alice FOR LOGIN alice
-- create a procedure that selects from sys.dm_exec_connections
create procedure sp_CreatePrincipal
AS
DECLARE @.ip Varchar(60)
SELECT @.ip=client_net_address
FROM sys.dm_exec_connections
WHERE session_id=@.@.spid
PRINT @.ip
go
-- now use this newly added procedure
-- to create a low privileged principal
EXEC sp_CreatePrincipal
-- we'll now want alice to be able to use the procedure and create new
principals
-- but without granting her directly the permissions
grant execute on sp_CreatePrincipal to alice;
-- verify that alice cannot create principals
execute as login = 'alice';
EXEC sp_CreatePrincipal
revert;
-- first, we'll need to create a database master key
create master key encryption by password = 'Apufe@.))%';
-- create a certificate to sign the procedure
create certificate certSignCreatePrincipal with subject = 'for signing
procedure sp_CreatePrincipal';
-- sign procedure sp_CreatePrincipal
add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
-- now that we signed the procedure, we can drop the private key
alter certificate certSignCreatePrincipal remove private key;
-- backup certificate to file; it will be used later to put the certificate
in master
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';
-- create and map a user to the certificate
--? create user u_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
-- create the same certificate in master now
use master;
create certificate certSignCreatePrincipal from file =
'certSignCreatePrincipal.cer';
-- create and map a login to the certificate
create login l_certSignCreatePrincipal from certificate
certSignCreatePrincipal;
GRANT VIEW SERVER STATE TO l_certSignCreatePrincipal;
-- we're done!
use demo;
-- check that the certificate in demo matches the one in master
select c.name
from sys.certificates c
JOIN master.sys.certificates mc ON c.thumbprint = mc.thumbprint;
-- verify that alice can now create principals
execute as login = 'alice';
sp_CreatePrincipal
revert;
-- cleanup
drop user u_certSignCreatePrincipal;
drop login l_certSignCreatePrincipal;
drop procedure sp_CreatePrincipal;
drop certificate certSignCreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;
EXEC sp_configure 'xp_cmdshell', 1 ;
RECONFIGURE ;
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\certSignCreatePrincipal.cer"' ;
EXEC xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\*.cer"' ;
use master;
drop certificate certSignCreatePrincipal;
drop database demo;|||Dave (Dave@.discussions.microsoft.com) writes:
> I am trying to avoid the "Permission denied" error message when a proc
> tries to select records from one of the system tables in a SQL Server
> 2005 master database.
> Sepcifically I need to be able to capture the IP address of the calling
> user which I can get from sys.dm_exec_connections.
> I have struggled with this for some time and could not find anything
> directly on point in MSDN. However, I was able to cobble something
> together that "appears" to work as shown below.
> I am hoping that someone who has specific experience with this issue
> might be able to comment on my approach and point out any issues or
> deficiencies.
Yes, you have to do something you did, although you don't the EXECUTE
AS thing. Certificate signing is all you need. I have an article on
my web site that show how you can do this:
http://www.sommarskog.se/grantperm.html
Since you seem to have grasped some of this already, you may want to
rush through until the first BULK INSERT example.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland Sommarskog.
That is a very useful web site at http://www.sommarskog.se/
However, one thing I am not clear on; why do you back up the private key and
Laurentiu drops it? What is the risk of dropping it like he did?
Erland Sommarskog's example:
BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
DECRYPTION BY PASSWORD = 'All you need is love')
Laurentiu Cristofor's example:
alter certificate certSignCreatePrincipal remove private key;
backup certificate certSignCreatePrincipal to file =
'certSignCreatePrincipal.cer';|||Dave (Dave@.discussions.microsoft.com) writes:
> That is a very useful web site at http://www.sommarskog.se/
> However, one thing I am not clear on; why do you back up the private key
> and Laurentiu drops it? What is the risk of dropping it like he did?
>
> Erland Sommarskog's example:
> BACKUP CERTIFICATE reloadcert TO FILE = 'C:\temp\reloadcert.cer'
> WITH PRIVATE KEY (FILE = 'C:\temp\reloadcert.pvk' ,
> ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
> DECRYPTION BY PASSWORD = 'All you need is love')
>
> Laurentiu Cristofor's example:
> alter certificate certSignCreatePrincipal remove private key;
> backup certificate certSignCreatePrincipal to file =
> 'certSignCreatePrincipal.cer';
If I do it in one way, and Laurentiu another, Laurentiu is likely to be
right! (Unless it's the usage of EXECUTE AS, where we are known to
disagree. :-)
I will have to admit that I was not aware of the REMOVE PRIVATE KEY
clause. I used what I was able to get to work. Dropping the private key
seems to be a better idea. However, as I understand it, it calls for a
different order of things. To wit, I first create the certificate and the
login in master, export the cert, and then move to the target database
where I sign the procedure. As I need the privte key to sign, I do need
the private key at this point.
I should definitely spend some time reworking the example - I only
need to find that time. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
)