Today all organizations have important data that is confidential and mission-critical, and should not be disclosed to unauthorized persons. Hence the security of data is one of the prime aspects of a DBA’s job.
We will discuss about the SQL server security model and different features that SQL provides for data security.
Security means that only relevant data should be available to authenticated persons. It additionally includes appropriate restrictions on the use of data, as well as controls to prohibit data theft. In case data is stolen by unauthorized users, security systems do not allow them to make use of it.
However, it is not sufficient to just secure the SQL server. The operating system’s network security is also important. In addition, physical access to the server must be restricted and the application which accesses SQL server must be authenticated.
The security model has five layers, which are Physical security, OS and Network security, Database security, Data security and Application Security.
There are some important terms in SQL server security, which include:
Principals are that require access to SQL server or database, to which we grant permissions, for example: logins, roles etc.
Securables are items we secure or ones on which we grant permissions to principals. For example, database, schemas, table etc. The below figure gives you details about the principals and securables.
Login: At server level, we have logins, which give access to a user to SQL Server. Server level permissions are assigned to logins. Login can be windows login or SQL server login, depending upon the authentication mechanism you are using.
Example: Creating a login
To create an SQL login named SqlLogin with password ‘Password@123’ by T-SQL, the command is:
To create a windows authenticated login, the T-SQL command is:
To create a login from Management studio
Open object explorer and then go to secure folder at the instance level and then right click login to create a new login
Fill the form to create a new login
Users: The logins are mapped with users in the database and the database level permissions are assigned to users. There are some predefined user present in every database which are dbo, guest. ‘dbo’ is the default user for sysadmin login.
Example: Creating a user
Create a user by T-SQL command:
Create a user by Management studio
In object explorer, go to the security folder inside the database folder and right click users for new users
Fill the form
Roles: There are some built-in roles present at server and database level. These roles contain predefined set of permissions so if you want to assign specific set of permissions to a login or user, make them the member of a role. There are two types of roles:
Fixed server roles: These server roles are predefined and you cannot create a new one.
To make a user member of Server Role, T-SQL command is:
Database Level Roles: At the database level, we have some predefined database level roles. However, users can create their own database roles.
To make a user member of Database Role, T-SQL command is
By these means we can restrict access to our database, however placing restrictions is not enough because data can also be stolen. Critical data must be encrypted for security so that even if it is stolen, it cannot be used. A SQL server encryption mechanism to encrypt the data by using certificates, keys.
Encryption:
Data can be encrypted, and the keys and certificates are in the database. But what if the backup file or mdf file gets stolen?
For that we have Transparent Database Encryption (TDE), which is a real-time IO encryption that uses certificates and Database encryption key. This is done for encrypting the database, so in case the backup or mdf file is stolen, it cannot be used fully without the certificate.
There are other important considerations too. For example, just applying the security is not enough. Auditing also holds huge significance. SQL server provides SQL Server Audit for auditing purpose. SQL Server Audit can track and log events that occur at the server level or the database level automatically. With SQL server audits, several server and database level actions can be tracked. These include failed login attempts, modification in structure of database or table etc. These can then be logged in Windows event log or a file system file.
Reference: Pinal Dave (https://blog.sqlauthority.com)
