SQL Server Security Concept

SQL Server Security Concept
Concept about Logins, Users, group,Principles, Roles and Schema.

In SQL server security structure is little complex from learning point of view.
Let's understand the relation among logins, users, principles, roles, databases, schemas by a diagram:

SQL Security Concept

   

 Login:

  Login 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:

Login Concept

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.

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 mode in SQL server:

a. Windows authentication logins (Windows login)

b. Mixed authentication (Window authentication and Sql server authentication)

Important point about Login:

1. 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.
2. 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. Make sure Guest user is always disable in Model & all other user databases.
   Query to Enable\Disable Guest User in a database:USE <DB_Name>
GO
-- Note: NEVER run this on production server
-- Enabling guest account
GRANT CONNECT TO guest
GO
--Disabling guest accountREVOKE CONNECT TO guest
GO
3. It is not possible to drop user dbo in all the databases.
4. It is not possible to drop the guest user only in the master and tempdb databases.
5. 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

User:  

User is used to assign database side permissions. We can assign specific permissions  to different users on different database objects. Permission can be assigned to a user using Database roles also.

Users are mapped to logins and the mapping is expressed by the SID property of logins and users. A login maps to a user in a database if their SID values are identical. Depending on the type of login, we can therefore have a categorization of users that mimics the above categorization for logins; so, we have SQL users and Windows users and the latter category consists of users mapped to Windows user logins or users mapped to Windows group logins.

So the summary is : A login provides access to the server and to further get access to a database, a user mapped to the login must exist in the database.

Script to get all users in the current database:

SELECT name

FROM sys.database_principals

WHERE [TYPE] IN ('S','U','G')

name

dbo

guest

INFORMATION_SCHEMA

sys

Amit-PC\Amit

Amit-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')

Database permission:
These 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

Roles

Roles are used to assign permission to different logins & Users. There are different types of Roles.

Server Role
Database Role
Custom Roles

1. Server Role: Server wide roles are used to assign server wide permissions to different logins.

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.

sysadmin is a Role which gives full permission to a login to do anything in the server. sa is a login in SQL server which is member of sysadmin Role. So sa login can be used to connect and administer a SQL server if windows login can not be used. If we need to create another SQL server administrator, we can create a new SQL server login and add that login to sysadmin Role.

Server Roles

2. Database Role
Database Roles are used to assign Database wide permission to users. Database permissions are usually assigned using following built in Database Roles.

db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
public

dbo is a user in every SQL database. dbo will be assigned the Role db_owner by default. This db_owner Role gives full permission to the dbo user in a SQL database.

Database Role

3. Custom Roles

We can also create Custom Roles as per our requirements.

Syntax:

USE <DB_Name>
GO
Create role <Role_Name>;
GO

Example:

USE TestDB
GO
Create role UserSp_Role;
GO

-- Grant access to user created stored procedures
SELECT 'GRANT EXECUTE ON '+name+' TO UserSp_role;' from sys.procedures where name not like 'sp_MS%'

GO
--copy and run the output of the above query on this database.
EXEC sp_addrolemember N'UserSp_role', N'TestDB'
GO

Schema

Schema can be imagined as a container to hold objects in a SQL database.  For example, when we create  a table in a SQL database, we are creating it in a schema.
Every schema will be owned by a user or a Database wide Role. The user or Role who owns the schema will also own all objects inside that schema. This gives a flexible permission structure to SQL server  database objects.
If a schema is owned by a Role, every user who is member of that Role will also get ownership on that schema. Such a permission inherited by members of a role on a schema is also an Implied permission.

Main advantage of  schema is that users can be easily removed since they are not owning any database objects. Even if a user owns a schema, we can easily change ownership of that schema to another Role or user before deleting that user. Obviously, it is easier than changing ownership of every database objects as it was in SQL server versions prior to 2005.

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')

Securables

Anything that can be secured in a SQL server can be called a securable. In short, access to a securable can be restricted or regulated using SQL server authentication system. Database, table, schema, login, User, Role etc are examples of securables.
Prinicipals
Any entity that can request access to a SQL server resource can be called as a Principal. SQL server login, Windows Login, database user etc are examples of principals.

Script to get all the server principle in Sql Server:

SELECT

      name AS RoleName

FROM sys.server_principals