Finding a User’s Effective Permissions

When you have a complex hierarchy of server roles and database roles, as well as permissions granted directly to users, it can sometimes be challenging to work out exactly which permissions a user has. A system function that can help with this issue is sys.fn_my_permissions(). This function accepts the parameters described in Table 6-1.

The function returns the columns described in Table 6-2.

The function is designed to return information about the caller of the function, but you can change this behavior by using the EXECUTE AS statement. The EXECUTE AS statement is used to specify the name of a login or user whose identity should be used as the execution context of the session.

As an example of how the EXECUTE AS statement works, please review Listing 6-2.

Listing 6-2. EXECUTE AS Example

USE master
GO

CREATE LOGIN DemoLogin WITH PASSWORD=N'Pa$$w0rd', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

ALTER SERVER ROLE sysadmin ADD MEMBER DemoLogin

USE AdventureWorks2016CTP3
GO

SELECT SUSER_SNAME() ;

EXECUTE AS LOGIN = 'DemoLogin' ;

SELECT SUSER_SNAME() ;

REVERT ;

SELECT SUSER_SNAME() ;

The script returns three results. The first result is your own login name. The second is DemoLogin’s login name. And the third result is your own login again. This is because the REVERT keyword is used to change the session context back to your own security context.

The script in Listing 6-3 demonstrates how the sys.fn_my_permissions function can be used in conjunction with the EXECUTE AS clause to find a user’s effective permissions at the instance, the database, and the object (within the current database) levels in a single query. I first wrote about this technique back in 2011 on the “SQL Server: Down & Dirty” blog ( www.sqlserverdownanddirty.blogspot.com ); since then, the method has been used and replicated by many others.

Listing 6-3. Find a User’s Effective Permissions

EXECUTE AS LOGIN = 'DemoLogin'
   SELECT o.name
        , a.entity_name
        , a.subentity_name
        , a.permission_name
   FROM sys.objects o
   CROSS APPLY sys.fn_my_permissions(CONCAT(
                                        QUOTENAME(
                                                SCHEMA_NAME(schema_id))
                                     , '.'
                                     , QUOTENAME(o.name))
                                     , 'OBJECT') a  
   UNION ALL
   SELECT d.name
        , a.entity_name
        , a.subentity_name
        , a.permission_name
   FROM sys.databases d
   CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a
   UNION ALL
   SELECT @@SERVERNAME COLLATE Latin1_General_CI_AS
          , a.entity_name
          , a.subentity_name
          , a.permission_name
   FROM fn_my_permissions(NULL, 'SERVER') a
   ORDER BY 1
REVERT

The script works by running three separate queries and creating a union of the results. The first query returns each object name from sys.objects and passes this name, along with the schema name, into the sys.fn_my_permissions() function . The second query does the same thing, but instead of interrogating sys.objects, the script interrogates sys.databases to retrieve permissions at the database level. The final query resolves the user’s effective permissions against the instance itself.

Code Signing

Code injection attacks can cause security breaches. You can protect against them by using code signing. For now, however, let’s simply assume that your security policy states that all assemblies and stored procedures must be code signed to help minimize the security footprint.

The script in Listing 6-4 reports which stored procedures in the database have been code signed and if the signature is valid. The script uses two security metadata objects. The first is sys.Certificates. The columns returned by this catalog view are described in Table 6-3.

The second metadata object used by the script is sys.fn_check_object_signatures(). This system function is used to return information regarding object signatures and their validity, based on the thumbprint of a certificate or asymmetric key. The function accepts the parameters described in Table 6-4.

The sys.fn_check_object_signatures function returns the columns described in Table 6-5.

Listing 6-4. Check Objects’ Signatures

DECLARE @thumbprint VARBINARY(20) ;  

SET @thumbprint =
(
SELECT thumbprint  
FROM sys.certificates  
WHERE name LIKE '%SchemaSigningCertificate%'
) ;  

SELECT entity_id
         , SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(entity_id) AS ProcedureName
         , is_signed
         , is_signature_valid  
FROM sys.fn_check_object_signatures ('certificate', @thumbprint)  cos
INNER JOIN sys.objects o
        ON cos.entity_id = o.object_id
WHERE cos.type = 'SQL_STORED_PROCEDURE                                                    ' ;  
GO  

The first part of the script retrieves the thumbprint of the database’s code signing certificate from the sys.Certificates catalog view. The second part of the script passes this thumbprint into the sys.fn_check_object_signatures and joins the results to the sys.objects catalog view to retrieve the schema name of the procedure.

Permissions Against a Specific Table

You may have a specific table or set of tables that contains sensitive information, and your security policy may state that you need to regularly audit who has permissions to that table and who assigned those permissions. Using SQL Server metadata, this is a straightforward task.

The sp_table_privileges system stored procedure identifies all permissions that principals have against a specific table, along with who granted those permissions. The procedure accepts the parameters described in Table 6-6.

The columns returned by the sp_table_privileges procedure are described in Table 6-7.

The statement in Listing 6-5 returns results for all tables in the current database.

Listing 6-5. sp_table_privileges

EXEC sp_table_privileges @Table_name = '%' ;

Always Encrypted Metadata

