SQL Server Security Audit (Part 2) – Database Level Audit

Once the Server Level audits described in Part 1 of the Security Audit series are complete, databases should be considered next. A number of steps can be taken to audit database level security.

Database owners

A database owner can perform any action in the database. This includes granting access rights to the database to other users. Every database has a built-in user account called the “dbo”. This is the database owner. By default, this user account is mapped to the login that created the database. There is also a fixed database role called “db_owner” whose members have database ownership privilege.

Just like finding out who has system administrator privilege in your SQL Server, you may want to find out who has ownership rights in each database.

Executing the following script against each database will give you a list of user accounts that are members of the db_owner role:

USE <<database_Name>> — Run against each database

GO

SELECT c.name AS DB_Owner_Role_Member

FROM  sys.database_principals a

       INNER JOIN sys.database_role_members b

        ON a.principal_id = b.role_principal_id AND a.is_fixed_role = 1 AND a.name =’db_owner’

            INNER JOIN sys.database_principals c

            ON b.member_principal_id = c.principal_id

If you want to find out the login that is mapped to the built-in “dbo” user, you can use the following query:

USE <<database_name>> — Execute for each database

GO

SELECT b.name AS Login_Mapped_to_DBO

FROM  sys.database_principals a

       INNER JOIN sys.server_principals b

       ON a.sid = b.sid

WHERE a.name = ‘dbo’

Guest user account

Just like dbo, the “guest” account is also a special built-in database user. This user is disabled by default, but you can enable it or create it manually. The guest account does not correspond to any SQL Server login. In fact its purpose is to serve as an ad-hoc user for any login. What this means is that any login that does not have a corresponding database user account can still get into the database provided the database has the guest user enabled. Guest user exists in the system databases – and they are there for a reason. But if you have a guest account enabled in your production database, you need to know why it is there and what access it has got.

A code snippet like the following can help you identify the rights of the guest user.

USE <<database_name>> — Execute for each database

GO

SELECT   c.name AS ObjectName,

         c.type_desc AS Object_Type,

         b.permission_name AS Permission_Type,

         b.state_desc AS Permission_Status

FROM     sys.database_principals    a

         INNER JOIN sys.database_permissions    b    

         ON a.principal_id = b.grantee_principal_id

            INNER JOIN sys.objects c

            ON b.major_id = c.object_id        

WHERE    a.name = ‘guest’

ORDER BY c.name


Orphan users

Strictly speaking, this is not a security hole, but one that needs to be looked at nevertheless. Sometimes SQL Server logins are deleted without dropping the associated database users first. Often databases are restored from another system and the user accounts in them do not correspond to any local logins. Your database is said to have “orphan” users in such cases. The security audit should pick up these orphan users in each database. The following query can be run in each database for this purpose.

USE <<database_name>> — Execute for each database

GO

SELECT      a.name AS OrphanUserName, a.type_desc AS UserType

FROM  sys.database_principals a

      LEFT OUTER JOIN   sys.server_principals   b

      ON    a.sid = b.sid

WHERE b.sid IS NULL

AND         a.type In (‘S’, ‘U’, ‘G’)

AND         a.name NOT in (‘sys’, ‘INFORMATION_SCHEMA’, ‘guest’)

 

Continues…

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |