July 22, 2011 at 4:39 pm
Hello,
I've come across an issue that perhaps has a simple (even obvious) resolution. But it has eluded me, alas. So any suggestions or pointers will be most appreciated.
I give an SQL login account permissions to execute a stored procedure. When I log in as that user and execute the sproc, it appears to run fine. However, when I check the records that are supposed to be updated or inserted, I find that they were not updated or inserted.
If I add update and insert permissions to the login, then log in as that user again and run the sproc, all the records are updated (and inserted) just fine.
I don't understand why I have to give the user (who's in the Public server role) permissions directly to the tables affected by the sproc. Obviously, something simple is missing because this is the why one would use sprocs in the first place: to limit the login account's access to the underlying SQL objects. So what's missing?
BTW, the affected tables also have triggers to insert changed records to audit tables. I had to give the login insert permissions to the audit tables, but this makes sense since the insert is triggered from the table--not the sproc.
The environment:
Windows Server 2003 with SQL 2008 version 10.50.1600.
Windows Server 2003 with SQL 2008 version 10.0.1600.
SQL Management Studio 2008 R2 SP 1.
(Both SQL servers give the same results.)
Many thanks for any ideas you can share.
July 22, 2011 at 4:45 pm
Without knowing the innards of the proc, I would venture a guess at ownership chaining.
Does this proc attempt to insert/update records in a different database?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 22, 2011 at 4:47 pm
It seems odd that you wouldn't get some sort of error, instead of having the insert or update just not happen. Anyway, this is a pretty comprehensive explanantion of the various issues.
July 22, 2011 at 7:13 pm
Jason provided a good lead mentioning Ownership Chaining. Cross-database calls will break an Ownership Chain unless a non-default configuration is put into place. Are these cross-database calls?
Chains can be broken within the same database as well. Here are some more thoughts on that line of thought:
Tess-205818 (7/22/2011)
Hello,I've come across an issue that perhaps has a simple (even obvious) resolution. But it has eluded me, alas. So any suggestions or pointers will be most appreciated.
I give an SQL login account permissions to execute a stored procedure. When I log in as that user and execute the sproc, it appears to run fine. However, when I check the records that are supposed to be updated or inserted, I find that they were not updated or inserted.
Do you receive any messages when the proc runs but the insert/update fails? If the insert/update fails but you do not receive an error then you may have a greedy TRY/CATCH block in place that need fixing. Could you post the trigger code with sensitive names obfuscated as needed? That will also tell us what kind of trigger it is, and may let us see the problem right away.
If I add update and insert permissions to the login, then log in as that user again and run the sproc, all the records are updated (and inserted) just fine.
I don't understand why I have to give the user (who's in the Public server role) permissions directly to the tables affected by the sproc. Obviously, something simple is missing because this is the why one would use sprocs in the first place: to limit the login account's access to the underlying SQL objects. So what's missing?
You should not have to grant additional permissions provided all objects are in the same database and have the same owner. Following the Ownership Chaining line of thought...are the proc, data tables and audit tables all in the same database? If they are all in the same database then who owns the proc, the data table and the audit tables? You can check the owners like this:
EXEC sys.sp_help @objname = N'schema_name.proc_name' ;
The second column in the first result set returned from the proc execution will show you the object owner.
Now check the table being referenced by the proc:
EXEC sys.sp_help @objname = N'schema_name.data_table_name' ;
Is the owner the same as the proc?
Now check an audit table being referenced by the trigger:
EXEC sys.sp_help @objname = N'schema_name.audit_table_name' ;
Is the owner the same as the data table?
BTW, the affected tables also have triggers to insert changed records to audit tables. I had to give the login insert permissions to the audit tables, but this makes sense since the insert is triggered from the table--not the sproc.
Triggers do not necessarily break an ownership chain. If the audit tables have the same owner as the data table (the one with the trigger on it) then the ownership chain will not be broken and an explicit GRANT permission on the audit table should not be necessary.
There are other reasons why the data update may be failing but Ownership Chaining is a good place to start.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 25, 2011 at 9:36 am
Thank you very much for the helpful response.
All of the database objects are within the same database, and all are under dbo. except for the audit tables, which are under a separate schema, x_audit.
I ran the sp_help statements you indicated and got the following results:
- The stored procedure Owner is dbo.
- All the tables have the Owner as dbo.
- The audit tables (under a different schema) have the Owner as db_owner.
I noticed that for the audit tables, the login has Insert privileges granted by db_owner. The select, update permissions on the table objects show dbo as the Grantor. The sproc Execute shows dbo as the Grantor.
When I try to revoke the db_owner granted Insert on the audit tables to try to switch it to the dbo grantor, I get an error: cannot grant or revoke to or from special roles. If I'm correct, dbo is in the db_owner role, but can this mis-match be why explicit permissions are needed?
Cheers,
Tess
July 25, 2011 at 9:38 am
i think if the procedure is doing dynamic SQL, the owner chaining rears it's head as well right? and the user needs access to the underlying objects being manipulated via dynamic SQL?
Lowell
July 25, 2011 at 9:38 am
Thank you very much for the link to Sommarskog blog! This is a very informative article.
Tess
July 25, 2011 at 10:01 am
Tess-205818 (7/25/2011)
I ran the sp_help statements you indicated and got the following results:- The stored procedure Owner is dbo.
- All the tables have the Owner as dbo.
- The audit tables (under a different schema) have the Owner as db_owner.
The audit table owner being different from the proc constitutes a broken ownership chain.
I noticed that for the audit tables, the login has Insert privileges granted by db_owner. The select, update permissions on the table objects show dbo as the Grantor. The sproc Execute shows dbo as the Grantor.
When I try to revoke the db_owner granted Insert on the audit tables to try to switch it to the dbo grantor, I get an error: cannot grant or revoke to or from special roles.
Someone must have explicitly changed the owner of the audit tables. Here's how you would change it back to dbo to match the schema they're in:
ALTER AUTHORIZATION ON OBJECT::dbo.audit_table TO dbo ;
If I'm correct, dbo is in the db_owner role, but can this mis-match be why explicit permissions are needed?
The dbo (a.k.a. The database owner) and the db_owner Database Role are different entities. There can only be one dbo for a database, and you can check the dbo like this:
SELECT SUSER_SNAME(owner_sid) AS dbo
FROM sys.databases
WHERE database_id = DB_ID() ;
There can be many members of the db_owner Role. It is implied that the dbo has all the rights granted by the db_owner Role without explicitly being a member of the Role. As a side note, members of the sysadmin Fixed Server Role enter all databases under the pretense that they are the dbo.
-----------------------------------------------------------------------
-- setup environment
USE master
GO
CREATE DATABASE test_ownership_chaining ;
GO
CREATE LOGIN not_dbo WITH PASSWORD = N'hello!'
GO
EXEC test_ownership_chaining.sys.sp_grantdbaccess
@loginame = N'not_dbo',
@name_in_db = N'not_dbo' ;
GO
USE test_ownership_chaining ;
GO
CREATE TABLE dbo.data_table
(
id INT IDENTITY(1, 1)
NOT NULL,
name VARCHAR(100),
CONSTRAINT [pk_dbo.data_table] PRIMARY KEY (id)
) ;
GO
CREATE TABLE dbo.data_table_audit
(
id INT NOT NULL,
name VARCHAR(100),
insert_date DATETIME CONSTRAINT [df_dbo.data_table_audit.insert_date] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.data_table_audit] PRIMARY KEY (id)
) ;
GO
CREATE TRIGGER dbo.data_table_after_insert ON dbo.data_table
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.data_table_audit
(id, name)
SELECT id,
name
FROM inserted ;
END
GO
CREATE PROC dbo.data_table_insert (@name VARCHAR(100))
AS
BEGIN
INSERT INTO dbo.data_table
(name)
VALUES (@name) ;
END
GO
GRANT EXEC ON dbo.data_table_insert TO not_dbo ;
GO
-----------------------------------------------------------------------
-- test #1 - make sure ownership chaining works :-)
-- succeeds because dbo.data_table and dbo.data_table_audit owners
-- are still the same
EXECUTE AS USER = 'not_dbo' ;
GO
EXEC dbo.data_table_insert
@name = 'Harold' ;
GO
REVERT
GO
-----------------------------------------------------------------------
-- test #2 - change the owner of the audit table from dbo to db_owner
-- to chow what happens when the ownership chain is broken
-- change the owner from dbo to db_owner
ALTER AUTHORIZATION ON OBJECT::dbo.data_table_audit TO db_owner ;
GO
-- now the insert fails because dbo.data_table and dbo.data_table_audit owners
-- are different
EXECUTE AS USER = 'not_dbo' ;
GO
EXEC dbo.data_table_insert
@name = 'Harold' ;
GO
REVERT
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 25, 2011 at 10:19 am
Lowell (7/25/2011)
i think if the procedure is doing dynamic SQL, the owner chaining rears it's head as well right? and the user needs access to the underlying objects being manipulated via dynamic SQL?
Correct. The first statement in a dynamic SQL batch does break an ownership chain. A new chain can be created within the context of that batch however. Here is a quick PoC to show how that can work:
USE master
GO
ALTER DATABASE test_ownership_chaining SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE test_ownership_chaining ;
GO
DROP LOGIN not_dbo ;
GO
-----------------------------------------------------------------------
-- setup environment
USE master
GO
CREATE DATABASE test_ownership_chaining ;
GO
CREATE LOGIN not_dbo WITH PASSWORD = N'hello!'
GO
EXEC test_ownership_chaining.sys.sp_grantdbaccess
@loginame = N'not_dbo',
@name_in_db = N'not_dbo' ;
GO
USE test_ownership_chaining ;
GO
CREATE TABLE dbo.data_table
(
id INT IDENTITY(1, 1)
NOT NULL,
name VARCHAR(100),
CONSTRAINT [pk_dbo.data_table] PRIMARY KEY (id)
) ;
GO
CREATE TABLE dbo.data_table_audit
(
id INT NOT NULL,
name VARCHAR(100),
insert_date DATETIME CONSTRAINT [df_dbo.data_table_audit.insert_date] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.data_table_audit] PRIMARY KEY (id)
) ;
GO
CREATE TRIGGER dbo.data_table_after_insert ON dbo.data_table
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.data_table_audit
(id, name)
SELECT id,
name
FROM inserted ;
END
GO
CREATE PROC dbo.data_table_insert (@name VARCHAR(100))
AS
BEGIN
DECLARE @sql VARCHAR(MAX) ;
SET @sql = 'INSERT INTO dbo.data_table (name) VALUES (''' + @name + ''') ;' ;
EXEC(@sql) ;
END
GO
GRANT EXEC ON dbo.data_table_insert TO not_dbo ;
GO
-----------------------------------------------------------------------
-- test #1 - show that Dynamic SQL breaks the ownership chain
-- fails because Dynamic SQL starts a new ownership chain and
-- not_dbo does not have insert rights on dbo.data_table
EXECUTE AS USER = 'not_dbo' ;
GO
EXEC dbo.data_table_insert
@name = 'Harold' ;
GO
REVERT
GO
-----------------------------------------------------------------------
-- test #2 - grant insert to not_dbo to show that a new ownership
-- chain can begin after the initial one is broken
GRANT INSERT ON dbo.data_table TO not_dbo ;
GO
-- now the insert succeeds because not_dbo can insert into dbo.data_table
-- and all other objects referenced have the same owner
EXECUTE AS USER = 'not_dbo' ;
GO
EXEC dbo.data_table_insert
@name = 'Harold' ;
GO
REVERT
GO
SELECT *
FROM dbo.data_table_audit ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply