I'm a SysAdmin who's inherited some DBA duties. I did a DBA course back in the MSSQL 7.0 days, but al lot has changed since then. Right now I'm trying to get my head around permissions.

I've got AD users in an AD group. I've created a login on the SQL2012 instance got the AD group and mapped that login as a user on the database in question. Assigning permissions on the database or on tables to that user is having the desired result. So far, so good.

What I'm trying to do now is set specific, granular permissions on a schema to allow devs to create tables. In general terms, across the entire database, we're trying to set things up so that developers can add data, but not change it or remove it. That's pretty important to us on an auditing front. If data genuinely needs to be changed because it's incorrect, we have a process where a sysadmin makes the change using a specific app that only updates a "record_valid_to" field on the "bad" row and then creates a new row with the correct data. The app then creates an entry in a table in a different schema, recording what was changed, when, by who, and why. So yeah, Devs need to add stuff, but never change or delete stuff that's already been added.

At the table level, the Insert permission works beautifully. Devs can insert new rows into tables, but they can't update data in existing rows, or delete rows. The idea behind this permission of non-destructive change is exactly what I'm shooting for almost everywhere.

At the schema level, I want to be able to give devs Create Table and the ability to create new columns in existing tables. They have a dev copy of the DB where they can create and delete all they like while they're getting things right. When committing changes to the production DB they damn well better be only creating the table or column once. =)

Unfortunately, as far as I can work out, I can't do what I want with permissions. In order to create a table the user needs the Create Table permission on the database, and the Alter permission on the schema. If I grant Alter at the Schema level that seems to give them Alter to all sub-objects of the schema too. They can then change and delete existing data and tables, which I don't want at all.

My first question is, am I missing something? Am I understanding it right so far, or is there a way of granting just the permissions I want at the schema level without (what would be called in NTFS land) inheritance?

Second question is, if I am understanding it right, what's next? I've been reading a bit about triggers and using them to block certain actions. Should I be granting the user Alter and then setting up a database level trigger to block the user from doing anything destructive with their Alter?

Another possibility is Application Roles. If we created a small one-shot app that let the devs create tables and add columns to existing tables, then gave that app the Alter permission on the schema, that feels like it would do the job, if slightly clumsily.

I'd really welcome any suggestions, corrections, explanations or pointers.

Cheers,

Ryan

ALTER on schema level does imply ALTER on the sub-objects, so yes, they can drop columns all day and cause data loss that way. However, ALTER does not imply rights to delete, or even view data. Look at this repro:

CREATE USER pelle WITHOUT LOGIN
go
CREATE SCHEMA sch
go
GRANT CREATE TABLE TO pelle
GRANT ALTER ON SCHEMA::sch TO pelle
GRANT SELECT,INSERT ON SCHEMA::sch TO pelle
go
EXECUTE AS USER = 'pelle'
go
CREATE TABLE sch.tbl (a int NOT NULL)
go
INSERT sch.tbl (a) VALUES (8)
go
DELETE sch.tbl WHERE a = 8
go
REVERT
go
DROP USER pelle
DROP TABLE sch.tbl
DROP SCHEMA sch

This produces:
Msg 229, Level 14, State 5, Line 1
The DELETE permission was denied on the object 'tbl', database 'tempdb', schema 'sch'.

My guess is that you have made the AD group owner of the schema.

You might want to become familiar with this poster of permissions

Poster: Permissions Poster

Thanks Erland,

I'm not sure what I got wrong in my original testing. I've revisited today after reading your post and can confirm that things are behaving as you say they should. Having granted the user Alter at the schema level and Select and Insert at the database level, the user is now able to create tables, add columns to tables, view data and add rows, but not delete rows or modify data. When granted Update on specific columns he can change data in just that column, as you'd expect.

To be honest, I was rather hoping this was just a misunderstanding on my part. That's the easiest option to fix. =)

Thanks again!

Ryan

Thanks Rick,

I have the 2012 version of that poster on my desk right now, although even at A3 it's a little hard to read. The problem is that even looking at the "Database permissions - schema objects" section I couldn't really work out whether granting alter on the schema was supposed to also grant alter on the data within the tables in that schema or not.

Having spent a fair bit of time with that poster in the last few days I think it's intended as a reminder for people who work with this stuff all the time, rather than as a learning aid. There's just not room on a poster to be sufficiently verbose that someone who doesn't already understand the flow of permissions can pick them up.

Don't get me wrong, it's a great poster and probably very useful to people who spend lots of time with SQL permissions, but lots of it went over my head.

Cheers,

Ryan

Yes, the system is pretty confusing.

Granting ALTER on a schema will let that user alter any table, view, or proc in that schema. Not change the data, just change the structure of the tables, etc.

Granting SELECT, UPDATE, and DELETE on a schema will let someone update data in any table in the schema.

Although this might be "good enough" security for your developers (who are at least marginally trustworthy), it's not real security.

This solution violates an important rule of SQL Server database security: "Don't let users create objects that will be owned by other users."  Doing so almost always enables privilege escalation using ownership chains.

Here, for instance, pelle can create a trigger on a table that will execute with intact ownership chains across the whole database:

CREATE USER pelle WITHOUT LOGIN
 go
 CREATE SCHEMA sch
 go
 GRANT CREATE TABLE TO pelle
 GRANT ALTER ON SCHEMA::sch TO pelle
 GRANT SELECT,INSERT ON SCHEMA::sch TO pelle
 go
 EXECUTE AS USER = 'pelle'
 go
 CREATE TABLE sch.tbl (a int NOT NULL)
 go
 INSERT sch.tbl (a) VALUES (8)
 go
  select * from sch.tbl
 go
 create trigger t on sch.tbl after insert as
 begin
   delete from sch.tbl
 end
 go
  INSERT sch.tbl (a) VALUES (9)
 go
 select * from sch.tbl
 go
 REVERT
 go
 DROP USER pelle
 DROP TABLE sch.tbl
 DROP SCHEMA sch

More broadly, see the brand new  Database Lifecycle Management (DLM) guide on MDSN for resources on how to manage schema creation and the migration of schema changes between environments. 

Thanks David,

Sorry it took me a little while to respond, I've been reproducing that and trying to get my head around what's going on.

In my test DB, the owner on both the schema and the table is 'dbo'. So a trigger attached to that table will run as dbo, meaning it's got the right to delete things. I can see that happening.

According to   http://technet.microsoft.com/en-us/library/aa258254(v=sql.80).aspx
Permissions

CREATE TRIGGER permissions default to the table owner on which the trigger is defined, the sysadmin fixed server role, and members of the db_owner and db_ddladmin fixed database roles, and are not transferable.

I haven't explicitly granted the user any of the above, but he's inheriting it from somewhere. I'm guessing it's the Create Table or Create Procedure permissions at the database level, or Alter Schema on the schema that table is in.

So I guess the big question is, am I barking up the wrong tree? Is there a way to allow users to create tables and then not delete from those tables that doesn't have backdoor privilege escalation using things like triggers and chained ownership? Or should I be going back to first principals, taking "Don't let users create objects that will be owned by other users" to heart and just telling Devs they can't create tables in the prod database?

We could probably set things up so that devs can create tables and things in a dev copy of their database, but table creation in prod has to be done by an email to the admins. It's obviously more overhead, but it's starting to seem like the only way of doing this sort of thing securely.

Permissions             &nb

Or should I be going back to first principals, taking "Don't let users create objects that will be owned by other users" to heart and just telling Devs they can't create tables in the prod database?

We could probably set things up so that devs can create tables and things in a dev copy of their database, but table creation in prod has to be done by an email to the admins. It's obviously more overhead, but it's starting to seem like the only way of doing this sort of thing securely.

I agree with David entirely.

For deployment into production, you need a rigid and formal way of deployment, and you don't achieve that by giving permissions to random developers to create objects.

Exactly how rigid depends on your situation. If there are senior developers you trust to be db_owner, that may be good enough. Personally, I prefer something that is based on version control where you can trace what was installed when.

OK, thanks so much to all three of you for the advice. We're going to formalise the process a bit and have table creation handled by admins after an internal approval process.

Having learned a little about creation of triggers and ownership chaining when those triggers fire, I'm now a little worried about how we're handling user-created stored procedures.

Right now, the devs are creating SPs that run complex queries, so that we can give end users a basic front-end app that will let them call those SPs from a nice simple GUI.

First question is, am I going to have similar ownership chaining issues with the SPs, or do SPs always run as the user who called them?

Secondly, is there an associated risk to allowing devs to create SPs? Can they use this permission to create a stored procedure that can then do stuff they wouldn't normally be able to do?

Third question I guess would be, is assigning the Create Procedure permission to the dev user or role at the database level the 'right' way of doing this, or is that too broad a permission?

Thanks again for everything, guys!

>First question is, am I going to have similar ownership chaining issues with the SPs,

Yes.

>do SPs always run as the user who called them?

Stored procedures by default run as the user who is invoking them "EXECUTE AS CALLER" is the default.  However the caller doesn't need permission to access objects owned by the same user as the stored procedure. Ownership chaining will suppress the permissions checking for access to these objects. 

>Secondly, is there an associated risk to allowing devs to create SPs? Can they use this permission to create a stored procedure that can then do stuff they wouldn't normally be able to do?

  So if you grant a dev CREATE PROCEDURE and ALTER and EXECUTE on the DBO schema then she can write a stored procedure that reads. eg:

use tempdb
go
create user joe_dev without login

grant create procedure to joe_dev
grant alter on schema::dbo to joe_dev
grant execute on schema::dbo to joe_dev

create table secret(id int)

go

execute as user='joe_dev'
go
create procedure joe_is_the_king
as 
begin
  select * from secret
  delete from secret
