Denali – Day 21: Security

Security wise Microsoft sql server is going great and on compare with other RDBMS in the market it has very little flows with regards to Security. It’s a one of the great achievement by sql server.

Contained databases-

As blogged about contained database, now user login information is stored in user database directly not in master database… id/password. Which isolate database from server, and user associated with that database can be easily access that database.

Default schema for Windows group:

Earlier it was dbo as default schema, so when you logon as user and create an object without specifying any schema and if other user try to access without specifing schema to that object he will unable to get access. This has been fixed and now a group of user can be having default same schema and they are shared access to their objects to avoid the complexity of the objects if we have several objects and users.

User-defined server role:

Another security issue was “Custom server role” which has introduced in Denali. –flexible server role(user defined)

Depending upon the requirement, we can create our custom server wide roles. Instead of allowing sysadmin role we can customize the user to a specific role and Deny some un-necessary access to them.

Auditing:

1. Audit Resilience:
   
2. Basic Audit sues – SQL Audit:
   
3. User-Defined Audit:
   
4. Audit Record Filtering:
   

1. Audit Resilience:
   

   Due to server error customer has to miss the audit, now we could recover the old audit information, recovery temporary files. without restart the complete instance, we could check audit issue.
   

    

2. Sql audit as well sql trace.
   

   Previously it was limited to Enterprise editions and above, now it is available for all editions of sql servers.
   

    

3. User defined audit:
   

Go to database ->Audit

Create a new Audit and Enable it

With the help of procedures we can create a audit and raised a custom check and output on an audit.

Create a new server Audit specification and specify the audit action type and enable it.

1. Audit Filtering:
   

We can conditionally filter the audit log file viewer – audit record so that to get the filtered audit information with audit specification where clause

Ref:

http://www.microsoft.com/sqlserver/en/us/solutions-technologies/mission-critical-operations/SQL-Server-2012-security-and-compliance.aspx

http://www.mssqltips.com/tipprint.asp?tip=2424

http://www.sqlmag.com/content1/print/topic/sql-server-2012-data-security-142461/catpath/sqlserverdenali/print/1