SQL Security– Demystifying Principals, Securables and Permissions November 5th, 2013

Vinod Kumar

I have written other blog posts around SQL Server: Security Practices and Checklist before. But many of my customers come back to basics and ask questions around what Principals, securables and permissions are. This blog post is inspired to those folks who want to understand from the basic. In future posts, I will dwell into more details into each of these security topics.

What are Principals?

A principal is any authenticated identity that can be given permission to access an object in the database system. SQL Server documentation distinguishes between indivisible principals, which are single identities (such as logins), and collection principals, which are collections of identities (such as fixed server roles).

Principals exist at three levels: Windows, SQL Server, and database. The types of principals possible at each of these levels are shown in the following table.

Level

Principals

Windows

Windows local user account

Windows domain user account

Windows group

SQL Server

SQL Server login

SQL Server role

Database

Database user

Database role

Database group

Application role

What are Securables?

Securables are the resources to which the SQL Server authorization system controls access. Securables are arranged in nested hierarchies called scopes, which can also be secured. The three securable scopes are server, database and schema. Securables at the Windows level include files and registry keys.

The Server Scope

Securables contained in the server scope include:

  1. Logins
  2. HTTP endpoints
  3. Certificates
  4. Event notifications
  5. Databases
The Database Scope

Securables contained in the database scope include:

  1. Users
  2. Roles
  3. Application roles
  4. Assemblies
  5. Message types
  6. Service contracts
  7. Services
  8. Full-text catalogs
  9. DDL events
  10. Schemas
The Schema Scope

Securables contained in the schema scope include:

  1. Tables
  2. Views
  3. Functions
  4. Procedures
  5. Queues
  6. Types
  7. Rules
  8. Defaults
  9. Synonyms
  10. Aggregates

What are Permissions?

Permissions are the rules that govern the level of access that principals have to securables. Permissions in a SQL Server system can be granted, revoked, or denied. Each of the SQL Server securables has associated permissions that can be granted to each principal. SQL Server 2005 introduces the ability to grant permissions at the server scope. In previous releases of SQL Server, server-level permissions were managed using fixed server roles.

The specific permissions associated with individual securables vary depending on the kinds of actions that a securable supports. The following table lists a few example permissions at various scopes. For a full list of permissions, see SQL Server Books Online. Below is a sample set:

Securable

Permission

Description

Server

CONNECT_SQL

Connect to the server.

Server

CREATE LOGIN

Create a login.

Server

ALTER ANY LOGIN

Alter any login in the server scope.

Server

CONTROL SERVER

Full system administrative control.

Login

ALTER

Alter the login.

Login

IMPERSONATE

Impersonate the login.

Database

CREATE TABLE

Create table in the database.

Database

ALTER ANY USER

Alter any user in the database.

Database

CONTROL

Full control of the database.

User

ALTER

Alter the specified user.

Schema

SELECT

Select rows from any object in the schema.

Schema

ALTER

Alter any object in the schema.

Schema

TAKE OWNERSHIP

Take ownership of the schema.

Table

SELECT

Select rows from the table.

Table

ALTER

Alter the table.

Table

CONTROL

Full control of the table.

From SQL Server 2005 we support inherited permissions for securables contained in a given scope. For example, a principal granted CONTROL permission on a database object will automatically inherit CONTROL permission on all securables contained in that database and all securables contained in the schemas within that database

As I sign off, want to understand how many of you take security seriously in your SQL Server deployments? How many out there still use the SA accounts in your environments and application connections? Please understand, Security is not an after thought but something part of your fundamental application deployments.

Share this article

Tags: , , , , , , , ,

This entry was posted on Tuesday, November 5th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply



 

Email
Print