1

I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role:

  • Select, update, insert, and delete permissions on any object in that schema
  • Create any object in that schema
  • Drop any object in that schema
  • No permissions on any other object outside that schema (except for those granted through the public role
  • No create/drop/alter permissions for the schema itself
  • The role and individual users should not require membership in db_ddladmin, db_datareader, db_datawriter, or any other default security roles

In simple terms, I want the users in the role to be able to do anything they want within the schema without affecting/seeing anything outside the schema or the schema itself. What is the least privilege to grant this type of access?

My approach so far:

  • Create training schema with dbo as the owner
  • Create training_modify role with dbo as the owner
  • Grant all permissions on the schema to the role
  • Grant create objects to the role
  • Add a user to the role

Here is the code for the above steps:

CREATE SCHEMA training AUTHORIZATION dbo;

CREATE ROLE training_modify AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::training TO training_modify;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO training_modify;

EXEC sp_addrolemember 'training_modify', 'example_user';

Is this the right approach? Does it have any unintended consequences? I am most worried about how ownership chaining may affect this approach negatively with dbo being the owner of the schema and the role (as well as other schemas/objects in the DB), and the alter permission being granted on the schema.

Also:

  • Should dbo still own the role? or should TestOwner own both the role and the schema?
  • Is it accurate that if I took away the CREATE PROCEDURE and CREATE FUNCTION permissions, this would prevent any DML on objects outside the specific schema? I don't think I care if they can create procedures, although I would like them to be able to execute procedures/functions (which I think would be covered by the EXECUTE permission on the schema). If this is accurate - are there any pros/cons between this approach and changing the owner of the schema?
  • Ownership chaining is not something to worry about. It only implies permissions for DML (INSERT, UPDATE, and DELETE), SELECT, and EXECUTE operations. It does not allow for CREATE, ALTER, DROP, etc. – Solomon Rutzky May 19 at 20:45
  • so would a user in this role (with this approach) be able to DML, SELECT, or EXECUTE objects owned by dbo that are outside of the training schema? I've read about ownership chaining a lot, but still haven't been able to get it through my dense skull. – OverflowingTheGlass May 19 at 20:50
  • 1
    Maybe. Though it should be fairly easy to test. Try it out and see what happens. If it does allow for those operations of dbo. objects, then just change the owner of the training schema to be training_modify. That might reduce the need for the other permissions on the schema itself. But it might allow for something else. I would have to check the doc, and I don't have time right now. Worst case, just create another user named training_owner and assign ownership of training schema to that user. Then you don't have to worry about dbo. stuff. – Solomon Rutzky May 19 at 20:54
  • 1
    I just did a simple test and it does appear that with the training schema being owned by dbo and the training schema allowing users to create their own procedures (in the training schema), a person could just create a procedure to do DML / SELECT on dbo. objects. And then I created a new user and made them the owner of the new schema:: CREATE USER [TestOwner] WITHOUT LOGIN; ALTER AUTHORIZATION ON SCHEMA::[Test] TO [TestOwner];, and that seems to prevent that access, as I suggested earlier. – Solomon Rutzky May 20 at 7:56
  • @SolomonRutzky should dbo still own the role? or should TestOwner own both the role and the schema? when I altered the authorization on the schema, the user in the role seemed to lose all permissions, regardless of whether dbo or TestOwner owned the role. – OverflowingTheGlass May 20 at 13:32
1

Ownership chaining is generally not something to worry about. It only implies permissions for DML (INSERT, UPDATE, and DELETE), SELECT, and EXECUTE operations. It does not allow for CREATE, ALTER, DROP, etc.

The tricky part / nuance here is that, by default, objects have a NULL owner, meaning that their ownership is implied as being the owner of the schema in which they exist. So, in this particular case, with:

  1. the training schema being owned by dbo, and...
  2. the training_modify user being able to create stored procedures and/or functions in the training schema,

the training_modify user, who can't do DML against a dbo. table, can simply create training.[proc] which does the DML and then execute that stored proc and it will allow for doing DML against the dbo. table.

Changing the owner of the training schema fixes this issue (assuming that you want no access to dbo. objects). You can create a database-only user (i.e. WITHOUT LOGIN) and make that user the owner of the training schema. This user will not be used for anything else; it only exists to allow the owning principal_id of the training schema to be different from that of the dbo schema, thus breaking ownership chaining.

Please note:

  • The documentation for CREATE ROLE states:

    The owner of the role, or any member of an owning role can add or remove members of the role.

    Meaning: the owner of a role does not affect ownership chaining. HOWEVER, anyone in the training_modify role probably shouldn't be able to add/remove others, so best to keep dbo as the owner of the role (not the schema).
     

  • The documentation for ALTER AUTHORIZATION, under "Special Cases and Conditions" states:

    When ownership is transferred, permissions on schema-contained objects that do not have explicit owners will be dropped.

    Meaning: you will need to GRANT permissions on the schema after executing ALTER AUTHORIZATION on it.
     

  • As far as pros/cons between, that depends greatly on what objects will exist in the dbo schema, how they will be accessed, and what the training is supposed to accomplish and how flexible this setup needs to be. I'm not privy to any of that info, so for now I will simply generalize by saying:

    • schema owner is dbo and not allowing CREATE PROCEDURE:
      1. CON: training person cannot play with creating procs and functions
      2. ???: if training folk need access to dbo. objects, then modules can be created in training schema (CON if lots of objects and/or different actions required, else just "meh").
    • changing schema owner and allowing CREATE PROCEDURE:
      1. PRO: training person can play with creating procs and functions
      2. ???: if training folk need access to dbo. objects, either explicit permissions will need to be granted to the training_modify role, allowing ad hoc access to those objects (CON), or modules need to be added to dbo schema to do that access and EXECUTE / SELECT permission can be granted on those (CON if lots of objects and/or different actions required, else just "meh").
         

    Assuming there's not too much in the dbo. schema and/or the required access is relatively simple, I would opt for changing the schema owner and allowing for CREATE PROCEDURE, etc.

| improve this answer | |
1

Is this the right approach? Does it have any unintended consequences? I am most worried about how ownership chaining may affect this approach negatively with dbo being the owner of the schema and the role (as well as other schemas/objects in the DB), and the alter permission being granted on the schema.

The ALTER permission in the training schema will only grant ALTER permissions to objects in that schema, as stated by Solomon Rutzky in the comments, ownership chaining doesn't apply for DDL, only DML/Execute.

Ownership chaining provides a mechanism for providing limited to access to objects that a user does not have explicit permissions on by chaining the ownership of one object to another. For example, if you have a stored procedure in the training schema that queries a table in the dbo schema and both the procedure and table are owned by dbo then users who have permission to execute the procedure will be able to get the query results from the table they otherwise do not have access to.

This doesn't provide any direct access to the table, the only access to the table is via the stored procedure. This is a method for granting controlled access to an object without needing to expose the underlying object. See this link for more info.

In your specific scenario, to allow users to perform DML/Execute on objects in another schema, you would need a stored procedure/function/view that is owned by dbo and executes the DML/Execute statements against other schemas also owned by dbo. Without this, your users will be prevented from running the commands against schemas/objects outside of training.

| improve this answer | |
  • 1
    Regarding the last paragraph: the tricky part / nuance here is that, by default, objects have a NULL owner, meaning that their default ownership is implied as being the owner of the schema in which they exist. So, in this particular case, with the training schema being owned by dbo, the training_mod user who can't do DML against a dbo. table can simply create training.[proc] which does the DML and then execute that and it works. As I just noted in a comment on the question, changing the owner of the training schema fix this issue. – Solomon Rutzky May 20 at 8:05
  • Thank you both for the thoughtful answers! So is it accurate that if I took away the CREATE PROCEDURE and CREATE FUNCTION permissions, this would prevent any DML on objects outside the specific schema? I don't think I care if they can create procedures, although I would like them to be able to execute procedures/functions (which I think would be covered by the EXECUTE permission on the schema). If this is accurate - are there any pros/cons between this approach and changing the owner of the schema? – OverflowingTheGlass May 20 at 12:58
  • @OverflowingTheGlass (and HandyD): there are too many details being discussed in the comments here and on the question, so I put my contributions into an answer. – Solomon Rutzky May 20 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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