June 28, 2018 at 2:16 am
Hi All,
I've two databases on a server , DB1 and DB2. There is User1 who has Read\Write on DB1 and no access to DB2. User2 has Read/Write on both , DB1 and DB2.
The scenario is like this : - User1 enters a record in a table in DB1 and a trigger on that table has to call an SP in DB2 (To which User1 has no access).
The SP is DB2 picks up data from some of its tables and inserts into DB3.
My question is , does grant execute on one SP has any risk of allowing the user (User1) to gain access to any objects ? Are there any risks ?
Thank You in advance.
Arshad
June 28, 2018 at 2:31 am
Only granting access will not work, as User1 does not have access to DB2.
Certificate signing is probably the way to go. I have an article on my web site that describes the technique in details, and there is a chapter dedicated to cross-database access. The article is quite long, and you need to read all to fully understand the last chapter.
http://www.sommarskog.se/grantperm.html
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 28, 2018 at 3:55 am
Erland Sommarskog - Thursday, June 28, 2018 2:31 AMOnly granting access will not work, as User1 does not have access to DB2.Certificate signing is probably the way to go. I have an article on my web site that describes the technique in details, and there is a chapter dedicated to cross-database access. The article is quite long, and you need to read all to fully understand the last chapter.
http://www.sommarskog.se/grantperm.html
Hi Erland,
Thanks. I'll go through your article . Concerned about the risks of enabling Cross-Database access and will not be approved by the client. Please note that the databases are in the same instance . Is there an option to grant any more suitable privileges along with 'GRANT EXECUTE' to make it work ?
Thank You
Arshad
June 28, 2018 at 4:24 am
As I said, certificate signing seems to be the way to go. I'm sorry, I don't have the time to elaboate now - and that's why I wrote an article about it.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 28, 2018 at 5:16 am
Cross Database Ownership chaining, by default, is disabled on SQL Server. This means that if 2 objects in different databases have the same owner then permissions will not be implicit.
Let's say you have a stored procedure, called proc1_sp and a table, called table1_tbl in a single database (DB1). Both proc1_sp and table1_tbl are owned by dbo. You have a Login called TestUser, which also has a user (TestUser) on DB1. Within that database you have granted TestUser permissions to execute proc1_sp, and that's it (no db_reader role for example). The SQL for proc1_sp might be something like:
ALTER PROC proc1_sp @id int AS
SELECT *
FROM Table1_tbl T1
WHERE T1.ID = @ID;
GO
If tsetuser were to run this procedure, then the data would be returned without a problem, as the permissions are implicit, as they have the same owner. Say, isntead, however, you had another table Table2_tbl with was owned by Steve. If you had the Same SP, but replaced Table1_tbl; with Table2_tbl then the SP will fail, as they don't have permission to select from the table.
The same is true for cross database queries, apart from even if the owner is the same, the implicit permission is lost cross database. If you want to enable the user to be able to SELECT from table2_tbl you'll need to also grant that permission. If that able is on a separate database, then a user linked to their login will be needed.
You can, like discussed above, enable Cross-Database Access, Enabling Cross-Database Access in SQL Server. That does come with caveat's, howerver, such as:
Database owners and members of the
db_ddladminor thedb_ownersdatabase roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases.Users with CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases that they might not have privileges in from the newly created or attached databases that they create.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 28, 2018 at 5:40 am
Yet a caveat with cross-DB chaining is that persons who have permissions to create users and procedures, can elevate their permissions to sysadmin, as I discuss in my article.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 1, 2018 at 12:43 am
Erland Sommarskog - Thursday, June 28, 2018 5:40 AMYet a caveat with cross-DB chaining is that persons who have permissions to create users and procedures, can elevate their permissions to sysadmin, as I discuss in my article.
How about 'Granting Execute' on that procedure and then assigning db_denydatareader and db_denydatawriter roles to the user, User1 on DB2. I did this with a simple message in the procedure body. It works fine till I use any object. Since I need only 'select' to User1 , I'll test by de-assigning db_denydatareader and keeping db_denydatawriter and let know.
Best Regards
Arshad
July 1, 2018 at 1:51 am
Arsh - Sunday, July 1, 2018 12:43 AMErland Sommarskog - Thursday, June 28, 2018 5:40 AMYet a caveat with cross-DB chaining is that persons who have permissions to create users and procedures, can elevate their permissions to sysadmin, as I discuss in my article.How about 'Granting Execute' on that procedure and then assigning db_denydatareader and db_denydatawriter roles to the user, User1 on DB2. I did this with a simple message in the procedure body. It works fine till I use any object. Since I need only 'select' to User1 , I'll test by de-assigning db_denydatareader and keeping db_denydatawriter and let know.
Best Regards
Arshad
As the user will have read to all objects , I made it work through the use of 'Execute As owner' .Thanks to all for the discussion on this.
Best \regards
Arshad
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply