Effective Permissions

Effective permissions relates to principals and securables. All three of these terms are complex, but interrelated. They all refer to security, access, and what level of interaction with a database an entity has. Each term has a specific role and is relative to the different components of a database.

Securables are the different components of a database that can have set permissions. There are different types of securables for each component within a database. Each securable can have its own permissions independent of the parent, with those permissions per securable creating nested hierarchies. The different hierarchies are “scopes” and are ordered by server, database, and schema.

Each server, database, and schema can have its own securable hierarchy. Schemas are the components that make up a database, components such as columns, tables, and rows. A scope can consist of elements of all three types, and range across the database. Each scope can contain different permissions, with different levels of interactivity per entity. Also, each nested hierarchy can also have different permissions as well.

A SQL database uses securables to regulate an entity’s access to the different components of the database. An entity is a user or object that accesses these components and are called a “principal.” Principals can be either people, a group, tables, databases, or other components that interact with data. There are hierarchies of principals as well, which are also called scopes. Much like a securables scope, principal scopes consist of server, database, and schema. They too can range across the database and have different permission settings per condition.

Effective permissions is the relationship between principals and securables. The SQL database engine uses effective permissions to determine which principal has access to what securable. There are three factors that determine effective permissions, each of which provides the SQL database engine information on which principal is authorized to to do what with a securable. Effective permissions are based on the specific permissions granted to the securable, the parents of a secruable, and the principals themselves.

There are many different types of permissions that are available for each scope and element of both securables and principals. To make it more complicated, the permissions may be different depending on the component that is actually being secured. Different components of a database will have different permissions available. These are in turn related to the scopes of each securable and principal, as well as the relationships between the two.

Since this is a very complex process with exponential permutations, SQL provides code to generate a table that will display the permissions for each condition. There are also different third-party products that make reading, following, and adjusting effective permissions simple.