November 21, 2012 at 12:47 am
Hi All,
we have SQL Server that has several databases and users can access the databases, the problem is that we want to add a new database called VIP that contains sesensitiveata. We wanna create security such that this database can only be accessed be people who are added under users not everyone who is added in the sql server.it must restrict even administrators and guests from viewing the data as this database contain lots of sesensitivenfomaition.
Trhanks in advance
Kind regards
The Bird
November 21, 2012 at 1:55 am
You will not be able to stop people who are sysadmins from looking at the database, people who are sysadmins have full control to do what they want when they want and you cannot setup explicit denies as it just ignores them. So your into the who watches the watchmen situation where you will need to atleast give your administrators some trust that they wont distribute the information. If you really want to stop them from sharing the information you will need to enforce a NDA which is legally binding which says if you tell anyone about the information in this database then we will take legal action against you.
Now that being said, create your database, create a role, assign the right permissions to the role, assign the people who need to look at the data access in the role.
Now if all of your accounts have sysadmin rights or elevated permissions, you need to go back to the drawing board on how you manage your SQL security.
November 21, 2012 at 2:11 am
As Anthony said you will not be able to prevent sysadmins from viewing the data, but you can set up auditing on the accounts to log when they have read\modified any data.
November 21, 2012 at 2:15 am
dnonyane (11/21/2012)
We wanna create security such that this database can only be accessed be people who are added under users not everyone who is added in the sql server.it must restrict even administrators and guests from viewing the data as this database contain lots of sesensitivenfomaition.
Put the DB on a different instance, perferably on a different VM and grant just the trusted people login rights. You cannot deny anything to a sysadmin and you cannot prevent a windows admin from getting access to SQL if they want
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
November 21, 2012 at 3:07 am
Hi Guys thanks for the advice,i think i will have to hide the data in a temp db as people never open that database and work with it from there then after delete the entire tables are sensertive
November 21, 2012 at 5:02 am
Have you thought about encrypting the sensitive Data itself as an added layer of security this coupled with the above suggestions should make it pretty secure.
In the end theres no such thing as 100% security, all you can do is best endevours to protect the data and limit access as much as possible.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 21, 2012 at 6:21 am
dnonyane (11/21/2012)
Hi Guys thanks for the advice,i think i will have to hide the data in a temp db as people never open that database and work with it from there then after delete the entire tables are sensertive
Security by obscurity does not work. If the data needs to be secure, then secure it. Hiding it won't stop someone who's looking.
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
November 28, 2014 at 1:37 am
Finally i managed to Secure the data by Implementing Encryption to the tables. i.e. Adding Encryption keys and certificates to the Columns. now as long as the users don't know these stuff they won't be able to interpret it.
thanks to all who contributed 🙂
November 28, 2014 at 1:47 am
dnonyane (11/28/2014)
now as long as the users don't know these stuff they won't be able to interpret it.
Security by obscurity does not work.
What exactly do you mean by 'adding encryption keys to the column'? Because the way SQL encryption works is that you open the key, use the encryption function then insert/update the encrypted value. Anyone with rights on the key/certificate can open it and decrypt the data, that includes all sysadmins and db_owners. Encryption is not a property of the table or column
You shouldn't be relying on 'the user doesn't know this stuff'. Encryption should protect data even from someone who knows exactly how the encryption hierarchy in SQL works, knows all the functions and is familiar with the database schema,
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply