Blog Post

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating