Dec 6, 2014

Understanding SQL Server Security: Logins, Users, Principles, Roles and Schema by examples

Basic Concept: Explanation or difference among Logins, Users, group,Principles, Roles and Schema by examples

In SQL server security structure is little complex from learning point of view. In this post we are trying to explain it very simple and detail ways. Let's understand the relation among logins, users, principles, roles, databases, schemas etc by a diagram:

   
1. Login:

It is server level entity which is only used for authentication. Login is not a user. Each login is mapped by only one user per database. It is also possible that a login is not mapped with any users of database and vice-verse.  

Logins and Users mapping\Associate diagram:


From above diagram Login L1 is mapped with user U1 of the database D1 as well as user U1 of the database D2. In the same way Login L3 is mapped with user U2 of the database D1 and users U4 of the database D2. Login L2 has not mapped with any users of any databases. In the same way users U3 has not mapped with any login of the sql server.

Questions:

Q1. What is use of login which has not mapped with any users of any databases?
Q2. What is use of users of a database which has not mapped with any login?

Each login must have default database.  So when any end user is logged in SQL the server by using any login, it is first connected to their default database of the login and then performs any operation as a mapped user in the database of the login.

Types of Login\authentication in SQL server:

SQL server supports two types of authentication mode:
a. Window authentication
b. Mixed authentication (Window authentication and Sql server authentication)

Important point about Login:

a. Dbo is only user in all the databases where more than one logins of sysadmin role are implicitly mapped if it has not mapped explicitly to that database.
b. Guest is only user in master, tempdb and msdb databases where more than one logins of not sysadmin role are implicitly mapped if it has not mapped explicitly to that database.
c. It is not possible to drop user dbo in all the databases.
d. It is not possible to drop the guest user only in the master and tempdb databases.
e. Script to get all login in SQL Server:

SELECT
      name AS LoginName,
      type_desc AS LoginType,
      default_database_name AS DefaultDatabase
FROM sys.server_principals
WHERE [Type] <> 'R'
ORDER BY Type

Script to create login:
CREATE LOGIN <LoginName>
WITH PASSWORD = '<Password>'

2. Server Role:

As we know role is set of permissions. Server roles are those roles which are assigned to login.  We can assign more than one role to each login. Each login must have a role named PUBLIC which cannot be removed.

Script to get all the server roles in Sql Server:

SELECT
      name AS RoleName
FROM sys.server_principals
WHERE [Type] = 'R'

Script to get all the assigned roles for given login:

SELECT
      SP.name As RoleName
FROM SYS.SERVER_ROLE_MEMBERS SRM
INNER JOIN SYS.SERVER_PRINCIPALS SP
ON SRM.role_principal_id = SP.principal_id
INNER JOIN SYS.SERVER_PRINCIPALS TSP
ON TSP.principal_id = SRM.member_principal_id
WHERE TSP.name = 'LoginName'

UNION ALL
SELECT 'Public'

SQL server has eight fixed server level roles in which not any permission can be granted or revoked. All the logins must be members of server role public which cannot be removed.  Unlike to fixed server role, permissions inside server role public can be granted or revoked.    Server roles are:

S#
Role Name
1
public
2
sysadmin
3
securityadmin
4
serveradmin
5
setupadmin
6
processadmin
7
diskadmin
8
dbcreator
9
bulkadmin

In the same way we can also created user defined server role and permission can be granted according our requirement.

3. Securable:

It is resource on which SQL server need access.  In other words, it is something which needs to be secured:

List of all the securable of the SQL Server:

Securable Name
Scope
Login
Server
Server
Server
Endpoint
Server
Database
Server
Schema
Database
User
Database
Database role
Database
Application role
Database
Assembly
Database
Fulltext catalog
Database
Message type
Database
Route
Database
Service
Database
Remote Service Binding
Database
Certificate
Database
Asymmetric key
Database
Symmetric key
Database
Contract
Database
Table
Schema
Procedure
Schema
Function
Schema
View
Schema
Aggregate
Schema
Synonym
Schema
Queue
Schema
Type
Schema
XML schema collection
Schema

4. Server Principle

Principle is something which can request resources/Securable. Server roles and login are collectively called as server principle.

Script to get all the server principle in Sql Server:

SELECT
      name AS RoleName
FROM sys.server_principals
  
5. Server permission:

It is server level permissions which can be granted or revoke to either login or server roles (principle) on a particular securable.

Script to get all the permissions on a principle:

SELECT
P.name AS PrincipleName,
      P.type_desc AS PricipleType,
      class_desc AS SecurableName,

      (CASE WHEN class_desc = 'Endpoint'
THEN (SELECT Name
FROM sys.endpoints
WHERE endpoint_id = major_id)
END) AS SubClassName,

      permission_name AS PermissionName,
      state_desc AS State
     
FROM sys.server_permissions SP INNER JOIN sys.server_principals P
ON P.principal_id = SP.grantee_principal_id
ORDER BY P.name

6. User:

User is only securable who can perform some actions on a database.  So users are database level securable. If a login is member of sysadmin server role and not mapped with any users of a database explicitly then it is mapped with dbo user of the database.  And if a login is not member of sysadmin server role and not mapped with any users of a database explicitly then it is mapped with guest (If guest user is enable) user of the database master, tempdb and msdb otherwise that login can access to other database.

Script to get all users in the current database:

SELECT name
FROM sys.database_principals
WHERE [TYPE] IN ('S','U','G')

Sample output:
name
dbo
guest
INFORMATION_SCHEMA
sys
Ritesh-PC\Ritesh
Ritesh-PC\Administrator
NT AUTHORITY\NETWORK

Note: Each database must have user named INFORMATION_SCHEMA and sys. These users cannot connect any database. It used for internal purposes.

Script to map the login with user in the current database:

SELECT
      DB_NAME() AS CurrentDatabaseName,
      SP.name AS LoginName,

      CASE
            WHEN DP.name IS NULL
AND EXISTS(SELECT * FROM sys.server_role_members SRM INNER JOIN  sys.server_principals ISP ON SRM.role_principal_id = principal_id WHERE member_principal_id = SP.principal_id AND ISP.name = 'SYSADMIN')   THEN 'dbo'

            WHEN DP.name IS NULL
AND EXISTS(SELECT * FROM sys.database_permissions P INNER JOIN sys.database_principals DP
ON P.grantee_principal_id = DP.principal_id
WHERE DP.name = 'guest' AND P.permission_name = 'CONNECT')   THEN 'guest'

            WHEN DP.name IS NULL THEN 'This login cannot connect this database.'
            ELSE DP.name
      END AS UserName
FROM sys.server_principals SP
LEFT JOIN sys.database_principals DP
ON SP.sid = DP.sid
WHERE SP.[type] IN ('S','U','G')

7.  Schema:

Schema is a securable which is created to group the database objects like table, procedure etc. For example if we want to assign permissions to particular user only on   15 tables, it is better to create a schema, keep all 15 tables in the schema and assign permissions to the user schema instead of assigning  same   set of permissions in all the 15 tables one by one.

--Script to create a new schema:
CREATE SCHEMA ExactHelp

--Creating new table in the new schema
CREATE TABLE ExactHelp.tblTest(a INT)

--Script to get list of all schema in a the current database
SELECT name FROM sys.schemas

Each user must have default schema to connect any database.

--Script to get the default schema of users:
SELECT name,default_schema_name
FROM sys.database_principals
WHERE [TYPE] IN ('S','U','G')

8. Database permission:

It is those permissions which can be granted or revoked to database level principles on a particular securable.

Scrip to get permission granted to database principles:

SELECT 
      P.name AS PrincipleName,
      P.type_desc AS PricipleType,
      class_desc AS SecurableName,
      permission_name AS PermissionName,
      state_desc AS State
FROM sys.database_permissions DP
INNER JOIN sys.database_principals P

ON DP.grantee_principal_id = P.principal_id

No comments:

Post a Comment