locked
3-Part Naming - Application Access RRS feed

  • Question

  • I have a SQL Server (SQL2016 SP2) with an application database and about 8 operational databases.  I have created a sql login for use by our web app which allows that app to execute stored procedures and read directly from the tables in the application database db_WebApp.  I have stored procedures in db_WebApp which 3-part name to other procs and tables in the operational databases (db_Oper1, db_Oper2, ... db_Oper8). 

    In order to enable reads/executes using 3-part naming within the db_WebApp stored procs, I also had to add the application login as a user to each of the affected operational databases and grant read permission on the affected tables and execute permission for the remote procs.

    However, now the application can just connect at will to the operational databases, which is not what I want, because I don't want the app to be able to directly reference schema in the operational databases (which can change, which is why I encapsulated access to them in stored procs in db_WebApp).

    Anyone have a good idea as to how I might be able to do this ?  I'm trying to use 3-part naming to access other databases on the same server, but I don't want to allow the sql user to directly connect to the databases.  I need to limit the access to 3-part naming (read, execute).


    Kathy Gibson

    • Moved by Tom Phillips Wednesday, January 2, 2019 5:11 PM Security question
    Wednesday, January 2, 2019 4:52 PM

Answers

All replies

    • Marked as answer by KathyGibson96 Wednesday, January 2, 2019 8:00 PM
    Wednesday, January 2, 2019 5:11 PM
  • Beware! First of all, do not enable the setting on server level, but do it per database if needed.

    Next, beware that if there are users in any of the database that are enabled for DB-chaining that have elevated permissions in the database, but should not have permissions on server level, they can now elevate their permissions to sysadmin by playing tricks in msdb.

    Enabling cross-database ownership chaining can  be the correct solution, but you need to understand the security risks.

    From a security perspective, the best solution is certificate signing, but it becomes a bit unmanageable if ther are many procedures. I discuss this technique in my article Packaging Permissions in Stored Procedures, and the last chapter covers cross-database access. (And explains in more detail about the msdb vulnerability that I mentioned.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 3, 2019 10:39 PM