Post reply

Question on the use of schemas for access control

  • January 17, 2020 at 9:21 pm

    #3716433

    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

  • January 18, 2020 at 10:10 pm

    #3716569

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

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

  • January 20, 2020 at 10:43 pm

    #3716914

    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

  • January 21, 2020 at 9:09 pm

    #3717162

    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 3 (of 3 total)

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