3

I'm redesigning security setup in our database. I was in process of defining certain user-defined roles and - as I find it handy - on the other monitor I was browsing documentation to make sure I don't miss anything.

And in this resource from the Microsoft Docs I noticed the following:

Do not add user-defined database roles as members of fixed roles. This could enable unintended privilege escalation.

Now - either I'm tired or there is no good explanation for this tip.

In fact I find it common to define specific role and ensure they have proper permission through fixed role membership.

Simplified examples:

  • "power user" = db_datareader + db_datawriter
  • "developer" = db_datareader + db_datawriter + ddladmin
  • "function designer" = db_datareader + GRANT CREATE FUNCTION on database:xyz

etc.

It's just handy.

I'm putting this security setup on hold - hopefully someone can give me some good pointers here.

Improve this question

3 Answers 3

Reset to default
4

The author is recommending that you practice the Principle of Least Privilege.

Basically, the author is saying that if you create a Role, it should do exactly what you define that role to do and nothing else at all. If you then assign it to a built-in role, you're now greatly expanding the role's permissions and losing some control over exactly what those permissions might be.

Improve this answer
2
  • 2
    That makes sense in a way but doesn't explain "unintended privilege escalation" - I'm aware what these roles grant but it sounds like there's some kind of loop hole when combining them with regular GRANTs
    – nimdil
    Jun 14, 2018 at 7:21
  • I don't think the author intended to imply that the system would modify permissions, but that you might not be aware of all the things that role can do. Or that someone later could come along and add rights to that role without your awareness.
    – CaM
    Jun 14, 2018 at 12:00
1

My guess would be say you have a role to assign users to a subset of tables and/or procedures in a database. If you add that role to say the datareader role then they would have access to every table.

This is an organizational thing. In some if someone asks for read only access to a table you give them access to the entire DB to save future time. In others access is strictly controlled due to information being highly sensitive and private in nature.

Improve this answer
1
  • 1
    Well that sounds more like "don't use it when you don't fully understand what they do", not "unintended privilege escalation.".
    – nimdil
    Jun 14, 2018 at 7:22
1

I'm redesigning security setup in our database

I would recommend moving away from the fixed database roles, entirely.

Ever since SQL 2005 it's just as easy to grant without the fixed database roles. They are really there for backwards compatibility (with apps and DBAs).

You can grant the role database-level permissions, schema-level or object-level permissions.

So one role might have

GRANT SELECT TO POWER_USER
GRANT INSERT, UPDATE, DELETE ON SCHEMA::DBO TO POWER_USER

Another might have

GRANT CONTROL TO DEVELOPER

etc.

Improve this answer
1
  • 1
    That makes sense - I started toying with the product starting with 2005. But I never encountered formal indication that it's for backwards compatibility. In fact - I have never seen SQL Server database not using them extensively. I'm fully aware that it's perfectly easy to just go with GRANT commands and I'm using them for users for which fixed-roles are incorrect.
    – nimdil
    Jun 14, 2018 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.