With digitalization all over the world, it is very important that the data saved in database is secure. Irrespective of the size, it is a fact that every database has some sensitive data and that has to be protected. This article highlights all options to secure your SQL Server data
1. Start with proper access and review it
The first important step is to ensure that you are providing only necessary access to users. If a user wants to read some data from database, provide them with Read access. Never grant full access to all users. It is important to understand the magnitude of this step. Consider an unhappy employee with full access to the production servers; he might run a delete statement and corrupt the SQL Server database.
2. Full access to third party users:
It is very common that full access is granted to third party users for application deployment. In that case, keep track of all provided access and after the deadline revoke the access or delete the user. It is a mandatory process to conduct user audit on all servers. Review the report and revoke unwanted access or delete unwanted users. Most companies disable or remove Windows account of their ex-employees. This will ensure that Windows account on SQL database too won’t work too. However SQL authentication accounts created for ex-employees will still work and it’s a security threat until it is removed.
3. Automate disable and drop
CREATE TABLE usertable ( servername VARCHAR(200) ,loginname VARCHAR(200) ,expirydate DATETIME ,solution VARCHAR(100) ,remarks VARCHAR(100) ,lastupdate DATETIME ) INSERT INTO usertable ( servername ,loginname ,expirydate ,solution ) VALUES ( 'Server1' ,'asdf' ,'2016-01-19 23:04:27.440' ,'DROP' ) SELECT * FROM usertable DECLARE c1 CURSOR FOR SELECT servername ,loginname ,expirydate ,solution FROM usertable OPEN c1 DECLARE @srvr VARCHAR(200) DECLARE @login VARCHAR(200) DECLARE @expiry DATETIME DECLARE @sol VARCHAR(200) DECLARE @cmd VARCHAR(400) FETCH NEXT FROM c1 INTO @srvr ,@login ,@expiry ,@sol WHILE @@FETCH_STATUS = 0 BEGIN IF @expiry < getdate() BEGIN IF @sol = 'DROP' BEGIN SET @cmd = 'sqlcmd -S ' + @srvr + ' -d MASTER -Q "DROP LOGIN ' + @login + '"' PRINT @cmd EXEC xp_cmdshell @cmd UPDATE usertable SET remarks = 'COMPLETED' ,lastupdate = getdate() WHERE servername = @srvr AND loginname = @login AND expirydate = @expiry AND solution = @sol END END FETCH NEXT FROM c1 INTO @srvr ,@login ,@expiry ,@sol END CLOSE c1 DEALLOCATE c1
4. Mask the data
If you have started to use SQL 2016, protecting your sensitive and confidential data is very easy with dynamic data masking. This feature helps to control the amount of data that has to be masked before being displayed on the screen. The best part is, all this protection occurs with minimal impact on the application layer. You can mask the complete data or just a part of it. All you have to do is to identify the sensitive fields in a database and create a central masking policy for those fields. Then create privileges so that users without those privileges will see the masked data whereas users with privileges will see the original data i.e., unmasked data. Please do note that the masking cannot be applied on a computed column. However if the source column of a computed column is masked, data from computed column would also be masked.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst data damage and excel recovery software products. For more information visit www.datanumen.com