Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer
    
so- is should take off part about "Views and Constraints". Views cannot be signed, and constraints will not be possible to make if schema sch1 users are not able to access that table from sch2. –  Jānis Aug 10 '12 at 8:19
    
I believe that the DBA (or member of the db_owner fixed database role) would need to create the constraints. The same probably applies to views. –  mrdenny Aug 10 '12 at 8:22
    
wow, that really works! is sch1 and sch2 are owned and managed by different users and none of them have permissions to other schema tables, db_owner still can make constraints from one schema table to other schema table. –  Jānis Aug 10 '12 at 8:51

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

share|improve this answer

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.

share|improve this answer
    
If i give "Select" permission on table, the user is able to select data from table directly. Ownership chaining would solve this, but that does not work as well because schemas are owned by different users. –  Jānis Aug 10 '12 at 7:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.