May 1, 2003 at 10:45 am
Greets, I have a quick question. Is it possible to have a column that contains the date the row was created and make that column hidden or uneditable by anyone?
Or is there an even better way to determine when the row was inserted?
May 1, 2003 at 12:11 pm
Create a view on the table to exclude the date column and let everyone accesses the view.
You could add additional BINARY_CHECKSUM column that returns the checksum value computed over a row of a table, or over a list of expressions to detect changes in the rows of a table.
Edited by - Allen_Cui on 05/01/2003 12:11:55 PM
May 1, 2003 at 4:43 pm
I also use UPDATE triggers to prevent this for safety, you can determine which coolumn is being updated and if the one in question force a rollback and the data won't change. Just make sure you throw yourself a message for apps or connections to see.
May 2, 2003 at 6:45 am
Another option is to apply column-level security preventing updates... for instance:
DENY UPDATE ON dbo.Orders (OrderID) TO Public
Of course, this won't stop anyone who is mapped as dbo (the database owner and anyone holding sysadmin privileges), but it's less overhead than a trigger.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 2, 2003 at 6:49 am
Thanks Brain, had not seen that before.
May 2, 2003 at 7:24 am
All great suggestions but I take it from these that there is no sure way to prevent someone with sysadmin privileges to change or even insert their own values in this column.
I was under the impression that there was a way to determine when a row was created other than having to create your own tracking system.
ahh, dreams of a perfect world.
May 2, 2003 at 7:36 am
No, because even with the trigger option, a sysadmin level account could simply disable the trigger. Anyone bearing that level of privilege should be highly trusted, just as a person with domain admin privs for the infrastructure should be.
You always have the option of running traces to audit or using a tool like LogExplorer. The row will be changed, but you'll have a record after the fact.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 6, 2003 at 6:59 am
Couple of comments with the assumption that you aren't worried by users with SysAdmin. If there is someone you can't trust with that level of access then your problems are outside of this topic.
First, if you want to know when a column was created I would add columns like this:
create_dt with default getdate()
create_user_nm with default suser_name()
For auditing I do two things:
1) Add columns last_chg_dt and last_chg_user_nm and populate them with a default for inserts and a trigger for updates.
2) Create shadow tables that are copies of your main table, with no RI and no indexes (unless you will access it a lot). On this table also add a PK (e.g., shdw_id and obviously this one will have an index), shdw_tms, shdw_user, shdw_cd (this one will contain U (pdate) or D (elete). Then use triggers to populate these.
What we do with the shadow tables is only insert on Updates and Deletes. Also, we write the values of the columns prior to the updates as the current values are accessible from the current table.
Hope this is of some help.
PS - Brian's approach is the one I have used to remove column level access and is, I'm certain, more efficient and trustworthy than triggers.
May 6, 2003 at 8:59 am
It should also be noted that while you may issue a GRANT against a whole table or view, internally SQL Server does the permissions check at the column level anyway, meaning you aren't losing any efficiency by doing it this way.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
May 6, 2003 at 9:03 am
You guys are awesome. Great solutions given here and information I wil certainly put to use in the future.
In the meantime, I've decided to "trust" a little more and wait it out. Something to do with Karma, you know?
Thanks again
May 12, 2003 at 2:28 pm
you can aslo use HOST_NAME() function as default value to return the workstation name
msh
msh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply