- Access to a SQL Server database takes place through three levels of security.
- In each one you need to have access and permissions configured independently.
- You may need to:
- (1) Login in to a Windows domain (level 1) (except for non-windows clients)
- (2) The Windows login is then mapped to SQL Server Login (level 2)
- (3) The SQL Server Login is mapped to a database user (level 3).
- Controls how application users connect to SQL Server
- (a) Windows Authentication mode
- Default and recommended
- Only authenticated Windows users can access the SQL Server instance
- Allowed connections:
- by users who are authenticated through the Windows Active Directory service or
- by the local user account databse on the SQL Server machine.
- A Windows Login(within SQL Server) has to be created for each Windows user or group that needs acess to the SQL instance.
- (b) Mixed Mode Authentication
- Windows users can be mapped to SQL Server Windows logins or
- SQL Logins can be created directly in SQL Server.
- This mode is required when non-Windows clients need to access the server.
- SQL Logins are not considered as secure as Windows Logins.
- While Windows uses Kerberos authentication, SQL Logins store username/passwd in the master database.
- If the use of SQL Logins is a requirement, you should also use some form of lower-layer network encryption (i.e. IPSec or SSL).
- Principals: Entities (individuals, groups, processes) that can request SQL Server resources.
- Securables: the server, databases, and objects within databases.
- Principals can be arranged in a hierarchy
- Scope of influence of a principal depends on:
- (a) its definition scope: Windows -> Server -> database
- (b) whether principal is indivisible (i.e. windows login, SQL Logins) or collection (i.e windows group)
- Every principal has a security indentifier (SID)
 Windows-level principals |
- Windows group
- Windows domain login
- Windows local login
|
 SQL Server-level principals |
- SQL Server Login
- The sa login is created when instance is installed.
- sa default database: master.
- Server-level principals for internal use: names enclosed with '##' (certificate-based)
- Fixed Server Role:
sysadmin, serveradmin, securityadmin, processadmin, - setupadmin, bulkadmin, diskadmin, dbcreator, public
|
 Database-level principals |
- Database User
- Every database includes information_schema and sys.
- These entities appear as users, BUT information_schema and sys ARE NOT principals. Cannot be dropped.
- The guest user is created by default in every user database.
- The guest user cannot be dropped, but can be disabled (revoke CONNECT), except in master and tempdb.
- Fixed | Flexible Database Role
(i.e. public. every database user belongs to the public db role.)
- Application Role
|
| Types of SQL Server Logins:
(neither of which are mapped to an OS user) |
- Windows Logins
- SQL Server Logins
Creating a SQL Server Login
(a) Create a SQL Server login that uses Windows Authentication
-- domain/user have to be a valid/existing Windows user
Create Login [domain/user] from windows;
-- domain/group has to be a valid Windows group
create login [domain_name/group_name] from windows;
(b) create a SQL Server Login that uses SQL Server authentication
CREATE LOGIN [test_SQLAuth_1] WITH PASSWORD=***,
DEFAULT_DATABASE=[Books],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO
ALTER LOGIN [test_SQLAuth_1] DISABLE
GO
Drop login test_SQLAuth_1;
(a) use sys.server_principals
select name, type_desc, default_database_name
from sys.server_principals;
name type_desc default_database_name
----------------------------------------------
sa SQL_LOGIN master
public SERVER_ROLE NULL
...
NT AUTHORITY\SYSTEM WINDOWS_LOGIN master
NT SERVICE\MSSQL$SQLSERVER08 WINDOWS_GROUP master
iranmr-PC\iranmr WINDOWS_LOGIN master
NT SERVICE\SQLAgent$SQLSERVER08 WINDOWS_GROUP master
..
test_SQLAuth_1 SQL_LOGIN Books
(b) use sys.sql_logins
select name, type_desc, default_database_name
from sys.sql_logins;
name type_desc default_database_name
-----
sa SQL_LOGIN master
...
test_SQLAuth_1 SQL_LOGIN Books
Server-Level Roles (Fixed Server Roles)
- SQL Server-level Logins can be managed using fixed server roles.
- There are 9 Server-level roles.
sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator, public
- Roles are similar to Windows OS groups
- Server-level roles: also called fixed server roles (because cannot be created by users)
- Have server-wide scope.
- You can assign SQL Server logins, Windows accounts, and Windows groups to server-level roles.
Information about Server-Level Roles:
exec sp_helpsrvrole;
ServerRole Description
-----
sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators
exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
-----
sysadmin sa
sysadmin NT AUTHORITY\SYSTEM
sysadmin NT SERVICE\MSSQL$SQLSERVER08
sysadmin iranmr-PC\iranmr
sysadmin NT SERVICE\SQLAgent$SQLSERVER08
Adding/Dropping a Server Login to a Server-level Role
exec sp_addsrvrolemember test_SQLAuth_1, sysadmin;
exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
-----
sysadmin sa
sysadmin NT AUTHORITY\SYSTEM
sysadmin NT SERVICE\MSSQL$SQLSERVER08
sysadmin iranmr-PC\iranmr
sysadmin NT SERVICE\SQLAgent$SQLSERVER08
sysadmin test_SQLAuth_1
exec sp_dropsrvrolemember test_SQLAuth_1, sysadmin;
Database users are principals at the database level.
A database user needs to be created for each login that needs to access the database.
When a login that does not have a user mapped to it tries to access a database, it is logged as the guest database user.
However, this only happens if the guest user has been
granted CONNECT to the database.
(a) Create database used mapped to an existing SQL Server Login
USE [Books]
GO
CREATE USER [test_auth_dbbooks1] FOR LOGIN [test_SQLAuth_1]
WITH DEFAULT_SCHEMA=[dbo]
GO
 (b) Check the created database user among the database principals |
use Books;
go
select name, type_desc, default_schema_name
from
sys.database_principals;
name type_desc default_schema_name
-------
public DATABASE_ROLE NULL
dbo WINDOWS_USER dbo
guest SQL_USER guest
INFORMATION_SCHEMA SQL_USER NULL
sys SQL_USER NULL
test_auth_dbbooks1 SQL_USER dbo
...
Fixed | Flexible Database Roles
- Roles are security principals that group other principals.
- Can be used to group database users with similar access privileges.
- You can create new (flexible) database roles to define groups based on business requisites.
- You can nest roles (add role into other role)
- Create new role:
CREATE ROLE role_name
- Add user to role:
exec sp_addrolemember role_name, user_name.
- Fixed roles exist in all databases:
db_onwer, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader
Application Role
- Is a database principal that enables an application to run with its own, user-like permissions.
- Can be used to enable access to specific data only to users connecting through a specific application.
- Application roles contain no members.
- How it works:
- (1) Client application connects to SQL Server as a database user.
- (2) Client application executes sp_setapprole.
- (3) If validated, a context switch occurs and the application assumes the permission of the application role.
CREATE APPLICATION ROLE weekly_receipts
WITH PASSWORD = '987G^bv876sPY)Y5m23',
DEFAULT_SCHEMA = Sales;
GO
Managing Schemas
- Schemas are collections of database objects that form a single namespace.
- Starting in SQL Server 2005, schemas and database users are separate entities.
- User name is no longer part of the object name.
- Each schema is now a distinct namespace that exists independently of the database user who created it.
- Each schema is owned either by a user or a role.
- If you need to drop a user, you need to transfer the schema ownership to someone else first.
- You can define a default schema for a user.
- Implications of the separation of ownership:
- Ownership of schemas is now transferable
- Objects can be moved between schemas.
- Multiple database users can share a single default schema.
- Improved object-permission management.
- Schema can be owned by any database principal: user, database role, application role.
Checking schemas |
use Books;
go
select * from sys.schemas;
name schema_id principal_id
------------------ ---------- ------------
dbo 1 1
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
db_owner 16384 16384
db_accessadmin 16385 16385
db_securityadmin 16386 16386
db_ddladmin 16387 16387
db_backupoperator 16389 16389
db_datareader 16390 16390
db_datawriter 16391 16391
db_denydatareader 16392 16392
db_denydatawriter 16393 16393
| Using SQL Server and Database Principals, Schemas, application roles and context switching
(a) Create a SQL Server login, database user and schema.
(b) Create application role associated with the new schema
(c) Activate the new application role, switching user context
(d) Return to previous context.
|
-- Using database books
USE books;
GO
(1) Create a SQL Server login user1
CREATE login user1 WITH password = ***;
(2) Create a [books] database user user1.
-- user1 is created in the [books] database,
-- is associated with user1 SQL Server principal (login)
-- has schema1 as default schema.
CREATE USER user1 FOR login user1 WITH default_schema = schema1;
(3) Query sys.sql_logins to check that the SQL Server principal was created
SELECT name, type_desc, default_database_name
FROM sys.sql_logins;
name type_desc default_database_name
-------------- --------- ---------------
sa SQL_LOGIN master
...
test_SQLAuth_1 SQL_LOGIN Books
user1 SQL_LOGIN master
(4) Query books.sys.database_principals to check that databse user user1 was created
SELECT name, principal_id, type_desc, default_schema_name
FROM sys.database_principals;
name principal_id type_desc default_schema_name
------------------- ------------- ------------- ---------------
public 0 DATABASE_ROLE NULL
dbo 1 SQL_USER dbo
guest 2 SQL_USER guest
INFORMATION_SCHEMA 3 SQL_USER NULL
sys 4 SQL_USER NULL
user1 6 SQL_USER schema1
db_owner 16834 DATABASE_ROLE NULL
...
(5) Query books.sys.schemas to check that the schema1 DOES NOT yet exist.
SELECT name, schema_id, principal_id
FROM sys.schemas;
name schema_id principal_id
----------------- --------- --------
dbo 1 1
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
db_owner 16384 16384
...
(6) Create schema books.schema1, owned by user1
USE Books;
GO
CREATE SCHEMA schema1 AUTHORIZATION user1;
(7) Query sys.schemas to check schema1 was created.
SELECT name, schema_id, principal_id
FROM sys.schemas;
name schema_id principal_id
----------------- --------- --------
dbo 1 1
guest 2 2
INFORMATION_SCHEMA 3 3
sys 4 4
schema1 5 6 -- <= owner: user1
db_owner 16384 16384
...
(8) Create table emp_sch1 in schema1, and insert one row.
CREATE TABLE schema1.emp_sch1 (id INT, name VARCHAR(20));
INSERT INTO books.schema1.emp_sch1
VALUES (1, 'John');
(9) Query books.schema1.emp_sch1 to check the inserted row.
SELECT *
FROM books.schema1.emp_sch1;
id name
-- ----
1 John
(10) Create application role approle_sch1, with default schema schema1. Grant select on schema1 to approle_sch1.
USE Books;
GO
CREATE application role approle_sch1
WITH password = 'p4sswd',
default_schema = schema1;
GRANT
SELECT ON schema::schema1 TO approle_sch1;
(11) Query the current user (should return 'dbo')
SELECT USER_NAME() AS USER_NAME;
user_name
---------
dbo
(12) Switch context to approle_sch1 using sp_setapprole. Use cookies to save orginal context information.
DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole 'approle_sch1', 'p4sswd',
@fCreateCookie = true,
@cookie = @cookie OUTPUT;
SELECT USER_NAME() AS USER_NAME;
-- should return approle_sch1
SELECT *
FROM emp_sch1;
-- should return one row
EXEC sp_unsetapprole @cookie;
GO
user_name
----------
approle_sch1
id name
-- ----
1 John
(13) Revert to the original context using sp_unsetapprole.
SELECT USER_NAME() AS USER_NAME;
user_name
----------
dbo
