Question on the use of schemas for access control

  • chuck.forbes

    SSCarpal Tunnel

    Points: 4291

    I know that this is a long article, so, I totally understand if there are no replies. This seemed like a good place to start, though, since the comments on the original article are 3 years old:

    https://www.red-gate.com/simple-talk/sql/sql-training/schema-based-access-control-for-sql-server-databases/

    I get the general premise of this article, but it seems to set the concept of user types at a higher level than we do. So that's where I'm trying to get some clarification. For example, they separate out groups using the following examples "Web-User, Admin, HR, Finance and marketing". But I would say that while we may have those, each of those groups have in turn their own "sub-roles" for lack of a better term. So, within "Web-user" there may be 5 sub-roles, each with their own mix of SEL/INS/UPD/DEL.

    Is this overall security technique saying that I would require 5 additional schemas for each of those sub-roles, or, is it saying that those 5 sub-roles would all have the same permission within the schema assigned to "Web-user", and then the application interface would control the SEL/INS/UPD/DEL, depending upon the sub-role a user is a member of?

    Thanks,

    --=Chuck

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Sue_H

    SSC Guru

    Points: 90543

    I think the point is more about using schemas for setting up permissions rather than always focusing on object level permissions. Basically looking at granting the permissions on schemas rather than objects. And allowing for some variances where you have a role group with 20 users and two of them may have different permissions. Grant to the group and then have some additional DCL for the two needing different permissions.  It's not about creating schemas to match the groups but granting the groups permissions in schemas to work with a collection of objects for security rather than individual objects. It's not necessarily about creating more schemas but being more thoughtful of what objects are in what schemas.

    Sue

  • chuck.forbes

    SSCarpal Tunnel

    Points: 4291

    Thanks Sue. That was pretty much my take on the article. I just wanted to see it through another DBA's lense. Much appreciated.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply