Access Controls for Users and Roles in SQL

User- and role-level security helps to protect your data against error or theft

A web developer

 OstapenkoOlena / Getty Images

All relational database management systems provide some sort of intrinsic security mechanisms designed to minimize the threats of data loss, data corruption, or data theft. 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. Some security mechanisms are common to all databases that implement the ​Structured Query Language.

User-Level Security

Server-based databases 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 similar.

Create individual database user accounts for each person with access to your database.

Avoid provisioning generic accounts accessible by several different people. First, this practice eliminates 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. Second, if a specific user leaves your organization and you wish to remove his or her access from the database, you must 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 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.

Role-Level Security

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 be overwhelmed by maintaining accounts and proper permissions. To ease this burden, relational databases support 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, you could create a DBA role and then add the user accounts of your administrative staff to this role. After that, you 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 vary 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 <object>]
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 <object>, is used to specify the affected table for table- or view-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 cannot be specified when the permissions are assigned to a role.

Example Database Grants

Let's look at a few examples. In our first scenario, we have recently hired a group of 42 data entry operators who will add and maintain customer records. They must 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

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

SQL includes 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

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