Read only table protection

I have an audit record table to log each action to modify system data. I need the audit table read only to provent any modification. What is the common way to do it? I'm using SQL Server 2005.

Thanks.
LVL 1
minglelinchAsked:
Who is Participating?
 
jogosCommented:
You always have to mix grant and deny-functions. Point I wanted to make with the different schema is that  it's always easier to group things.
To easy giving permission permissions are grouped in roles (sysadmin, datareader, ...) and you can make a role for your database when you can identify different sets of permisions.
If at design time you have know a set of tables whit their  views, funtions or procedures will have a different permission-approach you can bring those objects together in a schema so that with less grant/deny-statements you have same result, even better while if you make a similar object in that schema it automaticly is included in the permissions of that schema.

If you want to chech what the combination of permissions is for a specific object and user there is the function HAS_PERMS_BY_NAME() and in combination with 'execute as user' you can test it for a specific person (if you have enough permission to do so :) )
http://msdn.microsoft.com/en-us/library/ms189802.aspx
0
 
deisrobinsonCommented:
Something like:
deny insert,update,alter,TAKE OWNERSHIP on <<Table_name>> to <<UserName>

0
 
TempDBACommented:
yup. You can't make it completely read only else even your auditing won't happen. So, you can just allow the a/c which is inserting in the table to allow and make deny insert, update, delete as Master deisrobinson said.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jogosCommented:
Auiting table that is normaly only inserting, so update and delete can be denied for most users/user groups, except for adminstrators that is.
Even the insert can be denied if it's done with a protected procedure on wich all users only have execution permission.

The easiest way is offcourse that the auditing table and its procedure are in another schema so that the protection can be done for the whole schema if other auditing tables or procedures come in the future.
0
 
minglelinchAuthor Commented:
Yes, that's true. Have it a different schema would be an easy way.

I have an impression that SQL Server 2005 has a feature to do this. I cannot remember exactly what it ia called. It's something like table level audit trigger which can protect a table by denying an operation like UPDATE or DELETE on the table.
0
 
jogosCommented:
From 2008 sql server audit http://msdn.microsoft.com/en-us/library/cc280386(v=SQL.100).aspx

But writing your own trigger for insert/update/delete auditing http://www.codeproject.com/KB/database/AuditTrailGenerator.aspx
0
 
minglelinchAuthor Commented:
How about DDL trigger? I have impression I had ever used a DDL trigger to provent records deletion/modification, but forgot detail about it. May I use DDL to provent any delete and update operation on a table? Thanks.
0
 
minglelinchAuthor Commented:
I'm using SQL server 2005. The above joqos's link is a nice way to audit table modification accessing. I need to prevent any updates or deleting happening. I already have audit information in code.

I need a little more help or explanation on this:
"The easiest way is offcourse that the auditing table and its procedure are in another schema so that the protection can be done for the whole schema if other auditing tables or procedures come in the future. "

So using this way, I should use a different schema for the audit table. Only users with the same schema can modify the audit table which make it harder for audit table being changed, is it correct?
I insert audit data in code, and I only have on audit table all the time.

Thanks.
0
 
jogosCommented:
That's the normal grant or deny permision

In same schema  you can grant anybody acces to the whole schema, but explicitly deny access to anybody except for sysadmin an dbowner.
 
DENY DELETE ON yourtable to rolX, userY

http://www.mssqltips.com/sqlservertip/1138/giving-and-removing-permissions-in-sql-server/
http://msdn.microsoft.com/en-us/library/ms189121(v=sql.90).aspx

But as I said, when you know that something is protected for allmost anybody, its more easy to isolat it in its own schema and only grant the permissions. With mixiing grant and deny if you don't have a good view on it it could get messy.
0
 
minglelinchAuthor Commented:
Thanks. I think that's what I need. will do.
0
 
minglelinchAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.