| Author |
Topic |
|
WindChaser
Posting Yak Master
225 Posts |
 Posted - 2005-04-08 : 13:57:16
|
| I have a table which contains sensitive data such as licensing information. The SA password is not known by the users. I would like to make sure that a user which gains access to the database using any query tool using Windows NT authentication cannot write to the table. From EM, I have tried to limit the permissions for the Public user to reading only, but it doesn't work. What do I need to do to alter the table, using T-SQL, to get some writing security in there? Thanks folks! |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-04-08 : 14:07:25
|
| I think anyone atleast need permission to write or update to the table (or atleast part of db_datawriter database role) or UPDATE permission on that particular table? |
 |
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2005-04-08 : 14:16:08
|
| The table is usually only accessed by an application which uses a user with a SQL Server authentication. Only the application should be able to write to the table. |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2005-04-08 : 22:40:51
|
| The table is usually only accessed by an application which uses a user with a SQL Server authentication. Only the application should be able to write to the table. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-04-09 : 03:48:13
|
| You could look into using an "application role" to access the database from your application (See BOL for details) and then make sure that write access is only granted to that role.If instead you grant rights to a user or a group so that they use the application, this won't prevent them from accessing the data outside the application.HTH=================================================================In order to improve the mind, we ought less to learn than to contemplate.-Rene Descartes, philosopher and mathematician (1596-1650) |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2005-04-09 : 16:11:01
|
| 1. But with an application role, only the application can access, right? I need to be able to access for maintenance purposes as well. Also, I'm obviously not doing it correctly because, following your suggestion of reading BOL, I put an application role in there and I can still access the data from an external tool.2. But isn't the problem really that the MSDE installer sets a Login that has NT authentication by default (BUILTIN\Administrators). I figure that if I remove it, I'm removing the problem. Is there a straightforward method of removing this login? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-04-10 : 13:35:21
|
| 1) Yes, but from your problem statement, I thought that that is exactly what you wanted ("I would like to make sure that a user which gains access to the database using any query tool using Windows NT authentication cannot write to the table.")If people are able to access this table it's only because somewhere in their permissions list, they have permission. (So much for my glaring grasp of the obvious ;-)Remember that permissions are cumulative. The permissions granted to the user reflect permissions granted to the user themself and all rights granted to every group, server role and database role they are members of.The System Administrator, clearly, has the ultimate access rights. The BUILTIN\Administrators should be a memeber of the System Administrator account and thus would have the permissions.2) If the system is not allowing you to delete this login it could be because it is the only System Administrator account. Deleting this would be bad....very, very bad.If you don't understand what I'm saying, step away from the computer and go hire a DBA.HTH=================================================================In order to improve the mind, we ought less to learn than to contemplate.-Rene Descartes, philosopher and mathematician (1596-1650) |
|
|
|
WindChaser
Posting Yak Master
225 Posts |
Posted - 2005-04-10 : 14:15:25
|
| Thanks for the chuckle, Bustaz Kool, 'cause the fact of the matter is that in the security aspects of SQL Server, I'm obviously flying by the seat of my pants. However, I think that I might have found a remedy to my situation. I've done a sp_denylogin 'BUILTIN\Administrators' and nothing but my two SQL Authenticated Logins of my application and mine (for maintenance) now have access to anything. Radical? Possibly but, hey, it seems to meet the objective. If I try to access with Windows NT Authentication from Enterprise Manager or other tools, I'm locked out solid. Of course, I recognized that there may be some downsides that I'm not aware of in doing this. Any critiques would be welcomed.Thanks folks! |
|
|
|
|
Write-securing a table