Setting up users and admins with schema privileges

Question

I currently have a database that is split into two schemas: data and ref. I have two database roles named Users and Admins. Users need to be able to INSERT, SELECT, UPDATE, and DELETE from the data schema, and need to be able to SELECT from the ref schema. Admins need to be able to INSERT, SELECT, UPDATE, and DELETE from both schemas.

All users and admins interact with the data via stored procedures. Each table has a generated save, select, and delete stored procedure that the application uses (the stored procedure is in the same schema as the object it's hitting).

I previously had the database roles set up to execute against the schemas, but that's not really what I want, because then a user can insert, update, and delete against the ref schema via a stored procedure.

How can I set up security for the Admins and Users database roles to allow users to INSERT, SELECT, UPDATE, and DELETE against the data schema and to only SELECT against the ref schema while allowing the admins to INSERT, SELECT, UPDATE, and DELETE against both schemas?

• MarlonRibunal: There's a similar question at stack overflow. The question was "how can we maintain schema separation and prevent direct table access?" It might not directly answer your question but it will definitely lead you to what you need (Ownership Chaining). Here's the link: stackoverflow.com/questions/2212044/…
• gbn: Does this help? dba.stackexchange.com/a/6921/630

Answer 1

The part that should interest you is the grants. But I figured a "code/picture" is worth a thousand words.

use master
go
create database test1
go
use test1
go
create schema data
go
create schema ref
go
create table data.tbl1(a int);insert data.tbl1 values(1);
go
create table ref.tbl2(a int);insert ref.tbl2 values(2);
go
create proc data.tbl1_select as select * from tbl1
go
create proc data.tbl1_insert(@a int) as insert tbl1 values(@a)
go
create proc data.tbl1_update(@a int) as update tbl1 set a = @a
go
create proc data.tbl1_delete as delete from tbl1
go
create proc ref.tbl2_select as select * from tbl2
go
create proc ref.tbl2_insert(@a int) as insert tbl2 values(@a)
go
create proc ref.tbl2_update(@a int) as update tbl2 set a = @a
go
create proc ref.tbl2_delete as delete from tbl2
go
create role users
go
create role admins
go
grant execute on schema::data to admins
grant execute on schema::ref to admins
grant execute on schema::data to users
grant execute on ref.tbl2_select to users
go
use master
go
create login user1 with password='', default_database=test1, check_expiration=off, check_policy=off
create login user2 with password='', default_database=test1, check_expiration=off, check_policy=off
go
use test1
go
create user user1 for login user1;
create user user2 for login user2;
alter role admins add member user1;
alter role users add member user2;
go
setuser 'user1'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
go
setuser -- To go back to your user
go
setuser 'user2'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
select * from ref.tbl2

Here's the result:

The EXECUTE permission was denied on the object 'tbl2_update', database 'test1', schema 'ref'.
The SELECT permission was denied on the object 'tbl2', database 'test1', schema 'ref'.

• PollusB: Of course, you should never create a login with no password ;)

Answer 2

My advice:

• Set up 3 more schemas in somewhat of a n:1 (in fact 2:1) relationship to the schemas with the tables (i.e.: read_data, read_ref, change_data, change_ref)
• put the procedures in the new schemas (ins, upd, del to change, sel to read)
• then a GRANT EXECUTE on the schemas will do it (3 times for users, 4 times for Admins)

• Thomas Stringer: That's pretty clunky and cumbersome, I don't know if I'd recommend that strategy.
• DForck42: definatley can't do this. the application calls the stored procedures by schema.procname. by moving the procs to different schemas we completley break the app, and we're getting ready to do a final push to production. hence why i'm trying to clear up security.
• Andreas Wolter: A clean design helps. Otherwise you end up with problems like these. - you could use synonyms ans Execution Context to get around your specific problem with the app. Otherwise you're out of luck and have to Grant each proc separately
• Andreas Wolter: separation of duties and objects for those - that is a basic principle and by far not cumbersome if you think bigger than of 5 tables and procs