Shahid Naeem

Principals: Principals are the entities that request security to SQL Server resources.

Securables - Securables are the SQL Server resources that permissions can be granted to.

Permissions - The permissions link principals with securables. For example, a SQL LOGIN (the  principal) needs to CREATE (the permission) databases (the securable). Together, these three elements represent a complete security assignment.

SA account

1. The SA account is a SQL LOGIN that is assigned to the SysAdmin fixed-server role.
2. The SA account cannot be removed, and it can always be used to gain access to SQL Server.
3. The SA account should always have a strong password to prevent malicious attacks, and it should be used only by database administrators.

BUILTIN\Administrators login

1. The login is made a member of the SysAdmin fixed-server role
2. It is a windows group that corresponds to the local administrators group for the machine that SQL Server is running on.

NT AUTHORITY\SYSTEM login

1. This account is related to the local system account that SQL Server services can run under.
2. It is also added as a member of the sysadmin fixed-server role.
3. This account can also be removed if the SQL Server services are not running with the local system account. This should be done with caution, however, because it can affect applications such as Reporting Services.

dbo User

1. The dbo user is the database owner and cannot be deleted from the database.
2. Members of the Sysadmin server role are mapped to the dbo user in each database, which allows them to administer all databases.
3. Objects owned by dbo that are part of the dbo schema can be referenced by the object name alone. When an object is referenced without a schema name, SQL Server first looks for the object in the default schema for the user that is connected. If the object is not in the user’s default schema, the object is retrieved from the dbo schema. Users can have a default schema that is set to dbo.

guest User

1. The guest user is created by default in each database when the database is created. This account allows users that do not have a user account in the database to access the database.
2. By defa-ult, the guest user does not have permission to connect to the database. To allow logins without a specific user account to connect to the database, you need to grant
3. GRANT CONNECT TO GUEST
4. The guest account is a special account and does not have an associated login. You can change the access for the guest account by using T-SQL commands instead.
5. EXEC sp_addrolemember N’db_datareader’, N’guest’

The INFORMATION_SCHEMA User

The INFORMATION_SCHEMA user owns all the information schema views that are installed in each database. it cannot be dropped from the database.

The sys User

The sys account gives users access to system objects such as system tables, system views, extended stored procedures, and other objects that are part of the system catalog. The sys user owns these objects. Like the INFORMATION_SCHEMA user, it cannot be dropped from the database.

List All Users

SELECT left(u.name,25) AS [Name], type, left(type_desc,15) as type_desc

FROM sys.database_principals AS u

WHERE (u.type in (‘U’, ‘S’, ‘G’)) ORDER BY 1

View all object by user

SELECT name, object_id, schema_id, type_desc

FROM sys.all_objects

WHERE OBJECTPROPERTYEX(object_id, N’OwnerId’) = USER_ID(N’sys’)

ORDER BY 1

User/Schema Separation

It is a collection of database objects that one user owns and that forms a single namespace. A single namespace is one in which each object name is unique and there are no duplicates.

ROLES

Permissions are applied to a role, and then members are added to the role. Any member of the role has all the permissions that the role has.

SQL Server has the following three types of roles:

1.Fixed-server and fixed-database roles

These roles are installed by default and have a predefined set of permissions.

2.User-defined roles

These roles are created in each database, with a custom set of permissions for each set of users assigned to it.

3.Application roles

These roles are a special roles that can be used to manage database access for an application.

Fixed-Server Roles ( Server levelsecurables )

ROLE               PERMISSION

Bulkadmin Allowed to run the BULK INSERT statement.

Dbcreator Allowed to CREATE, ALTER, DROP, and RESTORE any database.

Diskadmin Allowed to manage disk files that are used by SQL Server.

Processadmin Allowed to terminate SQL Server processes.

Securityadmin Allowed to GRANT, DENY, and REVOKE permissions for logins at the server and                                   database levels. Members of this role can reset passwords for SQL Server logins

Serveradmin Allowed to change serverwide configuration properties and shut down the server, if                                 needed.

Setupadmin Allowed to add and remove linked servers and execute some system stored procedures.

Sysadmin Allowed to perform any activity in the server.

Fixed-Database Roles

ROLE                           PERMISSION

db_accessadmin Allowed to add or remove database access for logins.

