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.
The four key aspects of the SQL Server security are: securables, permissions, principals, and authorization.
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.
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.
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:
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
Sujith Kumar has over 25 years of professional experience in the IT industry. Sujith has been extensively involved in designing and delivering innovative solutions for the Fortune 500 companies in the United States and across the globe for disaster recovery and high availability preparedness initiatives. Recently after leaving Quest Software/Dell after 19 years of service he was working at Cirro, Inc. focusing on database management and security. His main focus and area of interest is anything data related.
Sujith has a Master of Science in engineering degree from Texas A&M University and a Bachelor of Science in engineering degree from Bangalore University and has published several articles in referred journals and delivered presentations at several events.
Adopting a Data Access Governance strategy will help any organization achieve stronger security and control over their unstructured data. Use this free guide to help choose the best available solution available today!Read more
Start a Free Stealthbits Trial!
No risk. No obligation.