Security and Audit Trail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Security and Audit Trail

Author

Topic

t1g312
Posting Yak Master

148 Posts

Posted - 2005-07-04 : 02:15:51

Hi All,

I have created a role that grants exec access to all my stored procedures. Unfortunately, it does not allow my audit trail trigger to execute. What do I do?

Thanks in advance,

Adi

-------------------------
/me sux @sql server

Kristen
Test

22859 Posts

Posted - 2005-07-04 : 03:27:32

Have the audit trail trigger call an SProc?

What's it doing that is denied?

Kristen

t1g312
Posting Yak Master

148 Posts

Posted - 2005-07-04 : 03:36:49

quote:
Originally posted by Kristen

Have the audit trail trigger call an SProc?

What's it doing that is denied?

Kristen

I have an audit trail trigger for each of the tables. Obviously the audit trail trigger tries to read the data from the table but I haven't even given table read access to the role.

Adi

-------------------------
/me sux @sql server

Kristen
Test

22859 Posts

Posted - 2005-07-04 : 10:16:54

Doesn't sound right, the trigger should run with the rights that the SProc was created with. (Unless there is an action on a different database)

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-04 : 21:33:58

i want to help but it's quite confusing..

you're saying the role grants execute rights on SPs, now if a user that was granted by the role executes the SP which does something on the table with a trigger, the trigger doesn't function?

check the trigger for any specific conditions that might be the cause,check if the trigger is enabled or better yet post the trigger

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-07-05 : 04:29:00

I knocked up a specific example here.

Created a pair of tables and a "Save" SProc (as "sa")

Create a trigger on Table1 that inserted into Table2

Created a user

Granted EXECute [on the SProc] to the User

REVOKEd SELECT, INSERT, UPDATE, DELETE on both tables from the user

The user can execute the SProc just fine ...

Which is how I think it should be (PROVIDED that the Sproc and Tables are all in the same database)

Dunno if that helps though?

Kristen

t1g312
Posting Yak Master

148 Posts

Posted - 2005-07-06 : 02:07:23

Hi Kristen,

Thanks for the reply. I'm using a slightly modified version of nr's trigger and I get the error "no PK on table tablename". When I grant read access to the table, it works just fine.

Adi

-------------------------
/me sux @sql server

Kristen
Test

22859 Posts

Posted - 2005-07-06 : 03:18:58

If you post the code you are using we can take a look for you

Kristen

t1g312
Posting Yak Master

148 Posts

Posted - 2005-07-06 : 04:26:18

Hi Kristen,

The trigger seems to be failing at this point:

-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end

And I get the error no PK...

I am assuming that it is because the trigger does not have select access to the table. I can post the entire trigger if you want me to.

Adi

-------------------------
/me sux @sql server

Kristen
Test

22859 Posts

Posted - 2005-07-06 : 13:13:23

As you haven't posted the code I can't see where @PKCols is set.

Either way, @PKCols is NULL and thus it is generating the error [deliberately].

I can't tell without the code, but I presume @PKCols is checking that you have a Primary key on the table, and without it for some reason the code can't run - so its raising an error.

Kristen

t1g312
Posting Yak Master

148 Posts

Posted - 2005-07-09 : 01:35:24

Hi Kristen,

Here's the code.

CREATE trigger tr_AuditPaxDetails on dbo.TableName for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKSelect varchar(1000)

select @TableName = TableName
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
-- Action
if exists (select * from inserted)
begin
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
select top 1 @UserName=UserName from masUser where spid=@@spid order by LastLoginAt desc
select @UpdateDate = convert(varchar,getdate(),120)
end
else
select @Type = 'D'
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+convert(varchar(100),coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @fieldname = ''
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
begin
if @Type = 'I'
select @fieldname = c.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS c,INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk where c.TABLE_NAME=pk.TABLE_NAME and c.COLUMN_NAME = pk.COLUMN_NAME and c.TABLE_NAME = @TableName and pk.CONSTRAINT_NAME LIKE 'pk%' and c.ORDINAL_POSITION = @field
else
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
if @fieldname <> 'LastUpdateAt' and @fieldname <> 'LastUpdateBy' and NOT(@fieldname IS NULL) and @fieldname <> ''
Begin
select @sql = 'insert AuditTrail (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
select @sql = @sql + ',' + @PKSelect
select @sql = @sql + ',''' + @fieldname + ''''
select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',''' + @UserName + ''''
select @sql = @sql + ' from #ins i full outer join #del d'
select @sql = @sql + @PKCols
select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
exec (@sql)
End
end
end

Adi

-------------------------
/me sux @sql server

Kristen
Test

22859 Posts

Posted - 2005-07-11 : 09:00:54

Seems to be as I surmised. You don't have a Primary key on that table, which is a pre-requisite for the trigger you have written.

I'm also surprised that this code, near the beginning, doesn't raise an error:

select @TableName = TableName

Kristen

t1g312
Posting Yak Master

148 Posts

Posted - 2005-07-18 : 08:36:44

Hi Kristen,

I do have a primary key on the table that I'm trying to Audit.

TableName isn't the real name of the table.

Adi

-------------------------
/me sux @sql server

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2009-11-23 : 15:41:32

Hi Adi:
I want to add two columns on this Trigger based on my table: One is CustomerID and GroupID. GroupID is because there will be lot of inserts and deletes on this table and from the application i just want to show the group ID and if want to see the details, i can click on the groupID and all the changes can be seen. How can i do this? Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources