Permission to view system tables in master database
- From: Dave <Dave@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 27 May 2007 12:11:00 -0700
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 DB
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 ;
.
- Follow-Ups:
- Re: Permission to view system tables in master database
- From: Erland Sommarskog
- RE: Permission to view system tables in master database
- From: Dave
- Re: Permission to view system tables in master database
- Prev by Date: Re: SQL 2000 Stored Procedure Problem
- Next by Date: RE: Permission to view system tables in master database
- Previous by thread: Custom Database Roles -- Insert
- Next by thread: RE: Permission to view system tables in master database
- Index(es):
Relevant Pages
|
