StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You should wrap the selection of data from the view on the "other" Schema, within a stored procedure. Then grant execute rights on the stored procedure. Although the user will not have direct access to the view they are permitted access via the stored procedure.</p> <p>Here is an example walkthrough for you demonstrating the security principles as work:</p> <pre><code>USE DATABASE SANDBOX; --Create Logins CREATE LOGIN UserOne WITH Password='Hello123'; CREATE LOGIN UserTwo WITH Password='Hello123'; --Create Database Users CREATE USER UserOne; CREATE USER UserTwo; --Create the Test Schemas CREATE SCHEMA SchemaOne AUTHORIZATION UserOne; CREATE SCHEMA SchemaTwo AUTHORIZATION UserTwo; --Create a View on SchemaOne CREATE VIEW SchemaOne.ViewOne AS SELECT 1 as TestColumn; --Create a View on SchemaTwo CREATE VIEW SchemaTwo.ViewTwo AS SELECT * FROM SchemaOne.ViewOne; --Test that the SchemaOne EXEC('select * from SchemaOne.ViewOne') AS USER = 'UserOne' --1 EXEC('select * from SchemaTwo.ViewTwo') AS USER = 'UserOne' --The SELECT permission was denied on the object 'ViewTwo', database 'SANDBOX', schema 'SchemaTwo'. --Create a stored procedure to safely expose the view within SchemaTwo to UserOne who's default Schema is --SchemaOne. CREATE PROCEDURE SchemaTwo.proc_SelectViewTwo AS select * from SchemaTwo.ViewTwo; --Grant execute rights on the procedure GRANT EXECUTE ON SchemaTwo.proc_SelectViewTwo TO UserOne; --Test the EXECUTE AS LOGIN='UserOne'; Exec SchemaTwo.proc_SelectViewTwo; revert; </code></pre> <p>An alternative approach as suggeted in my comments would be to use a Database Role to control access to multiple schemas. Using the principals as defined in the solution above, you could use Database Roles like so:</p> <pre><code>EXEC sp_addrole 'CrossSchemaRole'; EXEC sp_addrolemember 'CrossSchemaRole','UserOne'; GRANT SELECT ON SCHEMA::SchemaOne TO CrossSchemaRole; GRANT SELECT ON SCHEMA::SchemaTwo TO CrossSchemaRole; EXECUTE AS LOGIN='UserOne'; select * from SchemaTwo.ViewTwo; revert; </code></pre> <p>Some suggested further reading:</p> <ul> <li><a href="http://msdn.microsoft.com/en-us/library/bb283235%28SQL.90%29.aspx" rel="nofollow noreferrer">Securing SQL Server</a></li> <li><a href="http://msdn.microsoft.com/en-us/library/ms191465%28SQL.90%29.aspx" rel="nofollow noreferrer">SQL Server Permission Hierarchy</a></li> <li>White Paper: <a href="http://www.microsoft.com/Sqlserver/2005/en/us/white-papers.aspx#sec" rel="nofollow noreferrer">Security Overview for Administrators</a></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload