Trigger for select actions?

Discussion in 'Databases' started by candor, Dec 12, 2009.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Hi, due to a new legislation here in Italy the access to private data stored in a database must be logged and logs must be stored for at least 6 months.
    In my case I have a table called MemberInfo that contains things like addresses, phone numbers, geographic position.
    I created triggers for update and delete actions that insert new rows in a table containing who modified data, the date and modified data, but I don't know how to log the select action. I realized that there isn't a trigger for select and that's not possible to use other tools like the sqltrace and Event Notification because they need higher database privileges than those I have.
    Does anyone know how can I solve my problem? The new law should start on December, 15th!!!
    Thanks!!!
     
  2. dmitri

    dmitri DiscountASP.NET Staff

    Unfortunately, there is no SELECT trigger in SQL Server. You can create a stored procedure for the select statement and handle any type of logging inside the stored procedure. Actually, it is a best practice to write stored procedures for all DML statement (SELECT, INSERT, UPDATE, DELETE), and for the security reason, your tables that contain sensitive information should not be accesses directly by application, but by those stored procedures only.
     
  3. Thanks

    Thanks for your answer, I already did it but that doesn't solve my problem. The new Italian legislation is for logging the activity of system administrators ( db_owner and sysadmin in this case) not the end user of the data, so if my local authority ask me I have to show him a log that contains when and how I accessed the tables as an administrator and not as a user of the website.
     
  4. dmitri

    dmitri DiscountASP.NET Staff

    You cannot access database objects as an administrator because your SQL Server Login is not a member of sysadmin fixed role. Your database user that is mapped to your SQL Server Login is the member of db_owner database fixed role.
     
  5. OK, you're right. I was meaning that everyone that could open the table should be logged, that is, everyone that could use SqlManagementStudio and open the table should be logged. I know it's a stupid law and I have to log myself.....:eek:
     
  6. dmitri

    dmitri DiscountASP.NET Staff

    You as the database owner have the full control over the users who access the database and its objects, so you can go ahead and implement your select statement auditing with the stored procedure. You can also encrypt both your table and stored procedure for maxim security.
     
  7. Thank you for your advice, but what do you really mean?
    I understood I could encrypt my table, then I could create a stored procedure for selection and auditing and then I could encrypt the stored procedure. Is that right? (I'm a beginner, be patient.....:rolleyes:)
     
  8. dmitri

    dmitri DiscountASP.NET Staff

    Yes, that's right: you can encrypt the table and the stored procedure. Note, if the stored procedure is encrypted, it cannot be decrypted or viewed by anyone, including yourself and the system administrator. You will need to keep the T-SQL script that creates that stored procedure so you can recreate and edit it at a later time.
     
  9. :)Thank you again, I'll try to do that. Have a nice Christmas
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page