Stealthbits

Understanding Effective Access in SQL Server

Blog >Understanding Effective Access in SQL Server
Understanding Effective Access in SQL Server
| Sujith Kumar | | Leave a Comment

Microsoft SQL Server is the third most popular Relational Database Management System (RDBMS) as of September 2020 according to DB-Engines ranking.  It is also the most confusing RDBMS when it comes to database security compared to other popular RDBMS systems, such as Oracle, PostgreSQL, etc.  Having worked with Oracle for a very long time, I found SQL Server security very confusing when I started working with it for the first time.  Part of the complication stems from the fact that SQL Server is closely integrated into the Windows operating system, which in turn is typically part of an Active Directory (AD) domain.

SQL Server has the concept of effective permission on securables, which is calculated based on the user’s membership in SQL Server roles, AD groups, Local Windows groups, and direct permissions.  AD groups or Local Windows groups are relevant only if Windows Authentication or Mixed Authentication is enabled and if those groups are granted a SQL Server login. 

Key Security Concepts

The four key aspects of the SQL Server security are: securables, permissions, principals, and authorization.

  • Securables:  Can be SQL Server, SQL Server object, database, any object defined within each database, such as, tables, procedures, views to which permissions can be assigned to.
  • Permissions:  Are actions that are allowed on the securables.  For example, a securable object table has CRETATE, DROP, MODIFY, INSERT, UPDATE, DELETE, SELECT, TRUNCATE actions among many other actions.  A principal can be given permission to carry out an action on a securable object. In SQL Server 2016 (13.x) there are 230 explicit permissions that can be granted to a user.
  • Principals: Can be SQL Server logins, SQL Server roles, Local Windows users, Local Windows groups, AD users, AD groups.  Principals are either granted (GRANT) or denied (DENY) permissions on securables.
  • Authorization: Is the process of either assigning, revoking a permission to a principal on a securable. The authorization must be a GRANT, REVOKE or DENY.

These four key concepts can be summarized in form of a statement as follows:

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

When translated to a real-world example, would be similar to the statement below:

GRANT SELECT ON [dbo].[Employees] TO jsmith;

In the above statement, I am authorizing or granting SELECT permission on a securable Employees table to a principal jsmith.

Permission Hierarchy

In SQL Server, all securables are arranged in a strict hierarchy.  A server owns all the instances, an instance owns all the databases defined within the instance, all the objects defined within a database are owned by that database.  The concept of hierarchy is important because permissions can be inherited.  For example, if a user (principal) is granted the sysadmin role (permission) at the instance (securable) level, that user will inherit all the permissions on the securables downstream from the instance level down.  The permission inherited by a user on an object from one or more roles is the effective permission or access.  To make things even more complicated, permissions on one or more downstream objects can explicitly be denied.  In such a situation, where conflicting permissions are authorized on an object, the strictest permission will prevail.  Therefore, the effective permission will be DENY and will override any GRANT permissions on a securable.  One notable exception to this rule is the sysadmin role.  If a user is granted sysadmin role, permissions are not checked further, therefore any denials on downstream objects will not be enforced.

Roles

The concept of roles in SQL Server is similar to database roles in other RDBMS platforms.  There are three distinct categories of roles in SQL Server:

  • Fixed Server Roles: In most versions of SQL Server including SQL Server 2019, there are nine fixed server roles as shown in the screenshot below.  These are pre-configured and cannot be modified or deleted.  The only exception to this rule is the public fixed server role.  Every user or login in SQL Server is granted the public role and it cannot be revoked.  Default permissions assigned to the public can be modified.
  • Fixed Database Roles:  In SQL Server 2019, there are ten fixed database roles including the public fixed database role. The fixed database role public is granted to all the users or logins and cannot be revoked.  However, like the fixed server public role, the fixed database public role can also be customized.  Therefore, additional permissions should never be granted to the public role as every login will inherit those additional permissions.  You should be using user-defined roles in such situations.
  • User-Defined Roles:  These roles are database-level securables.  Permissions on database level objects are granted to a user-defined role.  User-defined roles are a convenient way to group multiple permissions on multiple securables, which are then granted to users.

Logins

SQL Server refers to database users as logins.  Logins can be users defined locally within the SQL Server or it can be Local Windows users, Local Windows groups, AD users, and AD groups.  If an AD group or a Local Windows group is granted login access, all the users that are part of that group will inherit the permissions as well.  SQL Server is made aware of the group membership when a user from that group tries to log in to the SQL Server.

Since SQL Server does not manage or receive automatic updates about Local Windows or AD group memberships, it is next to impossible to reliably calculate the effective permission on securables.  StealthAUDIT for SQL is an AD and SQL Server auditing and reporting tool that can enumerate effective permissions for all types of logins and users.  It can break down the permissions by individual Local Windows or AD Group users and SQL Server roles on all the securables in SQL Server. Thus, making it easier to address the issue of excessive permissions on SQL Server securables.  StealthAUDIT includes a web-console referred to as Access Information Center (AIC), which can be used to search the permission by a principal. A principal could be a SQL Server user, Windows user, AD user, AD group, or Local Windows group as shown in the screenshot below.

To learn more about how StealthAUDIT for SQL can help in effectively securing SQL Server security at all levels to mitigate the risks of advanced attacks, compliance failure, and operational outage please visit our website at https://www.stealthbits.com/database-security-solution

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe

DON’T MISS A POST. SUBSCRIBE TO THE BLOG!

 

Loading

© 2020 Stealthbits Technologies, Inc.

Start a Free Stealthbits Trial!

No risk. No obligation.

FREE TRIAL