none
Schemas and Roles question.

    Question

  • Hello,
    I'm using SQL Server 2008 Express R2 SP2.

    I'm having a hard time understanding the difference between Schemas and Roles, even after having gone through tons of links thrown up by a google search.

    I'll just post my specific question -
    Let's say I have two sets of users in my organization for each department. Let's say this is the Accounting department. So our two sets of users are Accountants, and Accounting Managers. The users of these two groups do not overlap.
    Let's also say there are higher level Managers over the department managers, all of whom in-turn report to the company's Proprietor.

    We have a bunch of tables in our database pertaining to Accounting. All tables have certain fields which are only of concern to Managers (or their higher bosses) and not to regular Accountants.
    No access is permitted to any of the tables directly, but instead, we have a whole bunch of stored procedures which are responsible for doing stuff like Inserts, Updates, and Selects.
    ALL tables in the database (regardless of department) are owned by the dbo schema.
    (Also, only one person is allowed DDL access to the tables/database, which would be the proprietor who doubles as the admin - me.)

    We have different procedures coded for different roles - meaning we have different procedures coded for Accountants, and for Accounting Managers, depending on what they will need to do with the data.
    But the caveat here is that all Accounting Managers will need access to all stored procedures coded for regular Accountants, but no regular Accountant can access any stored procedure coded for any Accounting Manager. Similarly, all higher Managers will need access to all procedures coded for regular Accountants, Accounting Managers, regular HR guys, HR Managers, etc., but the lower level guys can't access any stored procs for the upper level guys.

    Going by what I understand about Roles and Schemas so far, this is what I could do -
    Create Roles for my various groups, eg. Accountants, HR, Accounting Managers, HR Managers, Managers, Proprietor, etc., and then create corresponding Schemas with the same names, and add the namesake Role as the Schema's owner.
    I then create my stored procedures and assign ownership of these stored procs to the various schemas for whose use they are created.
    Then I do not assign any permissions to the Roles, but to the Schemas instead. By default, any Role that owns any Schema gets EXEC permission on ALL stored procs contained within this schema. (Although I'm not sure this is how it works, and I may have to manually sit and add each stored proc for that schema into the Permissions tab, and then manually assign EXEC permission on each item.)

    But then my question is this - First of all, I will have to manually grant exec permission to the Schema's owner for each stored proc within the schema. I can't just put objects (stored procs) into a schema, specify a role as that schema's owner, and expect the exec permission to work for all that role's users for all the stored procs within that schema. That's a lot of work - manually adding stuff.
    Second, a schema can only be owned by one service principal at a time. So this means, if I grant permissions to the Accounting role on the Accounting schema, all my Accounting Managers role members are left in the lurch, and they can't access any stored procs within the Accounting schema.
    I'm guessing I may be able to manually assign grant exec privileges to the Accounting Managers role using the 'securables' tab on each of the stored procs within the Accounting schema, but again, that's a lot of work that is unnecessarily being repeated.

    So yeah the question is -
    Is there a way for me to accomplish what I am looking for, using both Roles and Schemas, but with lessor repetitive effort?
    If not, and if I have to manually sit and assign permissions for each object in the manner described above, does creating/using Schemas of any sort (apart from the default dbo) make any sense for me at all? Can't I just ditch the Schemas, and just manually assign all permissions to the Roles instead?


    (Another problem I am facing is - using SQL Server 2008, it does not let me assign any Permissions/Securables to either Schemas or Roles using the GUI tools in Management Studio. Can I do this via GUI at all, or will I have to write T-SQL code for it each time I want to add securables/permissions to a Role or a Schema?)

    • Moved by Kalman Toth Tuesday, May 21, 2013 12:41 AM Not db design
    Tuesday, May 21, 2013 12:19 AM

Answers

  • Yes, create  two Schemas called Schema_Accountants and Schema_Accounting_Managers.

    Don't assign ownership of Proc1 to Proc25 to Schema_Accountants, and of Proc26 to Proc35 to Schema_Accounting_Managers.

    Instead of assigning ownership of the procs, you want to create the procs in the schema. As in CREATE PROC Schema_Accountants.Proc1 AS ...

    The use of the word ownership has a tortured history. The concept that we call schema used to be called owner. SQL Server 2005 changed the name of the concept to schema. Though very similar, don't think of schemas as "owning" the procs. Think of the procs as being contained in the Schema_Accountants schema. That will be less confusing, because the is a concept of ownership which is separate. For example, as a dbo, run this:
    CREATE SCHEMA TestSch;
    CREATE TABLE TestSch.TestTable (col1 int);
    sp_help [TestSch.TestTable];
    Note that the TestTable in the TestSch schema is "owned" by dbo. And you can use the ALTER AUTHORIZATION statement to change the owner to somebody else. But it stays in the TestSch schema. So... don't think of schemas as owners. They are organizing groups.

    Add the account users to the Accountants role. Then grant the Accountants role the execute permission to the entire Schema_Accountants schema:

    GRANT EXECUTE ON SCHEMA::Schema_Accountants TO Accountants;

    That will apply to all Proc1 - Proc25 procedures that are in the Schema_Accountants schema.

    And for Managers:

    GRANT EXECUTE ON SCHEMA::Schema_Accounting_Managers TO Accounting_Managers;


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by reachrishikh Tuesday, May 21, 2013 8:18 PM
    Tuesday, May 21, 2013 8:02 PM

All replies

  • Roles are for grouping people. To be precise, server roles (a type of principal) can have members that are server principals (logins, and other server roles). Database roles (a type of principal) can have members that are database principals (database users and other database roles).

    Schemas are for grouping database objects; tables, views, procedures, and a few other more obscure things.

    Permissions to access objects are granted or denied to principals. So you can grant access (such as the SELECT permission) to a principal such as a role. But a schema (a group of objects) can not be the holder of a permission. It can only be the object of a permission.

    A typical system, would group accounting objects (tables, views, and procedures) in the accounting schema. A group of roles would be created for each group of people; accountants, accountant managers, all managers, senior managers, etc. You can grant membership in these roles individually, or in a nested manner (such as accountant managers and engineering managers, and then a all managers role which has the other manager roles as it's members.)

    In a large organization with a good active directory structure, using Windows groups can be helpful. Group the managers there. Add the Windows group as a login and add it to the manager role. Obviously you can make this very complicated or keep it simple. Which ever works for you. And don't reuse names by creating an Accounting role and an Accounting schema or you will confuse everyone. (SQL Server has already done enough of that with the dbo user and the dbo schema. Same name. Different things.)

    Ownership of the schemas it probably less important. You probably want the schema owned by the people (group or user) that will manage the schema. Not the people using the schema.

    Hope this doesn't add more confusion.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, May 21, 2013 4:09 PM
  • Hi Rick,
    Thanks for the explanation. Most of it doesn't pertain to my exact issue, but helps clarify things anyway.

    I guess my question above was not put forth very clearly and was a little muddled, so I'll try again.

    Let's say I have a bunch of stored procedures in my database.

    Proc1
    Proc2
    .....
    Proc25

    Proc 26
    Proc 27
    .....
    Proc35

    That's a lot of procedures. If I manually try to sit and assign Exec permission on each procedure to each principle, it's going to take me a lot of time.

    Proc1 to Proc 25 needs to be accessed by a group called Accountants. Proc1 to Proc 35 all need to be accessed by a group called Accounting Managers.

    I have two database roles in the database called Accountants and Accounting Managers.

    In order to simplify management, can I just create two Schemas called Schema_Accountants and Schema_Accounting_Managers, and assign ownership of Proc1 to Proc25 to Schema_Accountants, and of Proc26 to Proc35 to Schema_Accounting_Managers?
    But now, how do I assign Exec permission to the Accountants role on all stored procs within the Schema_Accountants schema with the least amount of code/effort?
    Also, will it be possible for me to assign Exec permission to the Accounting Managers role on all stored procs within the Schema_Accounting_Managers schema, AND the Schema_Accountants schema, again with the least amount of code/effort?

    I mean, one of the major reasons to use Schema based logical grouping of objects in the first place is so that I do not have to write repeated code lines like -
    Grant Exec to Accountants on Proc1
    Grant Exec to Accountants on Proc2
    .....
    Grant Exec to Accountants on Proc25
    but instead, can do something like
    Grant Exec to Accountants on all Stored Procs in Schema_Accountants

    and
    Grant Exec to Accounting Managers on all Stored Procs in Schema_Accountants
    Grant Exec to Accounting Managers on all Stored Procs in Schema_Accounting_Managers.

    Tuesday, May 21, 2013 5:30 PM
  • Yes, create  two Schemas called Schema_Accountants and Schema_Accounting_Managers.

    Don't assign ownership of Proc1 to Proc25 to Schema_Accountants, and of Proc26 to Proc35 to Schema_Accounting_Managers.

    Instead of assigning ownership of the procs, you want to create the procs in the schema. As in CREATE PROC Schema_Accountants.Proc1 AS ...

    The use of the word ownership has a tortured history. The concept that we call schema used to be called owner. SQL Server 2005 changed the name of the concept to schema. Though very similar, don't think of schemas as "owning" the procs. Think of the procs as being contained in the Schema_Accountants schema. That will be less confusing, because the is a concept of ownership which is separate. For example, as a dbo, run this:
    CREATE SCHEMA TestSch;
    CREATE TABLE TestSch.TestTable (col1 int);
    sp_help [TestSch.TestTable];
    Note that the TestTable in the TestSch schema is "owned" by dbo. And you can use the ALTER AUTHORIZATION statement to change the owner to somebody else. But it stays in the TestSch schema. So... don't think of schemas as owners. They are organizing groups.

    Add the account users to the Accountants role. Then grant the Accountants role the execute permission to the entire Schema_Accountants schema:

    GRANT EXECUTE ON SCHEMA::Schema_Accountants TO Accountants;

    That will apply to all Proc1 - Proc25 procedures that are in the Schema_Accountants schema.

    And for Managers:

    GRANT EXECUTE ON SCHEMA::Schema_Accounting_Managers TO Accounting_Managers;


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by reachrishikh Tuesday, May 21, 2013 8:18 PM
    Tuesday, May 21, 2013 8:02 PM
  • That makes so much more sense now.

    Thanks a lot for all the help, Rick.

    Tuesday, May 21, 2013 8:18 PM
  • Let's say I have a bunch of stored procedures in my database.

    Proc1
    Proc2
    .....
    Proc25

    Proc 26
    Proc 27
    .....
    Proc35

    That's a lot of procedures. If I manually try to sit and assign Exec permission on each procedure to each principle, it's going to take me a lot of time.

    Yes, that quickly goes out of hand. But as I understand from your previous posts, you have an application that will log in on behalf of the users. And since users will be WITHOUT LOGIN, they cannot access the database to run procedures directly. Thus, there is little reason not to grant permission to all procedures to a single role into which you put all users.

    Because, if there is tasks that only Account Managers are permitted to do, you are not letting unauthorised users through all the way to the app let them press save, and then smash a permission error in their face, are you?

    Most likely you will need your own permission system within the application that controls to the various forms and functions. That could be based on SQL Server roles or AD groups.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 21, 2013 9:51 PM
  • "Because, if there is tasks that only Account Managers are permitted to do, you are not letting unauthorised users through all the way to the app let them press save, and then smash a permission error in their face, are you?"
    I do have my own permission system within the application, Erland. I just want to assign proper permissions as an additional layer of defense.

    In case an employee's account ever gets hacked, and someone else logs into our system via VPN using the hacked employee's credentials, I don't want them getting access to virtually all stored procs in the system just because we didn't add in necessary permissions at the root, database server level.

    " And since users will be WITHOUT LOGIN, they cannot access the database to run procedures directly. Thus, there is little reason not to grant permission to all procedures to a single role into which you put all users."
    But since I will have one Login that will be impersonating those users at a given time, the permissions that apply to the user being impersonated will also temporarily apply to that login while impersonating, won't it?

    Wednesday, May 22, 2013 5:08 AM
  • In case an employee's account ever gets hacked, and someone else logs into our system via VPN using the hacked employee's credentials, I don't want them getting access to virtually all stored procs in the system just because we didn't add in necessary permissions at the root, database server level.

    But in that case, the intruder will have all the powers of that employee, and if that employee is a manager, the intruder will have those powers.

    " And since users will be WITHOUT LOGIN, they cannot access the database to run procedures directly. Thus, there is little reason not to grant permission to all procedures to a single role into which you put all users."
    But since I will have one Login that will be impersonating those users at a given time, the permissions that apply to the user being impersonated will also temporarily apply to that login while impersonating, won't it?

    Yes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 22, 2013 7:51 AM
  • Haha, you're right. But at least he will still be limited in the damage he can do, and won't get access to any other managerial roles.

    Anyway, what alternative would you suggest based on your experience?
    Wednesday, May 22, 2013 7:55 PM
  • What I suggested in my first post. Security is not simple, and security often means hassle. But if you get more secure for the hassle, it's good. In this case... I don't think you buy any more security by granting permissions per procedure. Even if you use schemas and roles, it quickly goes out of hand, so the risk is that users cannot use the system, because permissions are incorrectly granted.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 22, 2013 9:41 PM
  • Rick,
    I have a question.
    Let's say I run this code on the database right now -
    GRANT EXECUTE ON SCHEMA::Schema_Accountants TO Accountants;

    At this moment, I have created Proc1 to Proc25 under the Schema_Accountants, so by running the code above, the Accountants role gets access to all 25 of them.
    Suppose I create some additional stored procs under the same schema in the future, say Proc 36 to Proc 38, will I need to run this code again, or will the role automatically get EXEC permissions on all new stored procs created in that schema in the future as well?

    Friday, May 24, 2013 7:33 PM
  • Automatic. That is, the EXECUTE permission will apply to all procedures in the Schema_Accounts schema regardless of when they are created. The grant is to the schema. It doesn't get itemized out to the items in the schema.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, May 24, 2013 8:58 PM