Continuing on last week’s post on security principals, this week’s installment in the series on SQL Server security takes a look at securables.

Securables

Securables is a collective term for everything that you can control access to. You can set permissions on the server level, on databases, schemas within the database as well as all manner of database objects – even individual table columns.

Permissions on any securable are subject to inheritance: many securables have “children” which will inherit permissions from parent securables.

Inheritance

Permissions on a securable are always implicitly inherited to its child securables. For instance, you can grant a user SELECT permission on a SCHEMA. It goes without saying that the schema itself doesn’t contain any data you can view, but you will have implicitly granted the user SELECT permissions on all views, table-value functions and tables within the schema. The different levels, top-to-bottom, are:

Server: Includes all databases, endpoints, logins, and of course, the server itself. Any permissions you set on the server will inherit to all databases. Server-level permissions are given to server principals, i.e. logins or fixed server roles.

Database: Permissions on the database level apply to everything within the database, including the database itself. Database permissions (and those on levels below) are granted to database principals, i.e. users or database roles.

Schema: Affects those objects that are schema-linked: tables, functions, views, procedures user-defined types, xml schema collections, etc. Note that not all securables are schema-linked: Assemblies, certificates, contracts and services, among others, are not tied to a schema and are direct children of the database.

Objects (and columns): The remaining objects (those belonging to a schema or directly to a database) are the most detailed securables, although tables and views can even have column-level permissions.

Using the sys.fn_builtin_permissions() function, you can view the different levels (known as classes) and their parents:

SELECT DISTINCT parent_class_desc, class_desc
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY 1, 2;

Conflicts

Just as with security principals, the fact that permissions are inherited from one securable to its children creates the possibility of permission conflicts. If you grant EXECUTE permissions on a schema, but deny that same permission on a specific stored procedure in the schema, the principal in question will have EXECUTE permissions on everything within the schema, except for that stored procedure. Again, any single rule that denies a permission to a securable always has precedence over other rules that implicitly or explicitly grant the same permission to the same principal.

Using schemas to assign permissions

When possible, try to assign permissions to higher-level securables, such as schemas or even on the database level, instead of doing nitty-gritty object-level assignments. This gives you a much, much clearer overview of who can do what. With that said, you may want to plan the schemas of your database with this kind of security layout in mind early on.

Here are a few examples of different strategies:

Organizational: The traditional approach is to divide data into schemas by business function. This is good if different users need access to different sources of data, but it doesn’t automatically provide a good strategy to separate, for instance, those who can execute code from those who have more tightly controlled read-write or even read-only access to certain objects.

Functional: Another example: procedures and functions required to perform certain tasks can be grouped in their own schemas. This would allow you to designate a single database role with EXECUTE access tho these schemas. You could have a designated schema that contains all ETL procedures, another schema that contains all the logic to perform a certain type of business logic, a reporting schema, etc.

Layered: You might also have different “layers”, or tiers, in an OLTP or datawarehouse database, placed in their own schemas where, for instance, only developers and ETL service accounts can access the “raw” data, while the end-users only have read-only permissions on the refined datamart views at the very top of the “food chain”.

You can actually think of these three approaches as three “dimensions” of your securables. It’s pretty common to see this type of segmentation not only in the form of different schemas, but also in multiple databases.

Ownership chaining

To complicate things a little bit more, ownership chaining allows a user to execute a module (i.e. a stored procedure, view, etc) without actually having permissions on the underlying tables that the module accesses. We’ll look more into ownership chaining in a future post.

Next week: Connecting the dots

Stay tuned for next week’s post, where we’re going to take a closer look at permissions, the actual “facts” that connect principals with securables.