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 withdbo
as the owner - Create
training_modify
role withdbo
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 shouldTestOwner
own both the role and the schema? - Is it accurate that if I took away the
CREATE PROCEDURE
andCREATE 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 theEXECUTE
permission on the schema). If this is accurate – are there any pros/cons between this approach and changing the owner of the schema?
Best Answer
Ownership chaining is generally not something to worry about. It only implies permissions for DML (
INSERT
,UPDATE
, andDELETE
),SELECT
, andEXECUTE
operations. It does not allow forCREATE
,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:training
schema being owned bydbo
, and...training_modify
user being able to create stored procedures and/or functions in thetraining
schema,the
training_modify
user, who can't do DML against adbo.
table, can simply createtraining.[proc]
which does the DML and then execute that stored proc and it will allow for doing DML against thedbo.
table.Changing the owner of the
training
schema fixes this issue (assuming that you want no access todbo.
objects). You can create a database-only user (i.e.WITHOUT LOGIN
) and make that user the owner of thetraining
schema. This user will not be used for anything else; it only exists to allow the owningprincipal_id
of thetraining
schema to be different from that of thedbo
schema, thus breaking ownership chaining.Please note:
The documentation for CREATE ROLE states:
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 keepdbo
as the owner of the role (not the schema).The documentation for ALTER AUTHORIZATION, under "Special Cases and Conditions" states:
Meaning: you will need to
GRANT
permissions on the schema after executingALTER 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:dbo
and not allowingCREATE PROCEDURE
:dbo.
objects, then modules can be created intraining
schema (CON if lots of objects and/or different actions required, else just "meh").CREATE PROCEDURE
:dbo.
objects, either explicit permissions will need to be granted to thetraining_modify
role, allowing ad hoc access to those objects (CON), or modules need to be added todbo
schema to do that access andEXECUTE
/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 forCREATE PROCEDURE
, etc.