May 11, 2008 at 10:46 pm
Hi All,
I have a database in sql server 2005. I want to restrict adding/updating data into a particular table for 'sa' login itself. Can i protect my table by asking password or some other mechanism since sa user will have all the rights on the database.
when by application is given to client, i might sell him only a particular module(for ex . Screening module). But that module requires main admin table to be there. I dont want him to alter the admin module data.He will be knowing the sa password. but i want to restrict him from making any changes for admin table. Please HelP!!
Regards,
Amit kulkarni
May 12, 2008 at 12:50 am
Don't ever let an application use sa to connect. There's no way to prevent someone who knows the sa password from doing anything to the server.
Set up an user name specifically for the application and give it the minimum permissions necessary to run. Nothing more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2008 at 11:31 am
You could encrypt the data that way they could not add to it or change it. This would not stop them from deleting it though. There is no way to stop an SA from deleting any thing.
May 15, 2008 at 2:31 pm
can u consider using stored procedure to be used in the appliction, by this u can do every thing u can and if u used with encryption option he cant alter ur procedures..even if he was sa.
..>>..
MobashA
May 16, 2008 at 12:05 am
Encrypted stored procs aren't hard to decrypt. Besides, if someone has the sa password, what stops them from conecting directly to the server and manipulating data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2008 at 9:32 am
Use of sa is strongly cautioned against because it doesn't undergo any security checks. So if you have someone connecting as sa, there's not anything you can do to stop them. If the table has encrypted columns, you'd have to ensure the application passed in the password to decrypt or the like because as sa they have access to the database master key, meaning you can't stop them from decrypting (and inserting their own, encrypted, values).
Therefore, you need to look to a solution where a lot less rights are granted. There's a principle in security we call the Principle of Least Privilege. It's really easy to understand. You give only the rights necessary to do the job and no more.
K. Brian Kelley
@kbriankelley
May 17, 2008 at 2:39 pm
u can try every thing from encryption, procedures to alias objects, but when using sa there is not much u can do.
..>>..
MobashA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply