I currently have a database that is split into two schemas:
ref. I have two database roles named
Users need to be able to
DELETE from the
data schema, and need to be able to
SELECT from the
Admins need to be able to
DELETE from both schemas.
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
Users database roles to allow users to
DELETE against the
data schema and to only
SELECT against the
ref schema while allowing the admins to
DELETE against both schemas?