db_backupoperator Allowed to back up the database.

db_datareader Allowed to read all user table data.

db_datawriter Allowed to change the data in all user tables.

db_ddladmin Allowed to run any Data Definition Language (DDL) command against the                                               database. This includes commands to create, alter, and drop database objects.

db_denydatareader Denied the right to read all user table data.

db_denydatawriter Denied the right to change the data in any of the user tables.

db_owner Allowed to perform any action on the database. Members of the sysadmin fixed–                                               server role are mapped to this database role.

db_securityadmin Allowed to manage permissions for database users, including

membership in roles.

The public Role

1. The public role is a special database role that is like a fixed-database role except that its permissions are not fixed. The permissions for this role can be altered.
2. Every user in a database is automatically made a member of the public role and in turn receives any permissions that have been granted to the public role. Database users cannot be removed from the public role.
3. The public role is similar in function to the guest user that is installed by default in each database. The difference is that the permissions granted to the guest user are used by any login that does not have a user account in the database. In this case, the login is allowed to enter the database via the guest account. In the case of the public role, the login has been added as a user of the database and in turn picks up any permissions that have been granted to the public role.

To view the permissions associated with the public role,

SELECT top 5 g.name, object_name(major_id) as ‘Object’, permission_name

from sys.database_permissions p

join sys.database_principals g

on p.grantee_principal_id = g.principal_id

and g.name = ‘public’

order by 1,2

This SELECT utilizes two catalog views that contain security information. The SELECT returns only the first five permissions for the public role, but the TOP clause can be removed to return all the permissions.

Application Role

To obtain the permissions associated with the role, the connection must set the role and supply the password.

This is done using the stored procedure sp_setapprole. You set the role to the sales application role (with the password PassW0rd) as follows:

EXEC sp_setapprole ‘sales’, ‘PassW0rd’

You can also encrypt the password:

EXEC sp_setapprole ‘sales’, {ENCRYPT N ‘ PassW0rd’}, ‘odbc’

Managing Permissions

1. A GRANT of a permission removes any REVOKE or DENY on a securable. For example, if a table has SELECT permission denied on it and then the SELECT permission is granted, the DENY permission will then be removed on that table.
2. DENY and REVOKE remove any GRANT permission on a securable. REVOKE removes any GRANT or DENY permission on a securable.
3. Permissions denied at a higher scope in the security model override grants on that permission at a lower scope. Keep in mind that the security model has the server scope at the highest level, followed by database and schema. So, if INSERT permission is denied on tables at the database level, and INSERT on a specific table in that database is granted at the schema level, the result is that INSERT is denied on all tables. In this example, a database-level DENY overrides any GRANT at the lower schema level.
4. Permissions granted at a higher scope in the security model are overridden by a DENY permission at a lower level. For example, if INSERT permission is granted on all tables at the database scope, and INSERT is denied on a specific table in the database (schema scope), INSERT is then denied on that specific table.

Statement permissions have nothing to do with the actual data; they allow users to create the structure that holds the data.

1. Create Database
2. Create Table
3. Create View
4. Create Procedure
5. Create Index
6. Create Rule
7. Create Default

The second type of permissions is object permissions, which control how users work with the actual data.

Control This permission gives the principal ownership-like capabilities on the object and all objects under it in the hierarchy. For example, if you grant a user Control permission on the database, then the user has Control permission on all the objects in the database, such as tables and views.

Alter This permission allows users to create, alter, or drop the securable and any object under it in the hierarchy. The only property the user can’t change is ownership.

Take Ownership This allows the user to take ownership of an object.

Impersonate This permission allows one login or user to impersonate another.

Create As the name implies, this permission lets a user create objects.

View Definition This permission allows users to see the Transact-SQL syntax that was used to create the object being secured.

Select When granted, this permission allows users to read data from the table or view. When granted at the column level, it lets users read from a single column.

Insert This permission allows users to insert new rows into a table.

Update This permission lets users modify existing data in a table but not add new rows to or delete existing rows from a table. When this permission is granted on a column, users can modify data in that single column.

Delete This permission allows users to remove rows from a table.

References Tables can be linked together on a common column with a foreign-key relationship, which is designed to protect data across tables. When two tables are linked with a foreign key, this permission allows the user to select data from the primary table without having Select permission on the foreign table.

