SQL Server – Granting User Permission to View XE Results

certificateimpersonationSecuritysql serversql server 2014

I need to grant a user (MyDomain\JohnSmith) permission to view one particular XE session results using sys.fn_xe_file_target_read_file

I thought the best way to do this would be to encapsulate the logic within a stored procedure in a database which John Smith uses called MyDb and then grant him access to the stored procedure.

I have solved this so far but it means using impersonation of the dbo user within the stored procedure and setting IS_TRUSTWORTHY on which some Googling suggests is a less secure method and that signing a stored procedure is the most secure way to achieve this.

Going the the signing method, I have hit some problems as below:

First of all, I create a certificate in the database:

USE MyDb
GO

CREATE CERTIFICATE [CodeSigningCertificate]
ENCRYPTION BY PASSWORD = 'SuperSecretPassword'
WITH EXPIRY_DATE = '2099-01-01',
SUBJECT = 'Code Signing Cert'

then I create my stored procedure which has the logic

CREATE PROCEDURE MySchema.MyProc
AS
BEGIN

    DECLARE @TraceFilePath NVARCHAR(256) = 'Path/To/My/File*.xel'

    SELECT  CONVERT(XML,event_data).value('(/event/@timestamp)[1]', 'NVARCHAR(MAX)' ) AS [TimeStamp],
            CONVERT(XML,event_data).value('(/event/action[@name="database_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Database_name],
            CONVERT(XML,event_data).value('(/event/data[@name="statement"]/value)[1]', 'NVARCHAR(MAX)' ) AS [Statement],
            CONVERT(XML,event_data).value('(/event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)' ) AS SQL_Text,
            CONVERT(XML,event_data).value('(/event/action[@name="username"]/value)[1]', 'NVARCHAR(MAX)' ) AS Username,
            CONVERT(XML,event_data).value('(/event/action[@name="client_hostname"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_Hostname,
            CONVERT(XML,event_data).value('(/event/action[@name="client_app_name"]/value)[1]', 'NVARCHAR(MAX)' ) AS Client_app_name
    INTO    #Results
    FROM    sys.fn_xe_file_target_read_file (@TraceFilePath, NULL, NULL, NULL )


    SELECT  *
    FROM    #Results
    WHERE   Statement LIKE '%some value%'
    ORDER BY SQL_Text
END

Next, I sign the stored procedure

ADD SIGNATURE TO MySchema.MyProc
BY CERTIFICATE [CodeSigningCertificate]
WITH PASSWORD = 'SuperSecretPassword';

Then I create a user which uses the certificate

CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate];

At this point, I need to grant the underlying privilege that the is required to run sys.fn_xe_file_target_read_file which is GRANT VIEW SERVER STATE

GRANT VIEW SERVER STATE TO [CodeSigningUser]

but when I try to do this, I get

"Msg 4621, Level 16, State 10, Line 44
Permissions at the server scope can only be granted when the current database is master"

which makes perfect sense as it is a server level permission that I am trying to grant to a database level principal

I have tried creating a login for the certificate (as I can grant VIEW SERVER STATE to a login) :

CREATE LOGIN [CodeSigningLogin] FROM CERTIFICATE [CodeSigningCertificate];

but I get the error

Msg 15151, Level 16, State 1, Line 40
Cannot find the certificate 'CodeSigningCertificate', because it does not exist or you do not have permission.

Which, again makes sense as the certificate is in a user database

I can create the certificate in the master database which allows me to create the login from the certificate but then I can't sign the stored procedure using that certificate as the certificate resides in a different database to the stored procedure.

The only way I can think of to accomplish this is to create the Stored Procedure in the master database and then create the certificate, also in the master database, create the login and assign VIEW SERVER STATE to the login.

Is there a way I can keep my stored procedure in MyDb and have a user MyDomain\JohnSmith execute it to be able to see the XE Session Results?

Best Answer

I have managed to do this now thanks to this article the missing piece of the puzzle was to copy the certificate to the master database

DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'CodeSigningCertificate')),
                 1);

EXEC (N'USE [master];
CREATE CERTIFICATE [CodeSigningCertificate]
FROM BINARY = ' + @Cert);

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'

Sql-server – Sharing certificates encryped by password between DBs and instances

You only specify the password to decrypt the private key file with. You need to add a password to store the certificate with:

CREATE CERTIFICATE ERecruitStatsGatheringCert        
ENCRYPTION BY PASSWORD = 'S3creT!
FROM FILE = 'd:\DavesCert.cer'       
WITH PRIVATE KEY (
    FILE = 'd:\DavesCert.pvk',  
    DECRYPTION BY PASSWORD = 'S3creT!');

But your Background information makes the whole exercise futile. You're doing it wrong. The correct sequence of actions is:

Create a cert in the user DB
Add the signature to the store procedure(s)
Drop the private key of the certificate
Backup/restore that cert to master (public key only!)
Create login in master, assigned that permission, etc.

Notice that not only the private key never leaves the database, is in fact explicitly dropped right after signing the procedure. This is required in order to prevent further use of this certificate to sign other procedures and abuse the login permissions created at step 5. You repeat these steps on each database and use a different certificate on each database. You repeat these steps each time you modify any of the signed procedure and generate a new certificate each time.

As a general rule, any signature/encryption public/private key (RSA) scheme that requires a copy of the private key is broken. This is why is called private and this is what gives value to the signature: the knowledge that there exists only one instance of this private key in the world, therefore anything signed by it is proof that it came from the one and only owner/holder of the unique private key.

I know there are some who shun my recommendation of dropping the private key right after is used to sign the procedure. I stand by my recommendation, but the important thing for your problem is that it is not required to copy the private key into [master] in order to leverage the the code signing permission you desire. You can (and should) create the login derived from the certificate using just the public key.

Regarding automation: As I see it, basically it is a tool (script, app) that takes two inputs: a securable (procedure, function, module) and a permission (VIEW SERVER STATE). Everything else is an automated process (create a one-time-use throw-away cert, sign, export cert, import cert in [master], create login, grant permission). The names implied (cert name, login name) can all be generated.

Proliferation of names is a valid concern. An alternative would be to use only one certificate and associated private key, and the signature tool could add the private key when needed (when signing the procedure), then remove it after the signing. Is really a matter of how you run your shop and how important is the asset you're protecting. But the important thing is that you do not need to import the private key in [master].

Best Answer

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Sql-server – Sharing certificates encryped by password between DBs and instances

Related Question