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?

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'.

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)