Execute This permission allows users to execute the stored procedure where the permission is applied.

All the permissions in SQL Server can exist in one of three states:

Grant Granting allows users to use a specific permission. For instance, if you grant SmithB Select permission on a table, then SmithB can read the data within. You know a permission has been granted when the Allow check box is selected next to the permission in the permissions list.

Revoke A revoked permission isn’t specifically granted, but a user can inherit the permission if it has been granted to another role of which they are a member. That is, if you revoke the Select permission from SmithB, then SmithB can’t use it. If, however, SmithB is a member of a role that has been granted Select permission, SmithB can read the data just as if SmithB had the Select permission. A permission is revoked when neither Allow nor Deny boxes are selected next to a permission.

Deny If you deny a permission, the user doesn’t get the permission—no matter what. If you deny SmithB Select permission on a table, even if SmithB is a member of a role with Select permission, then SmithB can’t read the data. You know a permission has been denied when the Deny check box is selected next to the permission in the permissions list.

Tips:

1. Every principal that is granted security to SQL Server must have an associated login.
2. Logins are stored in Master database.
3. It is possible to create a new login without creating an associated database user, but a new database user must have an associated login.
4. A login is used for user authentication ( connect to SQL Server)
5. A database user account is used for database access and permissions validation (database)
6. sa, builtin\administrators, and NT authority\system) are logins that are installed by default at installation time.
7. Dbo,guest,Information_schema and sys are database users that have been created by default at installation time.

PRACTICAL

Server Level Security

LOGIN

1. Registered Servers ( alt + ctrl + G)
2. Right click on Database Engine and Create group.
3. Add registered server into created groups.
4. Right click on Register server and connect to object explorer
5. Create window user  & associated window group.(computer Management)
6. Create window login – wLogin ( Management Studio )
7. General Tab
8. Window login ( active directory )
9. Default database
10. Enforce password policy  ( only in SQL login )
11. Server Roles
12. Select Server role default is Public ( unelectable)
13. User Mapping
14. Select databases
15. Select Database roles
16. Select Server Level Securable
17. Grant Permissions
18. Status
19. Grant permission to database Engine
20. Enable / Disable Login

SERVER ROLES

1. Add members to any server roles

CREDENTIALS

1. You can map window credential to SQL authentication user to access any kind of resource  (file system )outside SQL Server.

Database level Security

SCHEMA

1. Right click on Schema and select New and enter Name
2. Select Schema owner ( users, database roles, application roles )
3. To apply Permission to object in schema add objects into it
4. Create table with associated schema

USERS

1. Create User named  sLogin associated with login
2. Default schema ( can’t assign default schema in window authenticated user )
3. Owned schema by this user ( schema has only one owner )cant unselect
4. Database roles by this user
5. Securable
6. Add object associated with schema and grant permissions
7. Extend properties ( for documention)

Creating a Windows Login

Task 1.17 Creating a Standard Login

Task 1.18 Assigning Logins to Fixed Server Roles

Task 1.19 creating a Database User Mapping

Task 1.20 Assigning User Mappings to Fixed Database Roles

Task 1.21 Creating a Custom Database Role

Task 1.22 Creating an application Role

Task 1.23 Assigning Permissions

Users, Roles and groups

Managing schemas

DDL Triggers

Default Users

1. DBO- default owner ( when login by administrator)
2. Guest – ( drop it recommended )
3. Information_Schema
4. Sys -

User mapping

Every single user has public role.  We can’t un-select it.

Grant privilege to role à grant that role to individual user

//orfan user

Exec sp_addrolemember ‘db_datawriter’ , ‘johnson\joe’

Exec sp_changeuser

Users, Server Roles,

User Mapping-database role-default schema

, Securables, Status

Practice:

Create Schemaà

create Database role –  (a ) select default schema created, (b)Members to this roles

Application role – default created schema , add securables

——————————

DDL Triggers

Monitor most DDL activity

1. Prevent unauthorized usage
2. Audit DDL

2 Scope

1. Server
2. Database

Create trigger my_ddl_trig
  on database
  for ddl_database_level_events as
  ----    8-16
  if datepart(hour,getDate())<8 or
  datepart(hour,getDate())>17
  print ‘cannot perform ddl outside of normal business  hours’
rollback