end
go
exec joe_is_the_king
go
drop procedure joe_is_the_king
go
revert

>Third question I guess would be, is assigning the Create Procedure permission to the dev user or >role at the database level the 'right' way of doing this, or is that too broad a permission?

CREATE PROCEDURE by itself is harmless. It's granting ALTER on the dbo schema that's the problem.  The "Don't let users create objects that will be owned by other users."  rule has to be enforced for stored procedures, tables (because of triggers), views and synonyms, as these can all be used to perform arbitrary DML on objects owned by the other user.

David

To add what David says, you could have a dev schema which owned by someone else than dbo. devs would be permitted to create procedures in this schema. Because the owner is different from dbo, there is no ownership chaining. Users would then need SELECT etc permissions on the tables to be able to run the procedures. Important is to make sure that developers cannot transfer ownership of procedures to dbo!

I don't really recommend this, because I don't like users having access to the tables directly, and I think a regimented deployment is to prefer anyway.

OK, I've had a discussion with all the stakeholders in this project and we've come to the agreement that proper security and change control is the only sensible path. So hooray for that.

Current plan is for devs to only have any sort of schema permissions on the dev copy of their DB. All table changes and additions on prod will be actioned by a DBA following a change request process.

I'd still like devs to be able to create stored procedures for end users to run, but only if we can do it securely. I'm planning to create a schema called something like sp, which would be owned by a dedicated role called sp_owner. Devs would not get that role, they'd just be granted create procedure on the sp schema. Because the procedures would be owned by sp_owner and all the data tables and views would be owned by dbo, I'm hoping that completely removes ownership chaining as an issue for dev-created procedures.

The remaining questions on that front are:

1. What permission would I also need to grant a dev to allow him to delete or edit a procedure in the sp schema?

2. Can I actually do this without also giving them the right to change owner on the procedure?

3. Is there anything else obviously wrong with this plan that would accidentally give dev users more access than I'm trying to give them?

I also wanted to say thanks again for all your help guys. I do a fair amount of posting an helping out on network and sysadmin related topics on other forums and I'm always nervous helping someone who's as clearly out of their depth as I am right now. The temptation to say "just hire someone who knows what they're doing" is always there. I really appreciate you both taking the time to point me in the right direction and point out the obvious mistakes that I didn't know enough to know I was making. It's made a huge difference.

Cheers,

Ryan

As a general comment: you can find many of these answers in Books Online, in the T-SQL Reference. Every topic has a Permissions sections which is towards the end, right before the examples. I realise that with important things like security in mind that you want to double-check, but I point it out nevertheless.

1. For ALTER PROCEDURE you need ALTER permission on the procedure. To drop a procedure, you need CONTROL on the procedure or ALTER on the schema. In practice, this means that you should grant programmers ALTER on the sp schema.

2. Yes. To change object owner, you need TAKE OWNERSHIP, and this is not implied by ALTER.

3. Now, this is the difficult question, because finding security holes is about finding out what you did not think of. But it looks good. I nevertheless has a suggestion for impovement though, that I will come to later.

Many of these simple scenarios are easy to test by creating a script that sets up the required roles and permission and then creates a user without login and then impersonate that user. Here is a script that illustrates your thinking:

CREATE ROLE spowner
CREATE ROLE dev
go
CREATE SCHEMA sp AUTHORIZATION spowner
go
GRANT CREATE PROCEDURE TO dev
GRANT ALTER, EXECUTE ON SCHEMA::sp TO dev
go
CREATE USER somedev WITHOUT LOGIN
EXEC sp_addrolemember dev, somedev
go
EXECUTE AS USER = 'somedev'
go
CREATE PROCEDURE sp.somesp AS PRINT 'first version'
go
EXEC sp.somesp
go
ALTER PROCEDURE sp.somesp AS PRINT 'second version'
go
EXEC sp.somesp
go
PRINT 'Attempting to take ownership'
ALTER AUTHORIZATION ON sp.somesp TO somedev
go
PRINT 'Attempting to make dbo the owner'
ALTER AUTHORIZATION ON sp.somesp TO dbo
go
PRINT 'Attemping to move procedure to the dbo schema'
ALTER SCHEMA dbo TRANSFER sp.somesp
go
DROP PROCEDURE sp.somesp
go
PRINT 'EXEC should fail, since procedure has been dropped.'
EXEC sp.somesp
go
REVERT
go
-- Cleanup
DROP SCHEMA sp
DROP USER somedev
DROP ROLE spowner
DROP ROLE dev

So what could be improved? Well, rather than granting your developers CREATE PROCEDURE on database level, you could create a stored procedure that accepts the name of a stored procedure in the sp schema and then creates that procedure with a dummy body. You would sign that procedure with a certificate, and grant a user created from that certificate CREATE PROCEDURE. This is a technique that I describe in detail in an article on my web site: http://www.sommarskog.se/grantperm.html.