SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Rights within the database

This is a follow-on to my post What are the actual rights?

First and foremost, I want to include membership in the fixed database roles. That's fairly easy to pull based on individual users. The db_datareader and db_datawriter roles are naturally of great interest because they grant explicit access to all tables and views. However, if I want to get all the permissions, I need to look deeper. Within an individual database several areas have to be looked at for permissions. They are:
  • Database owner - Whoever is the owner of the database has the ability to completely bypass permissions. Therefore, I need to take this into account, especially if a database belongs to a particular login and not a generic one like sa
  • Members of the db_datareader and db_datawriter fixed database roles - I've mentioned this above, but just producing a report saying "Said user is a member of the role" may not be sufficient to convey how much access a particular user has. Therefore, if we produce a report saying said user has SELECT access, we need to take into account these fixed database roles.
  • Members of the db_denydatareader and db_denydatwriter fixed database roles - Like the previous point, these roles block access. And since DENY trumps an explicit grant, this may mean a user doesn't have the access other roles would grant. We have to take this into account when reporting what access a user actually has.
  • Permissions assigned against individual users - This is a no-brainer. We need to know what a particular user account has been assigned explicitly.
  • Permissions assigned to database roles - Best practices says to create user-defined database roles and assign permissions to them. Therefore, we can't just go with the permissions defined for the user. By the way, we do need to consider both user-defined and the public fixed database role as permissions can be assigned directly to public. Other fixed database roles aren't a concern because we can't assign permissions directly to them.
  • Users as members of database roles - In order to map permissions properly, we need to keep track of what database roles a user is a member of.
  • Database roles nested within database roles - Since database roles can be nested, we need to keep track of what roles are members of what other roles. This will also pose some complexity when we look at interrogating Windows logins which happen to be security groups.
Keep in mind that none of this takes into account ownership chaining. Unfortunately, to be able to do that means parsing functions, stored procedures, and views to determine what objects are being accessed and then determining ownership. If we have objects with the same name owned by different owners, we have to evaluate what object is being referenced. There's also the potential of referencing objects in other databases. At this point I'm not interested in going down that path. I'm interested in implicit permissions due to fixed database roles or database ownership and explicit permissions granted to database security principals. So that is the scope I'll be looking at for the time being.

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


No comments.

Leave a Comment

Please register or log in to leave a comment.