Because there can be a one-to-many relationship between column master keys and column encryption keys, followed by a one-to-many relationship between column encryption keys and encrypted columns, metadata can be invaluable in keeping track of how your data is encrypted. The query in Listing 6-7 joins sys.tables and sys.columns to the new sys.column_encryption_keys, sys.column_encryption_key_values, and sys.column_master_keys catalog views to provide a complete path through the hierarchy from the column to key store location of the column master key.

The sys.column_encryption_keys view returns the columns described in Table 6-10.

The sys.column_encryption_key_values view returns the columns described in Table 6-11.

The sys.column_master_keys view returns the columns described in Table 6-12.

Listing 6-7 demonstrates how these metadata objects can be drawn together.

Listing 6-7. Interrogate Always Encrypted Metadata

SELECT
          t.name AS TableName
        , c.name AS ColumnName
        , c.encryption_type_desc
        , c.encryption_algorithm_name
        , cek.name AS ColumnEncryptionKeyName
        , cev.encrypted_value
        , cev.encryption_algorithm_name
        , cmk.name as ColumnMasterKeyName
        , cmk.key_store_provider_name AS column_master_key_store_provider_name
        , cmk.key_path
FROM sys.columns c
INNER JOIN sys.column_encryption_keys cek
    ON c.column_encryption_key_id = cek.column_encryption_key_id
INNER JOIN sys.tables t
    ON c.object_id = t.object_id
JOIN sys.column_encryption_key_values cev
    ON cek.column_encryption_key_id = cev.column_encryption_key_id
JOIN sys.column_master_keys cmk
    ON cev.column_master_key_id = cmk.column_master_key_id ;

TDE Metadata

TDE metadata is exposed through the sys.databases, sys.certificates, and sys.database_encryption_keys catalog views. The sys.databases catalog view contains a column called is_encrypted. This column has the data type of BIT and returns 0 if a database is not encrypted with TDE and 1 if it is encrypted. Information on the certificate used to encrypt the database encryption key is exposed through sys.certificates.

The sys.database_encryption_keys catalog view exposes details of the keys used to encrypt the databases. It returns one row for each database that has a database encryption key associated with it. Table 6-13 details the columns returned by this catalog view.

The metadata exposed for TDE can be useful at various times; for example, to return a list of encrypted database on the instance, use the script in Listing 6-8.

Listing 6-8. Return a List of Encrypted Databases

SELECT name
FROM sys.databases
WHERE is_encrypted = 1 ;

If you need to ensure that all of the server certificates that are used to encrypt database encryption keys have been backed up, you can use the query in Listing 6-9. This query returns a list of certificates used in TDE that have not been backed up.

Listing 6-9. Ensure that Certificates Have Been Backed Up

SELECT
     DB_NAME(dek.database_id) AS DatabaseName
    ,c.name AS CertificateName
FROM AdventureWorks2014.sys.dm_database_encryption_keys dek
INNER JOIN master.sys.certificates c
ON c.thumbprint = dek.encryptor_thumbprint
WHERE c.pvt_key_last_backup_date IS NULL ;

If you had a task of encrypting many databases on an instance, you could even use metadata to create a metadata-driven script that would do the hard work for you. I used this script recently—when a friend of mine mentioned that he had been quoted three months by his DBA team—to encrypt 400+ databases on an instance.

The script in Listing 6-10 firsts creates a server certificate that is used to encrypt the database encryption key for each database. The script then uses the sp_msforeachdb system stored procedure to loop around each database.

Inside the loop, the script first checks to ensure that it is not in the context of a system database, and then checks to ensure that the database has not already been encrypted. This makes the script re-runnable, should you have an issue part way through. After the checks are complete, it creates a database encryption key before enabling TDE.

Listing 6-10. Metadata Driven Encryption Script

USE master                                                
GO

CREATE CERTIFICATE TDECert WITH SUBJECT = 'My DEK Certificate';
GO

EXEC sys.sp_MSforeachdb @command1 = 'USE ?
IF (SELECT DB_ID()) > 4
BEGIN
    IF (SELECT is_encrypted FROM sys.databases WHERE database_id = DB_ID()) = 0
        BEGIN
        CREATE DATABASE ENCRYPTION KEY  
        WITH ALGORITHM = AES_128  
        ENCRYPTION BY SERVER CERTIFICATE TDECert

        ALTER DATABASE ?  
        SET ENCRYPTION ON
        END
END' ;

Risks of Metadata Visibility

Even with the security measures that are in place to protect SQL Server, an attacker may still be able to expose some metadata if the overall security design of your application is weak. For example, imagine that you have a web application that handles security in the application tier and then connects to a SQL Server instance by using a single, highly privileged account.

If the web application is vulnerable to SQL injection, then an attacker could force the execution of the query in Listing 6-11.

Listing 6-11. Forced Information Disclosure

SELECT 1 + name FROM sys.tables

When run against the AdventureWorks2016 database, the query in Listing 6-11 returns the error message shown in Figure 6-1.

Figure 6-1. Forced error message

This error message has provided the attacker with the following information:

• There is a table in the database s ScrapReason.

• The application is leaking metadata.

• The application is (probably) running through a highly privileged account.

This information gives an attacker plenty of insight into where to start an attack. For example, if the attacker is correct and the application does run through a single account, then it is likely that there is a user’s table specifying permissions. The attacker could amend his query to filter by tables that contain the wildcard strings %user% or %login%. Once the attacker has this information, he can attack the table specifically and start spoofing user identities!