locked
Linked Server Stored Procedure Creation And Remote User Permissions RRS feed

  • Question

  •  am having an issue with creating a linked server connection, without exposing some security problems. My scenario is:

    • Create link from local DB A to remote DB B. This link will be named LINK. Created with as SA.
    • Create a user link from local user U to remote user P. Created as SA.
    • Create a Stored Procedure in DB A that performs: SELECT * FROM [LINK].[DB].[dbo].[Table]. Created as SA.

    My problem is that when SA is creating the PROC, there is an error saying that he has no permissions to the remote table. I can solve this issue by also linking the SA user to the remote user P. This however, effectively, requires SA rights, which is OK on initial installation, but not great for upgrades.

    During upgrades, the stored procedures listed above are either ALTERED or DROP/ADD with the newest code using SQL credentials that are DBO for the local DB A (i.e. not SA). In this case, the user cannot have the server level permission required to add a user mapping (ALTER ANY LOGIN). I thought of trying to pass NULL to the SP that adds linked users initially, but this leads to a big security issue as ALL local A users masquerade as remote user P.

    I had considered mapping all local users to a very weak account on the remote DB B, but it would appear that you need at least SELECT rights for the PROC creation not to fail. SELECT is still unacceptable since any local DB A user can view my data in remote DB B.

    Any thoughts? I'm not sure why it must required SELECT rights in order to create the PROC. I have tried giving only VIEW DEFINITION, but that was not sufficient.

     

    Thanks.

     

    Tuesday, January 10, 2012 10:04 PM

Answers

  • Well, the biggest issue is that the user that is creating the stored procedure must have SELECT permission on the remote table. As far as executing the PROC later, I am fine with a local user being mapped to the remote user that has db.

    Hehe. SQL Server has deferred name resolution, so that when you create a procedure that refers to a table that does not exist, SQL Server stays silent. Except, when you access an object on a linked server. Isn't that marvellous?

    I haven't test this, but maybe it works if you replace the references to the table in the linked server with a synonym. When you create the procedure, the synonym points to a local table. In fact, from what I said above, the synonym does not even have to exist at that point. Once the procedure has been created, you define the synonym to point to the linked server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Tuesday, January 17, 2012 6:07 AM
    Wednesday, January 11, 2012 10:56 PM

All replies

  • So you want to create a stored procedure A that retrieves data a table on server B, without users being able to access the table in server B directly?

    One way to do this is have a stored procedure which starts off:

    CREATE PROCEDURE access_remote_server WITH EXECUTE AS = 'proxyuser' AS

    proxyuser, and only proxyuser maps to a login on the remote server.

    However, for this to work, the database must be set to TRUSTWORTHY, and be owned by sa or corresponding. If there is any user in the database who is db_owner or db_securityadmin without being sysadmin, you have created a security hole, as these guys could elevate their permissions.

    An alternative to use certificate signing, and sign the procedure with a certificate that also is in master. A login created from the certificate has IMPERSONATE permissions on proxyuser.

    I have an article on my web site where I discuss both EXECUTE AS and certificate signing, but there is no example for this particular case. Nevertheless, the article should get you started about the possibilities. See
    http://www.sommarskog.se/grantperm.html

    (I'm sorry, but I don't have the time right to produce a full-fledge example.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 10, 2012 10:59 PM
  • An alternative to use certificate signing, and sign the procedure with a certificate that also is in master. A login created from the certificate has IMPERSONATE permissions on proxyuser.

    I left one thing out here: the procedure would do

    EXECUTE AS LOGIN = 'proxyuser'

    before accessing the remote table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 11, 2012 8:20 AM
  • Well, the biggest issue is that the user that is creating the stored procedure must have SELECT permission on the remote table. As far as executing the PROC later, I am fine with a local user being mapped to the remote user that has db.
    Wednesday, January 11, 2012 4:04 PM
  • Well, the biggest issue is that the user that is creating the stored procedure must have SELECT permission on the remote table. As far as executing the PROC later, I am fine with a local user being mapped to the remote user that has db.

    Hehe. SQL Server has deferred name resolution, so that when you create a procedure that refers to a table that does not exist, SQL Server stays silent. Except, when you access an object on a linked server. Isn't that marvellous?

    I haven't test this, but maybe it works if you replace the references to the table in the linked server with a synonym. When you create the procedure, the synonym points to a local table. In fact, from what I said above, the synonym does not even have to exist at that point. Once the procedure has been created, you define the synonym to point to the linked server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Tuesday, January 17, 2012 6:07 AM
    Wednesday, January 11, 2012 10:56 PM