SQL Server security – (Part 4)
Author: Basit A. Farooq
In this forth part of the SQL Server security article series, you’ll first learn how to manage securables at the server scope. Then, you learn how to set permissions using GRANT, DENY, and REVOKE. You’ll also learn how to use fixed server roles to configure access permissions to server-scoped objects.
Login permissions
Logins are principals but also securable objects. You can grant, deny, and revoke the following permissions on a login:
- CONTROL — Full control of the login.
- IMPERSONATE — can impersonate the login.
- VIEW DEFINITION — can view the metadata.
- ALTER — can modify the login.
When one login impersonates another, it performs actions inside the security context of the login it impersonates. This is sometimes necessary to allow a user with limited permissions to execute a statement that requires greater permissions.
As with server permissions, you use GRANT, DENY, and REVOKE statements to manage permissions to a login. The syntax is for granting permission to a login is:
GRANT permission [, …]
ON LOGIN:: login
TO <server_principal> [,…]
[WITH GRANT OPTION]
[AS login]
You must be logged on to the master database to run the command. You must have CONTROL permission on the login or the server level ALTER ANY LOGIN permission. If the login is mapped to a Windows user or group, you need to use the syntax [domainnameusername] (delimited identifier syntax) to specify the login. For example, to grant maryB permission to impersonate georgeV, you execute:
GRANT IMPERSONATE
ON LOGIN::georgeV
TO maryB
The DENY statement has the following syntax:
DENY permission [,…n]
ON LOGIN::login
TO <server_principal>
[CASCADE]
[AS login]
The REVOKE statement has the following syntax:
REVOKE [GRANT OPTION FOR] permission [,…n]
ON LOGIN::login
{FROM | TO} <server_principal> [,…n]
[CASCADE]
[AS login]
Database permissions
A database has a large number of available permissions. You can grant, deny, or revoke permissions directly.
Logins can have implicit permissions on a database due to permissions granted or denied on the server. For example, a principal with ALTER ANY DATABASE permission on the server automatically has the ALTER permission on the database and all other permissions that implies.
The ALTER database permission implies several permissions including:
- ALTER ANY USER
- ALTER ANY FULLTEXT CATALOG
- CREATE DEFAULT
In turn, the ALTER ANY FULLTEXT CATALOG permission implies the CREATE FULLTEXT CATALOG permission. The permission hierarchy is complex and you need to be careful when granting permission at a high level.
Other database permissions include:
- CONTROL — Full control of the database.
- BACKUP DATABASE — Ability to back up the database.
- CONNECT — Ability to connect to and use the database.
- DELETE — Ability to delete the database.
- EXECUTE — Ability to execute objects in the database.
- INSERT — Ability to insert records into all tables in the database.
- SELECT — Ability to retrieve records from all tables in the database.
You can grant permission to the following database principles:
- Database users
- Database roles
- Application roles
Using GRANT to grant permission on a database
The syntax for granting permission on a database is similar to what you’ve already seen:
GRANT permission | ALL [PRIVILEGES]
TO database_principal [,…n] [WITH GRANT OPTION]
[AS <database_principal>]
You use the ALL keyword, with or without PRIVILEGES, to grant the following permissions:
- BACKUP DATABASE
- BACKUP LOG
- CREATE DATABASE
- CREATE DEFAULT
- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE RULE
- CREATE TABLE
- CREATE VIEW
For example, to grant the guest user the CONNECT permission, you execute:
GRANT CONNECT TO guest
To grant the INSERT and UPDATE permissions to the SalesManager and also give the user the ability to grant INSERT and UPDATE permission to others, you execute:
GRANT INSERT, UPDATE
TO SalesManager WITH GRANT OPTION
Denying permission to a database
The DENY statement uses a similar syntax:
DENY permission | ALL [PRIVILEGES]
TO database_principal [,…n] [CASCADE]
[AS <database_principal>]
For example, to prevent the guest account from connecting to the database, execute:
DENY CONNECT TO guest
As with server permissions, a denied permission takes precedence over permissions granted through other sources. For instance, if your database user account is explicitly denied a permission and is granted the permission through a database role, the user is still effectively denied the permission.
Revoking permission to a database
To revoke permissions on a database, you use the following syntax:
REVOKE [GRANT OPTION FOR] permission | ALL [PRIVILEGES]
{TO|FROM} database_principal [,…n]
[CASCADE]
[AS <database_principal>]
For example, to remove the permission to grant others the INSERT permission from SalesManager, you execute:
REVOKE GRANT OPTION FOR INSERT
FROM SalesManager
Managing permissions using SQL Server Management Studio
You can also manage permissions using SQL Server Management Studio. For example, the following are the steps to grant, deny, or revoke user permissions to securables via SSMS 2014:
- Launch SQL Server Management Studio.
- In Object Explorer, expand the Databases folder
- Right-click the database for which you want to manage permission, and then choose Properties.
- Click Permissions.
- Click Add to add a user or role.
- Enter the name of the user or role.
- Click Check Names.
- Click OK.
- Select the user or role and check Grant, With Grant, or Deny for each permission you want to assign. To revoke a permission, clear the box.
- Click on OK to complete this action.
- You can also view effective permissions by selecting a user or role and clicking Effective Permissions.
Endpoint permissions
An endpoint defines a connection port used to support Native XML Web services or Service Broker applications. An endpoint is a securable object. You can grant, deny, or revoke the following permissions on an endpoint:
- ALTER
- CONNECT
- CONTROL
- TAKE OWNERSHIP
- VIEW DEFINITION
You assign permissions using GRANT, DENY, and REVOKE statements. The syntax is similar to what you’ve already seen and isn’t covered in this article series.
Viewing server and server securable permissions
You use the sys.server_permissions catalog view to retrieve the permissions set on the server and on server-level objects. It returns the columns described in the following table:
Column |
Description |
|
class
|
Class on which the permission is assigned. Server (100), Server principal (101), Endpoint (102).
|
|
class_desc
|
Description of the object on which the permission is assigned.
|
|
major_id
|
ID of the securable, on which permission exists, interpreted according to class. For most, this is just the kind of ID that applies to what the class represents. Interpretation for non-standard is as follows:
100 = Always 0
|
|
minor_id
|
Secondary ID of thing on which permission exists, interpreted according to class.
|
|
grantee_principal_id
|
Principal ID of the login or role the permission is granted to.
|
|
grantor_principal_id
|
Principal ID of the login who granted the permission.
|
|
type
|
Type of permission.
|
|
permission_name
|
Name of permission.
|
|
State
|
Can be set to D (Deny), R (Revoke), G (Grant), or W (Grant with Grant option).
|
|
state_desc
|
Description of the state.
|
For example, to view all permissions set on the server, execute:
SELECT * FROM sys.server_permissions
WHERE class = 100
Or the following query lists the permissions explicitly granted or denied to server principals:
SELECT pr.principal_id, pr.name, pr.type_desc,
pe.state_desc, pe.permission_name
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;
Continue to Part-5…