Schemas and user rights

Question

I have 2 schemas, lets say sch1 and sch2. Each owned by different user.

I do want to grant SELECT rights on sch2.MyTable (but just this one object) to sch1 procedures/views AND allow to reference sch2.MyTable in sch1 foreign key constraints.

I do NOT want to make sch2.MyTable directly accessible to those, who uses sch2 procedures/views, but want to allow indirect access.

Basically sch2 is much more important. Owners of sch2 have control of sch1 as well, but not vice a versa. On the other hand sch1 has some data that is dependant on sch2 and they should be able to see them and should not allow delete data from sch2, while sch1 still has some dependencies on them.

Is that possible (maybe through synonyms)?

For no it seems- no, not possible. Unless take off part about "Views and Constraints" OR "different schema owners".

Answer 1

You need to look into using certificates to sign the stored procedures. This will allow users to access anothers tables.

Answer 2

Hope the following answer will help:

Syntax:

GRANT <PERMISSION> ON <SCHEMA>.<TABLE NAME>(<COLUMN NAME>) TO <GRANTEE> .

DENY  <PERMISSION> ON <SCHEMA>.<TABLE NAME>(<COLUMN NAME>) TO <GRANTEE> .

REVOKE <PERMISSION> ON <SCHEMA>.<TABLE NAME>(<COLUMN NAME>) TO <GRANTEE> .

<PERMISSION> = SELECT | UPDATE | REFERENCES

You can check the privileges by using the information schema.

SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES

Answer 3

You should be able to do this easily by just granting EXEC on the stored procedure(s) and SELECT on the view(s) to the user(s) you want to indirectly access the table. We do this in our office all the time, we don't grant permissions to underlying tables and only allow access to the underlying data through views/procedures.