RE: Permission to view system tables in master database
- From: Dave <Dave@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 27 May 2007 14:04:00 -0700
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/2005/06/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;
.
- References:
- Prev by Date: Permission to view system tables in master database
- Next by Date: Re: Permission to view system tables in master database
- Previous by thread: Permission to view system tables in master database
- Next by thread: Re: Permission to view system tables in master database
- Index(es):
Relevant Pages
|
|
