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