Access Controls for Users and Roles in SQL

Security is paramount to database administrators seeking to protect their gigabytes of vital business data from the prying eyes of unauthorized outsiders and insiders attempting to exceed their authority. All relational database management systems provide some sort of intrinsic security mechanisms designed to minimize these threats. They range from the simple password protection offered by Microsoft Access to the complex user/role structure supported by advanced relational databases like Oracle and ​Microsoft SQL Server.

This article focuses on the security mechanisms common to all databases that implement the ​Structured Query Language (or SQL). Together, we'll walk through the process of strengthening data access controls and ensuring the safety of your data.

Users

Server-based databases all support a user concept similar to that used in computer operating systems. If you're familiar with the user/group hierarchy found in Microsoft Windows NT and Windows 2000, you'll find that the user/role groupings supported by SQL Server and Oracle are very similar.

It is highly recommended that you create individual database user accounts for each person who will be accessing your database. It's technically possible to share accounts between users or simply use one user account for each type of user that needs to access your database, but I strongly discourage this practice for two reasons. First, it will eliminate individual accountability—if a user makes a change to your database (let's say by giving himself a $5,000 raise), you won't be able to trace it back to a specific person through the use of audit logs.

Furthermore, if a specific user leaves your organization and you wish to remove his or her access from the database, you'll be forced to change the password that all users rely upon.

The methods for creating user accounts vary from platform to platform and you'll have to consult your DBMS-specific documentation for the exact procedure.

Microsoft SQL Server users should investigate the use of the sp_adduser stored procedure. Oracle database administrators will find the CREATE USER command useful. You also might want to investigate alternative authentication schemes. For example, Microsoft SQL Server supports the use of Windows NT Integrated Security. Under this scheme, users are identified to the database by their Windows NT user accounts and are not required to enter an additional user ID and password to access the database. This approach is extremely popular among database administrators because it shifts the burden of account management to the network administration staff and it provides the ease of a single sign-on to the end user.

Roles

If you're in an environment with a small number of users, you'll probably find that creating user accounts and assigning permissions directly to them is sufficient for your needs. However, if you have a large number of users, you'll most likely be overwhelmed by the burden of maintaining accounts and proper permissions. To ease this burden, relational databases support the notion of roles. Database roles function similarly to Windows NT groups. User accounts are assigned to role(s) and permissions are then assigned to the role as a whole rather than the individual user accounts.

For example, we could create a DBA role and then add the user accounts of our administrative staff to this role. Once we've done this, we can assign a specific permission to all present (and future) administrators by simply assigning the permission to the role. Once again, the procedures for creating roles varies from platform to platform. MS SQL Server administrators should investigate the sp_addrole stored procedure while Oracle DBAs should use the CREATE ROLE syntax.

Granting Permissions

Now that we've added users to our database, it's time to begin strengthening security by adding permissions. Our first step will be to grant appropriate database permissions to our users. We'll accomplish this through the use of the SQL GRANT statement.

Here's the syntax of the statement:

GRANT <permissions>
[ON <table>]
TO <user/role>
[WITH GRANT OPTION]

Now, let's take a look at this statement line-by-line. The first line, GRANT <permissions>, allows us to specify the specific table permissions we are granting. These can be either table-level permissions (such as SELECT, INSERT, UPDATE and DELETE) or database permissions (such as CREATE TABLE, ALTER DATABASE and GRANT). More than one permission can be granted in a single GRANT statement, but table-level permissions and database-level permissions may not be combined in a single statement.

The second line, ON <table>, is used to specify the affected table for table-level permissions. This line is omitted if we are granting database-level permissions. The third line specifies the user or role that is being granted permissions.

Finally, the fourth line, WITH GRANT OPTION, is optional. If this line is included in the statement, the user affected is also permitted to grant these same permissions to other users. Note that the WITH GRANT OPTION can not be specified when the permissions are assigned to a role.

Examples

Let's look at a few examples. In our first scenario, we have recently hired a group of 42 data entry operators who will be adding and maintaining customer records. They need to be able to access information in the Customers table, modify this information and add new records to the table. They should not be able to entirely delete a record from the database. First, we should create user accounts for each operator and then add them all to a new role, DataEntry. Next, we should use the following SQL statement to grant them the appropriate permissions:

GRANT SELECT, INSERT, UPDATE
ON Customers
TO DataEntry

And that's all there is to it! Now let's examine a case where we're assigning database-level permissions. We want to allow members of the DBA role to add new tables to our database. Furthermore, we want them to be able to grant other users permission to do the same. Here's the SQL statement:

GRANT CREATE TABLE
TO DBA
WITH GRANT OPTION

Notice that we've included the WITH GRANT OPTION line to ensure that our DBAs can assign this permission to other users.

Removing Permissions

Once we've granted permissions, it often proves necessary to revoke them at a later date. Fortunately, SQL provides us with the REVOKE command to remove previously granted permissions. Here's the syntax:

REVOKE [GRANT OPTION FOR] <permissions>
ON <table>
FROM <user/role>

You'll notice that the syntax of this command is similar to that of the GRANT command. The only difference is that WITH GRANT OPTION is specified on the REVOKE command line rather than at the end of the command. As an example, let's imagine we want to revoke Mary's previously granted permission to remove records from the Customers database. We'd use the following command:

REVOKE DELETE
ON Customers
FROM Mary

And that's all there is to it! There's one additional mechanism supported by Microsoft SQL Server that is worth mentioning—the DENY command. This command can be used to explicitly deny a permission to a user that they might otherwise have through a current or future role membership. Here's the syntax:

DENY <permissions>
ON <table>
TO <user/role

Examples

Returning to our previous example, let's imagine that Mary was also a member of the Managers role that also had access to the Customers table. The previous REVOKE statement would not be sufficient to deny her access to the table. It would remove the permission granted to her through a GRANT statement targeting her user account, but would not affect the permissions gained through her membership in the Managers role. However, if we use a DENY statement it will block her inheritance of the permission. Here's the command:

DENY DELETE
ON Customers
TO Mary

The DENY command essentially creates a "negative permission" in the database access controls. If we later decide to give Mary permission to remove rows from the Customers table, we can't simply use the GRANT command. That command would be immediately overridden by the existing DENY. Instead, we would first use the REVOKE command to remove the negative permission entry as follows:

REVOKE DELETE
ON Customers
FROM Mary

You'll notice that this command is exactly the same as the one used to remove a positive permission. Remember that the DENY and GRANT commands both work in a similar fashion*mdash;they both create permissions (positive or negative) in the database access control mechanism. The REVOKE command removes all positive and negative permissions for the specified user. Once this command has been issued, Mary will be able to delete rows from the table if she is a member of a role that possesses that permission. Alternatively, a GRANT command could be issued to provide the DELETE permission directly to her account.

Throughout the course of this article, you've learned a good deal about the access control mechanisms supported by the Standard Query Language. This introduction should provide you with a good starting point, but I encourage you to reference your DBMS documentation to learn the enhanced security measures supported by your system. You'll find that many databases support more advanced access control mechanisms, such as granting permissions on specific columns.