January 19, 2017 at 3:41 pm
Hello,
I'm dealing with a database problem and I would like to know how to solve it.
The problem is that I have an ObjectDeletions table that tracks the deletions of records in other tables and it requires a username (i.e. the person who made the deletion). This username is stored in one of the columns of the ObjectDeletions table. The problem is that the user is known only to the application through which deletions are made. The database knows nothing about these users. When the user deletes a project, for example, the application runs the stored procedure DeleteProjectData. I can pass the username to this stored procedure as a parameter but then I need to store the username in some kind of global variable. The reason I need to store it in a global variable is because the actual inserts into the ObjectDeletions table happens only via a trigger on a few other tables. For example, on the Project table, I have a trigger that looks like this:
ALTER TRIGGER [dbo].[trg_Project_ObjectDeletions]
ON [dbo].[Project]
FOR DELETE
AS
BEGIN
INSERT INTO [dbo].[ObjectDeletions]
(ObjectId, ObjectType, Description, UserName, TimeStamp)
SELECT d.ProjectId, 'Project', d.Name + ' - ' + d.AcmProject, SYSTEM_USER, SYSDATETIME()
FROM deleted d
END
In other words, it's not the stored procedure which inserts records into the ObjectDeletions table, but a trigger on the Project table.
If I can stored the username in a global variable as a first step in the stored procedure, how can I retrieve it in the trigger? Or is there a better way to accomplish this?
January 20, 2017 at 7:02 am
junk.mail291276 - Thursday, January 19, 2017 3:41 PMHello,I'm dealing with a database problem and I would like to know how to solve it.
The problem is that I have an ObjectDeletions table that tracks the deletions of records in other tables and it requires a username (i.e. the person who made the deletion). This username is stored in one of the columns of the ObjectDeletions table. The problem is that the user is known only to the application through which deletions are made. The database knows nothing about these users. When the user deletes a project, for example, the application runs the stored procedure DeleteProjectData. I can pass the username to this stored procedure as a parameter but then I need to store the username in some kind of global variable. The reason I need to store it in a global variable is because the actual inserts into the ObjectDeletions table happens only via a trigger on a few other tables. For example, on the Project table, I have a trigger that looks like this:
ALTER TRIGGER [dbo].[trg_Project_ObjectDeletions]
ON [dbo].[Project]
FOR DELETE
AS
BEGIN
INSERT INTO [dbo].[ObjectDeletions]
(ObjectId, ObjectType, Description, UserName, TimeStamp)
SELECT d.ProjectId, 'Project', d.Name + ' - ' + d.AcmProject, SYSTEM_USER, SYSDATETIME()
FROM deleted d
ENDIn other words, it's not the stored procedure which inserts records into the ObjectDeletions table, but a trigger on the Project table.
If I can stored the username in a global variable as a first step in the stored procedure, how can I retrieve it in the trigger? Or is there a better way to accomplish this?
I'd love to know how that would ever be possible... To my knowledge, there just isn't any way to populate any kind of "global variable" that could be accessed by a trigger. Of greater concern, however, is the idea that you have database access taking place without actually knowing exactly who is doing what with any certainty, as you are relying on the application to guarantee a given user has access. As a "bad actor" could insert code into the application that provides a "back door", you'd have no means of detecting such a problem, and it's a security hole that I wouldn't want hanging around. Unless you start controlling your database access with actual Active Directory users with SQL logins to match, and control access with roles and permissions granted to roles, it's unlikely you're going to get very far. The only alternative that I can think of is to add a user id field of some sort to every table that you want to track, and the stored procedures that access these tables have to have a user id passed along, and ALL inserts will then have that value to work with, and thus, the trigger would have that information via the "inserted" and "deleted" tables.
EDIT: Come to think of it, a delete will need to first update the record to change the user id to the one passed in, prior to the delete taking place, as otherwise, the original insert person gets credit for the delete. It's messy, and I'm pretty sure it's a bad idea, too.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 20, 2017 at 7:18 am
I'm not sure if I'm missing one of the core requirements but if the application is already calling a SP, I'd rewrite that SP to handle the process from end to end and just do the logging. Why even rely on a trigger at all in this case.
January 20, 2017 at 7:42 am
sgmunson - Friday, January 20, 2017 7:02 AMI'd love to know how that would ever be possible... To my knowledge, there just isn't any way to populate any kind of "global variable" that could be accessed by a trigger. Of greater concern, however, is the idea that you have database access taking place without actually knowing exactly who is doing what with any certainty, as you are relying on the application to guarantee a given user has access. As a "bad actor" could insert code into the application that provides a "back door", you'd have no means of detecting such a problem, and it's a security hole that I wouldn't want hanging around. Unless you start controlling your database access with actual Active Directory users with SQL logins to match, and control access with roles and permissions granted to roles, it's unlikely you're going to get very far. The only alternative that I can think of is to add a user id field of some sort to every table that you want to track, and the stored procedures that access these tables have to have a user id passed along, and ALL inserts will then have that value to work with, and thus, the trigger would have that information via the "inserted" and "deleted" tables.EDIT: Come to think of it, a delete will need to first update the record to change the user id to the one passed in, prior to the delete taking place, as otherwise, the original insert person gets credit for the delete. It's messy, and I'm pretty sure it's a bad idea, too.
The access thing is a whole nother debate. If you give direct access to AD logins, that means users can make updates without going through the application, which might not be what you want.
My solution for this, I think, would be similar to yours - create an extra column on the Project table. The column would never be used, except to be updated with the user id every time the stored procedure runs. The user id would thus be available to the trigger. It's a kludge, but it should work.
Y.B. - Friday, January 20, 2017 7:17 AMI'm not sure if I'm missing one of the core requirements but if the application is already calling a SP, I'd rewrite that SP to handle the process from end to end and just do the logging. Why even rely on a trigger at all in this case.
You could be right there - or maybe it's not just the stored procedure that makes changes to the table.
John
January 20, 2017 at 7:56 am
if you can modify the stored procedure, you could populate the VARBINARY(128) CONTEXT_INFO property.
that value is session based, i believe, so if the application calls a proc, and passes the user, you could populate the context info with user info, and use THAT in the trigger.
since we don't have any code yet, all i can offer is some code samples on context info usage.DECLARE @var VARBINARY(128)
SET @var = CAST(N'Hello World' AS VARBINARY(128))
SET CONTEXT_INFO @var
SELECT CONVERT(NVARCHAR(64),CONTEXT_INFO())
GO
-- Select the context information
DECLARE @sess VARBINARY(128), @var NVARCHAR(64)
SET @sess = (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@spid)
SET @var = CAST(@sess AS NVARCHAR(64))
print @var
SELECT CAST(context_info AS NVARCHAR(64)) AS RESULTS FROM master.dbo.sysprocesses
WHERE spid = @@spid
DECLARE @var VARBINARY(128),
@res NVARCHAR(64)
SET @var = CAST(N'Hello World' AS VARBINARY(128))
PRINT @var
--results: 0x480065006C006C006F00200057006F0072006C006400
SET @res = CAST(@var AS NVARCHAR(64))
PRINT @res
--results: Hello World
--The same but using CONVERT:
SET @var = CONVERT(VARBINARY(128), (N'Bananas and Oranges'))
PRINT @var
--results: 0x420061006E0061006E0061007300200061006E00640020004F00720061006E00670065007300
SET @res = CONVERT(NVARCHAR(64),@var)
PRINT @res
--results: Bananas and Oranges
Lowell
January 23, 2017 at 11:15 am
junk.mail291276 - Thursday, January 19, 2017 3:41 PMHello,I'm dealing with a database problem and I would like to know how to solve it.
The problem is that I have an ObjectDeletions table that tracks the deletions of records in other tables and it requires a username (i.e. the person who made the deletion). This username is stored in one of the columns of the ObjectDeletions table. The problem is that the user is known only to the application through which deletions are made. The database knows nothing about these users. When the user deletes a project, for example, the application runs the stored procedure DeleteProjectData. I can pass the username to this stored procedure as a parameter but then I need to store the username in some kind of global variable. The reason I need to store it in a global variable is because the actual inserts into the ObjectDeletions table happens only via a trigger on a few other tables. For example, on the Project table, I have a trigger that looks like this:
ALTER TRIGGER [dbo].[trg_Project_ObjectDeletions]
ON [dbo].[Project]
FOR DELETE
AS
BEGIN
INSERT INTO [dbo].[ObjectDeletions]
(ObjectId, ObjectType, Description, UserName, TimeStamp)
SELECT d.ProjectId, 'Project', d.Name + ' - ' + d.AcmProject, SYSTEM_USER, SYSDATETIME()
FROM deleted d
ENDIn other words, it's not the stored procedure which inserts records into the ObjectDeletions table, but a trigger on the Project table.
If I can stored the username in a global variable as a first step in the stored procedure, how can I retrieve it in the trigger? Or is there a better way to accomplish this?
SUSER_NAME()
January 23, 2017 at 11:56 am
Lowell - Friday, January 20, 2017 7:56 AMif you can modify the stored procedure, you could populate the VARBINARY(128) CONTEXT_INFO property.that value is session based, i believe, so if the application calls a proc, and passes the user, you could populate the context info with user info, and use THAT in the trigger.
since we don't have any code yet, all i can offer is some code samples on context info usage.
DECLARE @var VARBINARY(128)
SET @var = CAST(N'Hello World' AS VARBINARY(128))
SET CONTEXT_INFO @varSELECT CONVERT(NVARCHAR(64),CONTEXT_INFO())
GO
-- Select the context information
DECLARE @sess VARBINARY(128), @var NVARCHAR(64)
SET @sess = (SELECT context_info FROM master.dbo.sysprocesses
WHERE spid = @@spid)
SET @var = CAST(@sess AS NVARCHAR(64))print @var
SELECT CAST(context_info AS NVARCHAR(64)) AS RESULTS FROM master.dbo.sysprocesses
WHERE spid = @@spidDECLARE @var VARBINARY(128),
@res NVARCHAR(64)
SET @var = CAST(N'Hello World' AS VARBINARY(128))
PRINT @var
--results: 0x480065006C006C006F00200057006F0072006C006400
SET @res = CAST(@var AS NVARCHAR(64))
PRINT @res
--results: Hello World
--The same but using CONVERT:SET @var = CONVERT(VARBINARY(128), (N'Bananas and Oranges'))
PRINT @var
--results: 0x420061006E0061006E0061007300200061006E00640020004F00720061006E00670065007300
SET @res = CONVERT(NVARCHAR(64),@var)
PRINT @res
--results: Bananas and Oranges
Thanks Lowell, for this script. I'll experiment with this and see if it works for my purpose.
So far I've tried this:
declare @var varbinary(128)
set @var = cast(N'hello world' as varbinary(128))
set context_info @var
select convert(nvarchar(64),context_info())
GO
This gives me a table with 'hello world' in it.
Then to test if the variable was persisted in context_info, I opened a new query window and ran this:
select convert(nvarchar(64),context_info())
It gave me a table with NULL in it. <-- It doesn't seem to have persisted.
If I store the username in context_info during the execution of the stored procedure, will it be available to the triggers?
PS - To answer other people's question about inserting into the ObjectDeletions table during the stored procedure: we could do this but the point of the table is to catch ANY deletions--whether they occur because of the execution of the stored procedure (which would be user initiated) or by some other means. We're implementing this in response to a strange occurence that's happened a couple times: projects seem to get mysteriously deleted. We're not sure why. By attaching a trigger to the Project table that inserts a row into the ObjectDeletions table, we figure this is our best way of catching these mysterious deletions.
January 23, 2017 at 12:10 pm
junk.mail291276 - Thursday, January 19, 2017 3:41 PMHello,I'm dealing with a database problem and I would like to know how to solve it.
The problem is that I have an ObjectDeletions table that tracks the deletions of records in other tables and it requires a username (i.e. the person who made the deletion). This username is stored in one of the columns of the ObjectDeletions table. The problem is that the user is known only to the application through which deletions are made. The database knows nothing about these users. When the user deletes a project, for example, the application runs the stored procedure DeleteProjectData. I can pass the username to this stored procedure as a parameter but then I need to store the username in some kind of global variable. The reason I need to store it in a global variable is because the actual inserts into the ObjectDeletions table happens only via a trigger on a few other tables. For example, on the Project table, I have a trigger that looks like this:
ALTER TRIGGER [dbo].[trg_Project_ObjectDeletions]
ON [dbo].[Project]
FOR DELETE
AS
BEGIN
INSERT INTO [dbo].[ObjectDeletions]
(ObjectId, ObjectType, Description, UserName, TimeStamp)
SELECT d.ProjectId, 'Project', d.Name + ' - ' + d.AcmProject, SYSTEM_USER, SYSDATETIME()
FROM deleted d
ENDIn other words, it's not the stored procedure which inserts records into the ObjectDeletions table, but a trigger on the Project table.
If I can stored the username in a global variable as a first step in the stored procedure, how can I retrieve it in the trigger? Or is there a better way to accomplish this?
If you're on 2016, consider adding an SUSR_NAME not null default suser_name() column to each table then convert your tables to temporal.
January 23, 2017 at 1:37 pm
you said I opened a new query window and ran this:
select convert(nvarchar(64),context_info())
the context_info is session specific, similar to a temp table.
so if i call a procedure, and it assigns conext info, then anything the proc calls, implicitly via a trigger or explicitly, would have access to the context_info you just set. a new window would have a blank context info.
i was envisioning something like this:--this code added inside the procedure body
--procedure assigns or looksup the username
DECLARE @var VARBINARY(128)
SET @var = CAST(N'Lowell' AS VARBINARY(128))
SET CONTEXT_INFO @var
--SELECT CONVERT(NVARCHAR(64),CONTEXT_INFO())
and your trigger, which is kicked off due to the procedure, right?, has been slightly modified here:ALTER TRIGGER [dbo].[trg_Project_ObjectDeletions]
ON [dbo].[Project]
FOR DELETE
AS
BEGIN
DECLARE @User NVARCHAR(64) =CONVERT(NVARCHAR(64),CONTEXT_INFO())
INSERT INTO [dbo].[ObjectDeletions]
(ObjectId, ObjectType, Description, UserName, TimeStamp)
SELECT d.ProjectId, 'Project', @User + ' - ' + d.AcmProject, SYSTEM_USER, SYSDATETIME()
FROM deleted d
END
Lowell
January 24, 2017 at 6:49 am
I'm assuming you can't use suser_name() or an equivalent because the application connects using a SQL Server login rather than windows authentication. You could prevent deletes, etc. from occurring outside the stored procedures by adding the users to the db_denydatawriter role and granting them execute on the stored procedures.
January 24, 2017 at 12:10 pm
Lowell's script worked. Since the delete triggers fire in the same session as the stored procedure execution, I was able to get the username from context_info in the trigger.
Thanks everyone for your help. I will take all other suggestions under advisement.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply