April 9, 2009 at 9:10 am
Guys,
I was a bit surprised when I tested this, so wanted to know what your thoughts are.
Under what USER permissions do the TRIGGERS run?
Example:
I have a user TEST that has SELECT, INSERT permissions on a table Patient.
There is an INSERT Trigger on this table too, which puts the audit records into x_Patient table. But the user TEST is denied SELECT, INSERT permissions on the audit table x_Patient
So when I login under this user TEST and INSERT a record into Patient table, the TRIGGER is fired and the audit record gets recorded into the audit table x_Patient.
Is this normal behaviour? I believed the TRIGGER runs in the same USER context (permissions) as the original transaction. But the user TEST is denied any permission on the audit table, but the trigger still inserts audit records into the audit table.
Whats going on?
thanks for any insight,
_UB
April 9, 2009 at 9:14 am
Generally speaking, SQL chains permissions. If you have permission to insert into the main table, then it assumes (quite logically), that you have permission to perform all actions that inserting demands, including running that trigger.
Another example is if you have permission to execute a proc, which inserts into a table, but you don't have permission to insert into the table, the proc will still insert into the table. You just won't be able to manually insert, outside of the proc.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 9:40 am
Not quite.
I think I do understand the chain of permissions in the case of Stored Procs or Views or (SProcs with Dynamic Sql).
As far as I recollect, TRIGGERS run under the same USER context as the main transactions. But in this case, I made sure, I deny any permissions on the Audit table. This is what is confusing !!
Thanks for the response.
April 9, 2009 at 9:47 am
They do run under the same permissions as the user that caused them to fire (by whatever means).
I guess I'm not getting what you want to have happen. You have a table that someone is supposed to be able to insert into. You have a trigger on that table which inserts into a table they are denied insert access to. It seems your desired end result is that, even though they have insert permission on the first table, any attempt to insert will raise a permissions error and roll back.
Why not just deny them insert permission on the first table? That would be more direct and easier, and would have the same end result of not being able to insert anything into either table.
Or is there something else you want to accomplish with this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 10:09 am
This is what I am trying to do.
We have a Patient table, when users try to INSERT records into it a TRIGGER is fired that INSERTS a copy of the same record in an audit table [z_audit_Patient]
User has permissions only on the main table [Patient], but not on the audit table [z_audit_Patient]
I hope the code below will help...
--DROP TABLE dbo.Patient
CREATE TABLE dbo.Patient (NAME VARCHAR(20), Age INT)
--DROP TABLE dbo.z_audit_Patient
CREATE TABLE dbo.z_audit_Patient(Audit_ID INT IDENTITY(1,1), Name VARCHAR(20), Age INT, CREATED_BY VARCHAR(200), CREATED_ON DATETIME)
--DROP TRIGGER dbo.Tr_Patient_Insert
CREATE TRIGGER dbo.Tr_Patient_Insert
ON dbo.Patient AFTER INSERT
AS
BEGIN
DECLARE @Co INT
SELECT @Co = COUNT(*) FROM INSERTED
IF @Co <> 0
BEGIN
INSERT INTO dbo.z_audit_Patient
SELECT Name, Age, USER_NAME(), GETDATE() FROM INSERTED
END
END
--
-- Set permissions to a limited user 'TEST'
--
GRANT SELECT, INSERT ON dbo.Patient TO TEST
DENY SELECT, INSERT ON dbo.z_audit_Patient TO TEST
--
-- Now run under the limited user
--
EXECUTE AS USER = 'TEST'
--
-- Now insert records
--
INSERT INTO dbo.Patient VALUES ('Name3', 12)
INSERT INTO dbo.Patient VALUES ('Name4', 1)
--
-- see how the records are inserted into the tables after TRIGGERS
--
REVERT
SELECT * FROM dbo.Patient
SELECT * FROM dbo.z_audit_Patient
Patient Table
[Name] Age
Name3 12
Name4 1
z_Audit_Patient Table
ID [Name] Age Created_by Created_on
1 Name3 12 TEST 2009-04-09 10:52:57.637
2 Name4 1 TEST 2009-04-09 10:55:35.840
April 9, 2009 at 10:51 am
UB (4/9/2009)
We have a Patient table, when users try to INSERT records into it a TRIGGER is fired that INSERTS a copy of the same record in an audit table [z_audit_Patient]User has permissions only on the main table [Patient], but not on the audit table [z_audit_Patient]
So what do you want to happen in that case? Record goes into Patient but not audit? Record doesn't go into either?
Bear in mind that if a trigger fails and rolls back, it rolls back the change that fired the trigger too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2009 at 11:00 am
Gail,
But the problem is .... the record gets inserted into Patient table and z_audit_Patient table.
thats the puzzling point for me?
April 9, 2009 at 11:02 am
Judging by what you set up and what you expected to happen, it looks like what you want to do is NOT audit when certain users add data to the table. In other words, they should be able to add data to the Patients table, but when they do so, there will be no matching record in the audit log. Is that what you want?
If so, it's going to be easiest to accomplish that by having the trigger check what account is being used, and abort if it's a certain account.
if user = 'NonAuditUserName'
return;
Plug in the user name you don't want to audit, put that at the beginning of the trigger (right after "as"), and it should do what you seem to want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 11:08 am
UB (4/9/2009)
But the problem is .... the record gets inserted into Patient table and z_audit_Patient table.
Yes, it will. As Gus said, it's due to ownership chaining.
If that's not the behaviour you want, what do you want to happen?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2009 at 11:12 am
GSquared,
Okay may be the code gives an incorrect view of the problem I am trying to convey. I want the INSERT transactions to be audited from any user.
My understanding was that the database user, that starts the INSERT transaction needs INSERT permissions not only on the main table but also on the Audit tables to successfully INSERT record into audit tables.
But my understanding seems to be incorrect. Even though the database user has permissions only on the main table, the trigger, when fired, could successfully insert record into audit table along with main table.
hope this helps to clarify...
I greatly appreciate all the input...
_UB
April 9, 2009 at 11:27 am
Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects.
If you wanted explicit permissions on the referenced objects you would need to use the EXECUTE AS CALLER and then it would check permissions not only on the module itself, but also on any database objects that are referenced by the module.
See this in completeness here: http://msdn.microsoft.com/en-us/library/ms188354.aspx. Read the Remarks section and then also more info is in the link to Ownership Chains that explains in detail how that works. That should answer your question....
April 9, 2009 at 11:32 am
UB (4/9/2009)
Gail,But the problem is .... the record gets inserted into Patient table and z_audit_Patient table.
thats the puzzling point for me?
As mentioned in my first post on this thread, when you grant insert permission on the Patient table, you are implicitly granting permission to do all the actions necessary for an insert to succeed.
If the trigger was denied permission to insert into the audit table, it would have an error, and would have to roll back the whole operation, including the insert into Patient. That would, effectively, deny the insert permission on Patient. Thus, the trigger instead assumes that you want to be able to insert into Patient, and must thus be able to also insert into z_audit_Patient.
If you can't insert into z_audit_Patient, you also can't insert into Patient, because the trigger will have to roll it back.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 11:34 am
UB (4/9/2009)
GSquared,Okay may be the code gives an incorrect view of the problem I am trying to convey. I want the INSERT transactions to be audited from any user.
My understanding was that the database user, that starts the INSERT transaction needs INSERT permissions not only on the main table but also on the Audit tables to successfully INSERT record into audit tables.
But my understanding seems to be incorrect. Even though the database user has permissions only on the main table, the trigger, when fired, could successfully insert record into audit table along with main table.
hope this helps to clarify...
I greatly appreciate all the input...
_UB
That is correct. SQL works that way. It assumes that, if you have permission to do something, you have permission to do all the things that are necessary for that something to succeed.
There are a few cases where that breaks down, like it can fail on cross-server transactions. But for the most part, it's pretty good about making sure you can do what you're supposed to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply