Securing Database Objects - T-SQL

SQL Server's security mechanism is both elegant and flexible. When SQL Server first came to be, it included a role-based security model where all roles and logins were defined within the database server. Users are similar to logins but defined at the database level. Users could be made members of a role and then permissions for various database objects could be assigned at the individual or role level. This approach met all of the necessary requirements except that it duplicated much of the security assignments managed by the network system. As SQL Server was integrated into the Windows platform, it made sense to integrate the existing Windows security model. Today, you have the option to use either Windows Integrated Security or both Windows and SQL Server security mechanisms to secure database objects. Whether or not you choose to utilize the Windows security integration is up to you, but this option is always enabled.

Typically, it makes sense to use Windows Integrated Security if you have the luxury of managing the network security as well. This is convenient considering you don't have to create duplicate login names and groups. In some situations, it may not be feasible to use integrated security. On a departmental database server, where the server is managed separately from the corporate network, this can be a challenge. Another common exception is the Internet service provider that creates accounts for its customers to manage their databases on a common server. In this scenario, there may be no reason to allow access to any other network resources.

The coverage of this topic focuses on the language rather than the administrative tasks and practices. In brief, SQL Server defines eight fixed server roles that can be used to map various server and database object permissions. Logins defined at the server level may have membership in these roles. Logins can be defined using the SQL Server security model or can map to a user or group in Windows. At the database level, custom roles can be defined that may also be used to grant or deny object permissions. Users are defined at the database level that map to a login at the server level. This may seem a little complicated at first, but it really isn't. The short version is that users, in one form or another, are grouped into roles so that you don't have to assign permissions for every individual user. Ideally, all permissions are assigned to a role with the occasional exception for the user who needs to have special permissions or restrictions.

SQL Server also provides the ability to enforce complex passwords and password expiration on SQL Server logins. SQL Server retrieves the password policy from the local security policy on the server and uses it. If complex passwords are required on the server, then, by default, they will also be required for SQL Server logins. The same goes for password expirations.

Managing Security Objects

SQL Server logins, users, and roles have their own corresponding CREATE and DROP statements. The syntax for creating a new login is slightly different depending on whether the new login is a Windows login or a SQL Server login. This first example shows how to create a SQL Server login:

CREATE USER Paul FOR Login Paul And the following example shows how to create a login for a Windows account:

The syntax for dropping users or logins is simple. Just use the DROP USER or DROP LOGIN statement:

Data Control Language

Three SQL statements are used to control permission to all database objects and securable user resources (that is, users and roles). Each statement accepts the permission type (select, insert, update, delete, execute, and so on), the object name, and the user or role to which the setting applies.

GRANT

To grant permission is to give or allow permission to perform a type of operation on an object. The following are examples:

DENY

The DENY statement is used to explicitly prohibit a user or role members from performing a specific action on an object. Even if a user is a member of a role or is otherwise granted permission, they will not be able to perform the action if they are denied permission explicitly or through any role membership:

REVOKE

This statement is often misunderstood, as the term revoke means to take away. Revoking a permission doesn't necessarily mean that a user loses the ability to perform an action. To revoke permission means to remove the current set of permissions for an object and user or role. This could have the effect of removing an explicit GRANT or DENY, if either exists. This would cause the permission set for a user to revert to those applied through a role membership or to the default permissions.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

T-SQL Topics