SQL SERVER
SECURITY
Dr. SALAH SALEM BINDAHMAN
Introduction
Security is often one of the most
challenging aspects of designing and
managing database systems.
It is not about guaranteeing a completely
attack-proof system.
It is about mitigating and responding to
risk.
It is about ensuring that you take
necessary steps to minimize the scope of
the attack.
SQL Server Authentication
Modes
SQL server uses two different authentication
modes: windows and mixed modes.
In windows mode, SQL server does not need
to store any information about the
passwords to manage the access for
authentication.
In the mixed mode, SQL server manages all
the authentications and save the
usernames and the passwords in the master
database.
SQL Server Authentication
Modes
Principals
The term principal refers to describe
individuals, groups, and processes that
will interact with the SQL server.
Principals have three different levels
Windows level.
SQL server level.
Database level.
Principals covers login, credential, server
roles, users
Login
SQL server offers two kinds of logins: windows
and SQL logins.
To create login we use the following command
CREATE LOGIN [name] {WITH <options> | FROM <source>}
Options are: password, check_policy,
must_change, default_database, etc.
Sources are: windows, certificate,
symmetric_key.
Login
Login
To create new SQL login for Carol
CREATE LOGIN Carol WITH password = ‘password’
GO
To change the password
ALTER LOGIN Carol WITH password = ‘newpassword’
GO
To remove the login
DROP LOGIN Carol
GO
Credentials
SQL server has a feature of mapping a
login to external windows accounts.
It can be one-to-one mapping or many-to-
one mapping allowing multiple logins to
use one shared account.
It is useful if you need the login to
interact with resources outside the scope
of the SQL server.
Credentials
To create new credential
CREATE credential StreetCred
WITH IDENTITY = ‘AughtEight\CarolStreet’
SECRET = ‘password’
GO
To associate Carol login the this
credential
ALTER LOGIN Carol WITH CREDENTIAL = StreetCred
GO
Credentials
Server Roles
SQL server defines eight server roles to
simplify management of SQL logins.
It is also called fixed server roles.
It allows you to automatically assign a
common set of permissions to a login.
Server Roles
Server Roles
To add a login to a fixed server role, we
use the stored procedure sp_addsrvrolemember.
and to remove it we use sp_dropsrvrolemember.
To add a server role to Carol login
EXEC sp_addsrvrolemember ‘Carol’ , ‘securityadmin’
GO
To remove it
EXEC sp_dropsrvrolemember ‘Carol’ , ‘securityadmin’
GO
Database Users
Users are granted access to securable
database objects through a membership
of database roles, or associate it with
any object.
When login is created, it has no explicit
permission to any database in the
server.
The user dbo (database owner) is
created for each database by default.
Members of sysadmin server role and sa
account are mapped to dbo.
Database Users
To create a database user named Bob
that is mapped to Carol login
CREATE USER Bob FOR LOGIN Carol
WITH DEFAUL_SCHEMA = Sales
GO
To make change to a user
ALTER USER Bob WITH DEFAUL_SCHEMA = Production
GO
Database Users
Fixed Database Roles
Every database has a fixed roles that
allow you to give permissions to users.
There are ten defined database roles in
SQL server
We use the procedure sp_addrolemember and
sp_droprolemember to add and drop roles to
user.
EXEC sp_addrolemember ‘datareader’ ,‘Carol’
GO
EXEC sp_droprolemember ‘datareader’ ,‘Carol’
GO
Fixed Database Roles
Application Roles
Application roles usually used when
database access must be the same for
all users who run particular application.
CREATE APPLICATION ROLE SalesApp
WITH PASSWORD = ‘P@ssw0rd’,
DEFAULT_SCHEMA = Sales;
GO
Permissions
A well-implemented security solution can
answer the following questions:
Who are you? (authentication)
What can you do? (authorization)
What have you done? (auditing)
To define a permission we need to
specify a permission state, an action, the
object, and the principal.
PermissionState Action ON Object To Principal
GRANT SELECT ON [Link] TO Carol
Permissions
There are three permission states:
GRANT: it means you have the right to perform
the action on the object.
GRANT_W_GRANT: it means you can also give
a permission to others on the same object.
DENY: it means you cannot perform this action.
REVOKE: it means cancelling the permission
that has been granted.
You can give permission on the server,
schema, or database levels.
Server Permissions
in this type, you can specify the permission
and the login that you will assign it to.
The following is granting Carol a permission
to create databases
GRANT CREATE ANY DATABASE TO Carol
Granting a permission to alter any logins
with grant
GRANT ALTER ANY LOGIN TO Carol
WITH GRANT OPTION
To cancel the previous grant
REVOKE ALTER ANY LOGIN TO Carol CASCADE
Server Permissions
Database and Schema
Permissions
You can grant a control permission on
Sales schema to Carol
GRANT CONTROL ON SCHEMA :: Sales TO Carol
GO
You can also specify many actions on a
specific table in the schema
GRANT SELECT, UPDATE ON [Link] TO Carol
GO