SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 grant READ access to view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kimi86
Yak Posting Veteran

79 Posts

Posted - 03/12/2013 :  11:06:32  Show Profile  Reply with Quote
I have a view which is selecting rows from a table in a different database. I'd like to grant select access to the view, but not direct access to the base table. The view has a where clause restricting the number of rows.

Can I grant select to the view and not the base table, or do I need to switch to a stored procedure (not preferred at all).

James K
Flowing Fount of Yak Knowledge

3717 Posts

Posted - 03/12/2013 :  12:15:08  Show Profile  Reply with Quote
Whether you use a stored proc or a view, in order for the user to be able to retrieve the rows from a table in another database, you will need to do one of two things:

a) grant select permission to the user on the table in the remote database (which in turn means that that user must exist in that database as well).
b) enable cross-database ownership chaining and make sure that the owner of the view is the same as the owner of the table.

A bit more information about cross database ownership chaining is available here: http://msdn.microsoft.com/en-us/library/ms188676%28v=sql.105%29.aspx
Cross database ownership chaining has security implications, especially so if the two databases are owned by different entities, or if there are highly privileged users in either database who should not have access to data in the other database.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/12/2013 :  18:45:13  Show Profile  Reply with Quote
There is actually another way to do this for a Function or SP: Certificates. You don't need cross database ownership chaining and the user does not need access to the other database. It can't be a view though it only works for functions, triggers, and stored procedures. It is not for the faint of heart but DBAs and control boards may appreciate the limited user access.

Certificates and signed routines allow a local user to call a function or procedure that depends on access to remote database tables. The local user does not need to even be a user in that remote database. If you want to pursue this here are the basic steps needed to make it work:

certificates for cross db access without explicit user 
permissions to the dependent database or any tables therein


------------------------------------------------------------------
IN REMOTE DB (database with object to permission by certificates)

1. create a certificate
       
2. backup the certificate to files so we can create the same certificate in another database

3. create a user from the certificate (doesn't need to correspond to a login)

4. remove the private key from the certificate for added security (optional)

5. grant privs to this certificate user to the remote objects that you don't want regular users to access directly

------------------------------------------------------------------
IN LOCAL DB (database where the regular user will be calling SPs and functions)

6. create a certificate from the file backups in step 2

7. delete the files for security and general clean up reasons (optional)

8. "sign" [add signature] the SP/Function that needs permission to remote tables from the certificate

9. remove the private key of the signature for added security (optional)
------------------------------------------------------------------

Assuming the local user has permission to exec the local procedure they can now execute it
even though they don't have permissions themselves to the dependent remote tables or even
have access to the remote database.


Be One with the Optimizer
TG
Go to Top of Page

Kimi86
Yak Posting Veteran

79 Posts

Posted - 03/13/2013 :  05:40:00  Show Profile  Reply with Quote
thank for all ideas
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000