SQL Audit trail
-
I have a stored procedure that takes two parameters (1) insert statement (2) user name It just executes the insert statement. There is only one database user in the database. My requirement is (1) I need a way for the insert trigger to access the user name for inserting data into audit trail tables. (2) In case when many users are logged in at the same time, how do I know which user name to use. In our firm we have the constraint of one database user per database. If there is any other solution please advise. Live Life King Size Alomgir Miah
-
I have a stored procedure that takes two parameters (1) insert statement (2) user name It just executes the insert statement. There is only one database user in the database. My requirement is (1) I need a way for the insert trigger to access the user name for inserting data into audit trail tables. (2) In case when many users are logged in at the same time, how do I know which user name to use. In our firm we have the constraint of one database user per database. If there is any other solution please advise. Live Life King Size Alomgir Miah
Alomgir Miah wrote: I need a way for the insert trigger to access the user name for inserting data into audit trail tables. Why? you are inside a stored procedure, let it do the work. Alomgir Miah wrote: In our firm we have the constraint of one database user per database. Unless you have a very good reason for this then that is a crazy constraint. How do you share information? (Which is one of the major advantages of having a database in the first place) Also, if you only have one user per database then what is the problem? Surely you know who's using what database.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Alomgir Miah wrote: I need a way for the insert trigger to access the user name for inserting data into audit trail tables. Why? you are inside a stored procedure, let it do the work. Alomgir Miah wrote: In our firm we have the constraint of one database user per database. Unless you have a very good reason for this then that is a crazy constraint. How do you share information? (Which is one of the major advantages of having a database in the first place) Also, if you only have one user per database then what is the problem? Surely you know who's using what database.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Thanks a lot for your reply. I think I was not very clear in my questions. I am passing (1) An insert query string (2) UserName to the stored procedure. As soon as the insert happens, the insert trigger is fired. In the insert trigger I am auditind the data by storing the changes(insert in this case) into an audit trail table. The answers to your questions are (1) The trigger can access the database user only through user_name INSERT INTO AuditTrail SELECT user_name(),getdate(),'Fund','FamilyID',@rowid,'Insert',null,convert(varchar,FamilyID) FROM inserted But I need a way to access the parameter passed into SP. (2) I know the constraint sucks. This is enforced for security reasons. More users means more activity tracking for all users. This means more work for the support groups and security holes in the application. ( Frankly, I dont agree to this at all like you said. ). But it does not means one user. It means many kerberos user and only one database user. So the only solution I can think of is having a LastUpdatedBy column in the tables we are auditing. So the insert statement in the Trigger changes to INSERT INTO AuditTrail SELECT LastUpdatedBy,getdate(),'Fund','FamilyID',@rowid,'Insert',null,convert(varchar,FamilyID) FROM inserted and I wont need the stored procedure in discussion at all. Is it the best way to go keeping in mind the constraints we have. Live Life King Size Alomgir Miah
-
Thanks a lot for your reply. I think I was not very clear in my questions. I am passing (1) An insert query string (2) UserName to the stored procedure. As soon as the insert happens, the insert trigger is fired. In the insert trigger I am auditind the data by storing the changes(insert in this case) into an audit trail table. The answers to your questions are (1) The trigger can access the database user only through user_name INSERT INTO AuditTrail SELECT user_name(),getdate(),'Fund','FamilyID',@rowid,'Insert',null,convert(varchar,FamilyID) FROM inserted But I need a way to access the parameter passed into SP. (2) I know the constraint sucks. This is enforced for security reasons. More users means more activity tracking for all users. This means more work for the support groups and security holes in the application. ( Frankly, I dont agree to this at all like you said. ). But it does not means one user. It means many kerberos user and only one database user. So the only solution I can think of is having a LastUpdatedBy column in the tables we are auditing. So the insert statement in the Trigger changes to INSERT INTO AuditTrail SELECT LastUpdatedBy,getdate(),'Fund','FamilyID',@rowid,'Insert',null,convert(varchar,FamilyID) FROM inserted and I wont need the stored procedure in discussion at all. Is it the best way to go keeping in mind the constraints we have. Live Life King Size Alomgir Miah
Having a LastUpdatedBy column in the table(s) would be a possible solution as the trigger can get that information. But, why are you passing an insert statement into the stored procedure. You'd need to do something like EXEC(@insertStatement) which kind of blows away the purpose of a stored procedure if that is all you are doing. It is much better, and more secure, if you create a stored procedures (plural) for your data access and modification needs so that the each stored procedure can do sanity checking on the data and also it means that only the actions that the stored procedures can perform are available to outside applications (assuming you revoke access to the tables and views directly for the database user that is being used by the application). The stored procedures can also update the relevant audit tables for you. That would mean that you no longer need to have a LastUpdatedBy column in your tables. Assuming those above you are serious about security - but their one user for the whole database seems inherently insecure, they should have set up roles so that each person can only access the data they need. I know the application is probably stopping that kind of access, but there could be a weakness in the application and it is better to have multiple levels of security. That is why if you look at ancient castles that still exist today then you'll see they have many layers of protection. If an invader breaks down one wall or gate then the occupants can retreat and fight from the next gate.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
Having a LastUpdatedBy column in the table(s) would be a possible solution as the trigger can get that information. But, why are you passing an insert statement into the stored procedure. You'd need to do something like EXEC(@insertStatement) which kind of blows away the purpose of a stored procedure if that is all you are doing. It is much better, and more secure, if you create a stored procedures (plural) for your data access and modification needs so that the each stored procedure can do sanity checking on the data and also it means that only the actions that the stored procedures can perform are available to outside applications (assuming you revoke access to the tables and views directly for the database user that is being used by the application). The stored procedures can also update the relevant audit tables for you. That would mean that you no longer need to have a LastUpdatedBy column in your tables. Assuming those above you are serious about security - but their one user for the whole database seems inherently insecure, they should have set up roles so that each person can only access the data they need. I know the application is probably stopping that kind of access, but there could be a weakness in the application and it is better to have multiple levels of security. That is why if you look at ancient castles that still exist today then you'll see they have many layers of protection. If an invader breaks down one wall or gate then the occupants can retreat and fight from the next gate.
My: Blog | Photos WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
This really helped. I will be exploring the possibilities based on your inputs. I got rid of the SP. Its purpose was just to accept the username. I was just trying to use it. Well I am using Object Oriented Database with a Class per table. I have a Query Generator too which generates dynamic SQL queries on the fly. So I dont use SP's to do Select's , Insert's, Delete's and Update's. The business rules are implemented in the Data Access Layer. Live Life King Size Alomgir Miah