I have multiple schemas within my database. I can code joins between these schemas and the same applies to my users. I want to restrict my user access to these schemas – users should not be allowed to perform joins between schemas. Their operations should be restricted to schema level only. Does this involve sets of GRANT and DENY on schema? Or would we need to add users to specifics groups which would have restricted access to the schemas? Any suggestions are appreciated! Thank you, Swathi
Using groups to control security is always a good move - you can easily add and remove people from groups as they come and go, instead of having to manually configure the security for each user. Once you have the groups setup, then yes you can apply specific GRANTs and DENYs on the schema.
I support @kev riley's answer with one caveat. Suppose we have schemas A and B, and users Bill and Ted. If Bill owns schema A and Ted owns schema B, and you grant Bill rights on A while denying him rights on B (and vice versa for Ted), then all is well. This is sort of like the old SQL 2000 notion, before schemas were introduced as logical namespaces: users owned subsets of tables. If, however, you **do** want both Bill and Ted to be able to access both schemas A and B, but prohibit them from using both schemas **at the same time** through a join, you cannot use access control to do this. As far as I'm aware, there is no mechanism to say "Grant some user/group access to schema A, and grant that same user/group access to schema B, but never allow that user to join a table in schema A to a table in schema B." The security controls aren't that fine-grained. It sounded like your question might have been more along those lines, so I wanted to throw out that warning.