Security is the most important tasks for a DBA. we need control the access to sql server as well as provide security to the datal.
There are mainly 3 levels of security.
Server level – Is the topmost level in connecting to sql server.
Database level – Once connected to the server. We need to provide security to user at database level.
Object level – Once connected to database, we need to provide security on database object like tables stored procedures etc.,
For providing security we have 3 processes
Authentication, validating credentials on server authorization, permission to perform their operations.
Validating credentials (username and password) on server called authentication. For any user who are connecting to server will have login name and password.
There are mainly 3 levels of security.
Domain user, Database Administrators, other admins, developers will have windows login for connect to server. These user use same account for connecting to windows and sql server. Windows login store in windows component called Active Directory. These logins provides high security.
We need to provide security to sql server.
We have to validate both windows and sql logins. This process of checking login name and password is authentication.
There are 2 types of authentication in sql server:
1. Windows mode
It allows only window login users in this mode. It enforces only windows user to connect and access the databases. When we use highly restrict database we go with windows login. We do not provide access to external users if the data is highly restricted. This mode provides best security, it validates logins in active directory.
2. Mixed mode
This mode allows both windows and sql logins. Whenever any login tries to establish connection first it validate username and password in active directory. If login exists, establishes connection otherwise validation goes to syslogins of master database and verifies there. If logins exists it will establish a connection.
Whenever any request comes first we need to verify the login active directory. If it is not exist we need to create (or) ask active directory admins to create command for windows.
Create login [domain\login name] from windows
Authentication at database level:
Login account is a key to monitor means to connect to server. It’s a server level access.
We require another account called user account to access the databases inside the server. This is a database level access. We need to grant access for these user account on each database separately.
Every login account will be mapped with the user account. Wevery login will have a security is (SID). It’s a unique security number inside sql server to avoid duplication of logins using the SID of login account we’ll map to SID of user account. Now it establishes relationship between login account and user account. This is user mapping, one login account will mapped to one user account only we call this as one to one mapping.
Once the user mapping is created between login and user account, sql server allows to connect to server and then to access the databases inside the sql server.
Creating windows login:
Creating login loginname from windows
Creating sql server login:
For creating any login, it requires 4 properties
sp-add login ‘login name’ ‘password’ ‘default database’
Example: sp-add login ‘sql 2012 admin’ ‘sql@12345’ ‘master’
Go to security folder under management studio → Go to login → Right click on login → Select new login
Domain users will set these password policies. These policies will be enabled at window server level. We are using same policies for sql login also. Once we provide require properties to login we can enable password policy. What is minimum password length and complexity.
We are using password policies which created at domain level
When this login is to be expire. It expires after specified days
Domain users create login and provides password we need to change the password in logging first time.
When user login next time sql server prompt a message to change the password.
Once all these details provided login will be created.
Once user connected to server, they should have permissions to perform / operating certain tasks. As a DBA we need to restrict the permissions by granting the roles, roles are set of privileges for connection server and to access the database.
We have 2 types of roles
DB – owner: DB owner is capable of doing anything on particular database. Highest privilege at database level. DB – security admin – He is responsible for creating/deleting/modifying roles to users and manages permissions.
DB – data reader – He can run select queries on all tables, only read activities.
DB – data writer – He can perform insert/update/delete data in the database.
DB – deny datareader – To restrict the read access on particular confidential data even though user has server level (or) high privileges at database level we can prevent that user in accessing particular database using deny role.
DB – deny data writer: He cannot able to write data on restricted databases.
DB – DDL admin: He can able to create/after/drop the tables and other objects.
DB – access admin: He cannot able to create (or) modify any user role. He can grant only roles and remove access to other users.
Backup operator: He can able to take backup of databases
Public: This is default role on database level.
Using T-sql command – sp-add role member ‘username’ ‘rolename’
Example: sp-add role member ‘Testadmin’ ‘DB-owner’
Flexible roles are the roles to create group of users in terms of functionality.
Sql server 2012 introduces contained databases,
There are 2 types of Authentication in this,
We need to identify 3 things in providing object level access.
Grant/revoke permissions on securable to principals.
Example: Grant insert on dbo.sqltest to empadmin
Go to users under database → go to properties → go to securables → select objects from search → object types provides list of objects tables, views etc.
Select them → provide permissions and click OK.
Using encryption we can protect the confidential data while transferring data through networks. It restricts hackers to access data.
Certificate encryption is used to identify users, devices and organization certification authorities generate these certificates to provide the securing sql service will use these certificates and encrypt data.
Asymmetric keys uses a public key that encrypt data before sending data private decrypts that message after receiving. Here we have 2 keys in encryption.
Symmetric key uses same key to encrypt and decrypt the data.
TDE is a sql server 2012 enhanced feature that allows encryption of data in better way.