|
- 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).
Authentication Mode |
- 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 |
- 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 |
---|
|
SQL Server-level principals |
---|
|
Database-level principals |
---|
|
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.
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 |
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 |
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 booksUSE books; GO(1) Create a SQL Server login user1CREATE 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 createdSELECT 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 createdSELECT 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 user1USE 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; GOuser_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