Authorization and Permissions in SQL Server
In this we will learn about authorization and Permissions in SQL Server. However, when you create database objects, you must explicitly grant permissions to make them accessible to users. Every securable object has permissions that can be granted to a principal using permission statements.
The Principle of Least Privilege
Developing an application using a least-privileged user account (LUA) approach is an important part of a defensive, in-depth strategy for countering security threats. The LUA approach ensures that users follow the principle of least privilege and always log on with limited user accounts. Moreover, administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted.
Role-Based Permissions
Granting permissions to roles rather than to users simplifies security administration. Permission sets that are assigned to roles are inherited by all members of the role. However, it is easier to add or remove users from a role than it is to recreate separate permission sets for individual users. Roles can be nested. And, too many levels of nesting can degrade performance. Further, you can also add users to fixed database roles to simplify assigning permissions.
Permissions Through Procedural Code
Encapsulating data access through modules such as stored procedures and user-defined functions provides an additional layer of protection around your application. However, you can prevent users from directly interacting with database objects by granting permissions only to stored procedures or functions while denying permissions to underlying objects such as tables. Thus, SQL Server achieves this by ownership chaining.
Permission Statements
The three Transact-SQL permission statements are described in the following table.
Here, the GRANT statement can assign permissions to a group or role that can be inherited by database users. However, the DENY statement takes precedence over all other permission statements. Therefore, a user who has been denied a permission cannot inherit it from another role.
Ownership Chains
SQL Server ensures that only principals that have been granted permission can access objects. However, when multiple database objects access each other, the sequence is known as a chain. And, when SQL Server is traversing the links in the chain, it evaluates permissions differently than it would if it were accessing each item separately. Further, when an object is accessed through a chain, SQL Server first compares the object’s owner to the owner of the calling object. And, if both objects have the same owner, permissions on the referenced object are not checked.
Procedural Code and Ownership Chaining
Suppose that a user is granted execute permissions on a stored procedure that selects data from a table. If the stored procedure and the table have the same owner, the user doesn’t need to be granted any permissions on the table and can even be denied permissions. However, if the stored procedure and the table have different owners. Then, SQL Server must check the user’s permissions on the table before allowing access to the data.
Reference: Microsoft Documentation