Recently I was asked to create a database audit program. It was designed to audit roles, schemas, users, login associations, permissions for roles and users and role/user schema assignments. I’ve written this kind of program before. Usually I use SQL Data Management Object. This time I wrote the application using system views and functions. I find the performance to be a lot faster than using DMO objects.
You may be asking what is the value of a database audit method such as this? For many installations this kind of audit allows you to determine how your database and or its deployment has changed. Gathering the audit information, I save the results in a database I designed for this purpose. Now I can run the program on a scheduled basis, or execute it on demand. Then I can compare the results of different audit results, much like comparing different versions of code, and see how the implementation has changed over time.
This is different than a database compare in that it maintains an ongoing history. It doesn’t compare one database against another. It continues to track the state of the same database over time. The comparison, then, is of the same database at different points of time.
This has proven useful for determining the rights of a user in the database. We like to grant access to database objects through schemas and roles. The schemas own and control the objects. Roles are granted permissions to the schemas. Users/logins are granted membership to roles.
Most of our logins are Active Directory groups. As a result, we can determine what permissions a specific Active Directory Login can perform through the AD groups they have been assigned. We track the groups that have been created as database logins, which the AD User is a member. Then reviewing the Roles the group has been assigned, we can establish the schemas, and permission within that schema.
Using this implementation we get a fairly simple method of granting database access, and have the ability to be quite granular regarding individual user permissions in a database.
We’ll talk a little bit about using schemas tomorrow.
Cheers,
Ben