Chapter 3
Assigning Access Privileges
IN THIS CHAPTER
Controlling operations with SQL
Identifying users and specifying roles
Categorizing users
Granting and revoking privileges
Granting and revoking roles
Because databases are among the most valuable assets that any organization has, you must be able to control who has access to them, as well as what level of access to grant. SQL handles access management with the third of its main components, the Data Control Language (DCL). Whereas the Data Definition Language (DDL) is used to create and maintain the structure of a database, and the Data Manipulation Language (DML) is used to fill the database structure with data and then operate on that data, the DCL protects the database from unauthorized access and other potential problems.
Working with the SQL Data Control Language
The DCL consists of four SQL statements, and two of them — COMMITROLLBACK — are discussed in Book 4, Chapter 2. The other two DCL statements — GRANT and REVOKE — control who may access various parts of the database. Before you can grant database access to someone, you must have some way of identifying that person. Some parts of user identification, such as issuing passwords and taking other security measures, are implementation-specific. SQL has a standard way of identifying and categorizing users, however, so granting and revoking privileges can be handled relatively easily.
Identifying Authorized Users
Users may be identified individually with a unique identifier, or they may be identified as a member of a group. Individually identified users can be given a customized array of access privileges, whereas all group members receive the same suite of privileges. Groups are defined by the roles that the people in them play. People who all perform the same role have the same access privileges.
Understanding user identifiers
SQL doesn’t specify how a user identifier is assigned. In many cases, the operating system’s login ID serves the purpose. A user identifier is one of two forms of authorization identifier that enable access to a database system. The other form is a role name, which I discuss in the next section.
Every SQL session is started by a user. That user’s user identifier is called the SQL-session user identifier. The privileges associated with the SQL-session user identifier determine what privileges that user has and what actions she may perform during the session. When your SQL session starts, your SQL-session user identifier is also the current user identifier. The identity of the current user is kept in a special value named CURRENT_USER, which can be queried to find out who is currently in charge of a session.
Getting familiar with roles
In a small company, identifying users individually doesn’t present any problem. In a larger organization, however, with hundreds of employees doing a variety of jobs, identifying users individually can become a burden. Every time someone leaves or joins a company or changes job responsibilities, database privileges have to be adjusted. This adjustment is where roles come in.
Although a company may have hundreds or thousands of employees, these employees do a limited number of jobs. If everyone who plays the same role in the company requires the same database access privileges, you can assign those privileges to that group of people based on the roles they play in the organization. One role might be SALES_CLERK. All the sales clerks require the same privileges. All the warehouse workers require different privileges, which is fine, because they play a different role in the company. In this way, the job of maintaining authorizations for everyone is made much simpler. A new sales clerk is added to the SALES_CLERK role name and immediately gains the privileges assigned to that role. A sales clerk leaving the company is deleted from the SALES_CLERK role name and immediately loses all database privileges. An employee changing from one job category to another is deleted from one role name and added to another.
Just as a session initiated by a user is associated with an SQL-session user identifier, it is also associated with an SQL-session role name. The value of the current role name is available in the CURRENT_ROLE special value. When an SQL session is created, the current role name has a null value. At any given instant, either a user identifier is specified, and the associated role name has a null value, or a role name is specified, and the associated user identifier has a null value. A SET ROLE statement can create a situation in which both the user identifier for a session and a role name are non-null. In such a case, the privileges assigned to both the user identifier and to the role name are available to the user.
Creating roles
You can create a role with a single SQL statement. Here is the syntax:
CREATE ROLE <role name>
[WITH ADMIN {CURRENT_USER | CURRENT_ROLE}] ;
When you create a role, the role is automatically granted to you. You are also granted the right to pass the role-creation privilege on to others. When creating a role, you may identify yourself as either the current user or the current role. If you identify yourself as the current user, you’re the only one who can operate on the new role. If you identify yourself as the current role, anyone who shares your current role is also able to operate on the new role.
Destroying roles
The syntax for destroying a role is easy to understand, as follows:
DROP ROLE <role name> ;
Classifying Users
Aside from the fact that users may be members of a group identified as a role, there are four classes of users. Each of these classes has associated privileges that may supersede the privileges accorded to a user by virtue of his role. The four classes are
- Database administrator (DBA): Every database has at least one DBA and possibly multiple DBAs. It’s the responsibility of the DBA to maintain the database, making sure that it’s protected from harm and operating at peak efficiency. DBAs have full rights to all the objects in the database. They can create, modify, or destroy any object in the database, including tables and indexes. They can also decide what privileges other users may have.
- Database object owners: Users who create database objects such as tables and views are automatically the owners of those objects. A database object owner possesses all privileges related to that object. A database object owner’s privileges are equal to those of a DBA, but only with respect to the object in question.
- Grantees: Grantees are users who have been granted selected privileges by a DBA or database object owner. A grantee may or may not be given the right to grant her privileges to others, who thus also become grantees.
- The public: All users are considered to be part of the public, regardless of whether they have been specifically granted any privileges. Thus, privileges granted to PUBLIC may be exercised by any user.
Granting Privileges
The GRANT statement is the tool you use to grant privileges to users. A fairly large number of privileges may be granted, and they may apply to a fairly large number of objects. As a result, the syntax of the GRANT statement is lengthy. Don’t let the length intimidate you! The syntax is very logical and fairly simple when you become familiar with it. Here’s the syntax:
GRANT <privilege list>
ON <privilege object>
TO <user list> [WITH GRANT OPTION]
[GRANTED BY {CURRENT_USER | CURRENT_ROLE}] ;
<privilege list> ::= privilege [ , privilege]…
<privilege> ::=
SELECT [(<column name> [ , <column name>]…)]
| SELECT (<method designator> [ , <method designator]…)
| DELETE
| INSERT [(<column name> [ , <column name>]…)]
| UPDATE [(<column name> [ , <column name>]…)]
| REFERENCES [(<column name> [ , <column name>]…)]
| USAGE
| TRIGGER
| UNDER
| EXECUTE
<privilege object> ::=
[TABLE] <table name>
| <view name>
| DOMAIN <domain name>
| CHARACTER SET <character set name>
| COLLATION <collation name>
| TRANSLATION <translation name>
| TYPE <user-defined type name>
| <specific routine designator>
<user list> ::=
authorizationID [ , authorizationID]…
| PUBLIC
Whew! That’s a lot of syntax. Look at it piece by piece so that it’s a little more comprehensible.
Not all privileges apply to all privilege objects. The SELECT, DELETE, INSERT, UPDATE, and REFERENCES privileges apply to the table privilege object. The SELECT privilege also applies to views. The USAGE privilege applies to the DOMAIN, CHARACTER SET, COLLATION, and TRANSLATION objects. The TRIGGER privilege applies, logically enough, to triggers. The UNDER privilege applies to user-defined types, and the EXECUTE privilege applies to specific routines.
Looking at data
The first privilege in the privilege list is the privilege of looking at a database object. The SELECT statement retrieves data from database tables and views. To enable a user to execute the SELECT statement, issue a GRANT SELECT statement, like this example:
GRANT SELECT
ON CUSTOMER
TO SALES_MANAGER ;
This statement enables the sales manager to query the CUSTOMER table.
Deleting data
In a similar fashion, the GRANT DELETE statement enables a user to delete specified rows from a table, as follows:
GRANT DELETE
ON CUSTOMER
TO SALES_MANAGER ;
This statement enables the sales manager to prune inactive customers from the customer table.
Adding data
With the INSERT statement, you can add a new row of data to a table. The GRANT INSERT statement determines who has the right to perform this operation, as follows:
GRANT INSERT
ON CUSTOMER
TO SALES_MANAGER ;
Now the sales manager can add a new customer record to the CUSTOMER table.
Changing data
You can change the contents of a table row with the UPDATE statement. GRANT UPDATE determines who can do it, as in this example:
GRANT UPDATE
ON RETAIL_PRICE_LIST
TO SALES_MANAGER ;
Now the sales manager can update the retail price list with new pricing information.
Referencing data in another table
You may think that if you can control who does the seeing, creating, modifying, and deleting functions on a table, you’re well protected. Against most threats, you are. A knowledgeable hacker, however, can still break in by using an indirect method.
A correctly designed relational database has referential integrity, which means that the data in one table in the database is consistent with the data in all the other tables. To ensure referential integrity, database designers apply constraints to tables that restrict what someone can enter into the tables. If you have a database with referential-integrity constraints, a user can possibly create a new table that uses a column in your confidential table as a foreign key. Then that column serves as a link through which someone could steal confidential information.
Suppose that you’re a famous Wall Street stock analyst. Many people believe in the accuracy of your stock picks, so whenever you recommend a stock to your subscribers, many people buy that stock, and its price goes up. You keep your analysis in a database that contains a table named FOUR_STAR. Your top recommendations for your next newsletter are in that table. Naturally, you restrict access to FOUR_STAR so that word doesn’t leak out to the investing public before your paying subscribers receive the newsletter.
You’re still vulnerable, however, if anyone other than you can create a new table that uses the stock-name field of FOUR_STAR as a foreign key, as shown in the following command example:
CREATE TABLE HOT_STOCKS (
Stock CHARACTER (4) REFERENCES FOUR_STAR
);
The hacker can try to insert the symbol for every stock on the New York Stock Exchange, American Stock Exchange, and NASDAQ into the table. Those inserts that succeed tell the hacker which stocks match the stocks that you name in your confidential table. It doesn’t take long for the hacker to extract your entire list of stocks.
You can protect yourself from hacks such as the one in the preceding example by being very careful about entering statements similar to the following:
GRANT REFERENCES (Stock)
ON FOUR_STAR
TO SECRET_HACKER;
Your hacker will not have a user identifier of SECRET_HACKER, of course. More likely, it’ll be something like JOHN_SMITH. Beneath that innocent exterior, however, lies a profiteer or agent of a competitor.
Avoid granting privileges to people who may abuse them. True, people don’t come with guarantees printed on their foreheads, but if you wouldn’t lend your new car to a person for a long trip, you probably shouldn’t grant him the REFERENCES privilege on an important table, either.
The preceding example offers one good reason for maintaining careful control of the REFERENCES privilege. Here are two other reasons for carefully controlling REFERENCES, even if the other person is totally innocent:
- If the other person specifies a constraint in
HOT STOCKSby using aRESTRICToption, and you try to delete a row from your table, the database management system (DBMS) tells you that you can’t because doing so violates a referential constraint. - If you want to use the
DROPcommand to destroy your table, you find that you must get the other person to first drop his constraint (or his table).
The bottom line is that enabling another person to specify integrity constraints on your table not only introduces a potential security breach, but also means that the other user sometimes gets in your way.
Using certain database facilities
The USAGE privilege applies to domains and user-defined types (UDTs). I’ve talked about domains before; UDTs are exactly what the name implies, data types that users have defined. I’ll describe them in a minute. To use or even see a domain or UDT, a user must have the USAGE privilege for that domain or UDT. Suppose that Major League Baseball has a domain named MLBTEAMS that consists of the names of all the Major League Baseball teams. A user holding the role of team owner could be granted use of that domain, as follows:
GRANT USAGE
ON MLBTEAMS
TO TEAM_OWNER ;
Responding to an event
You can grant a user or a role the privilege of creating a trigger that fires when a specified change takes place to a table, such as the renaming of a Major League Baseball team, as in this example:
GRANT TRIGGER
ON MLBTEAMS
TO TEAM_OWNER ;
Defining new data types
One advanced feature that was added to SQL in the SQL:1999 version enables users to create structured user-defined types. Naturally, the creator of a UDT has all privileges attached to that UDT. Among those privileges is the USAGE privilege, which allows the type to be used to define columns, routines, and other schema objects. Also included is the UNDER privilege, which permits subtypes of the type to be defined, as follows:
GRANT UNDER
ON MLBTEAMS
TO LEAGUE_VICE_PRESIDENT ;
Executing an SQL statement
The EXECUTE privilege enables the grantee to use SQL-invoked routines. By restricting the ability to invoke routines, you keep those routines in the hands of those who are authorized to run them, as in this example:
GRANT EXECUTE
ON PRICECHANGE
TO SALES_MANAGER ;
Doing it all
For a highly trusted person who has just been given major responsibility, instead of issuing a whole series of GRANT statements, you can take care of everything with just one statement, GRANT ALL. Here’s an example:
GRANT ALL PRIVILEGES
ON MLBTEAMS
TO LEAGUE_VICE_PRESIDENT ;
GRANT ALL PRIVILEGES is a pretty dangerous statement, however. In the wrong hands, it could cause a lot of damage. For this reason, SQL Server 2005 deprecated this syntax. Although it’s still supported in SQL Server 2008 R2, in SQL Server 2017 you get a message that it is deprecated when you try to use it.
Passing on the power
To keep your system secure, you must severely restrict the access privileges you grant and the people to whom you grant these privileges. People who can’t do their work because they lack access, however, are likely to hassle you. To preserve your sanity, you probably need to delegate some of the responsibility for maintaining database security.
SQL provides for such delegation through the WITH GRANT OPTION clause. Consider the following example:
GRANT UPDATE
ON RETAIL_PRICE_LIST
TO SALES_MANAGER WITH GRANT OPTION ;
This statement is similar to the GRANT UPDATE example (refer to “Changing data,” earlier in this chapter) in that the statement enables the sales manager to update the retail price list. The statement also gives her the right to grant the update privilege to anyone she wants. If you use this form of the GRANT statement, you must not only trust the grantee to use the privilege wisely, but also trust her to choose wisely in granting the privilege to others.
Revoking Privileges
If it’s possible to grant database privileges to users and roles, it had better be possible to revoke those privileges, too. Things change. People’s jobs change, and their need for data changes. Sometimes, people leave the company and go to work for a competitor. You certainly want to revoke privileges in a case like that.
The syntax for revoking privileges is similar to the GRANT syntax, as follows:
REVOKE [GRANT OPTION FOR] <privilege list>
ON <privilege object>
FROM <user list>
[GRANTED BY {CURRENT_USER | CURRENT_ROLE}]
{RESTRICT | CASCADE} ;
The privilege list, privilege object, and user list are the same as they are for GRANT. The major difference from the GRANT syntax is the addition of the RESTRICT and CASCADE keywords. Note that {RESTRICT | CASCADE} isn’t enclosed in square brackets, meaning that it isn’t optional. One of the two keywords is required in any REVOKE statement.
In SQL Server’s T-SQL, the CASCADE keyword is optional, and the RESTRICT sense is assumed if CASCADE is not present.
If a REVOKE statement includes the RESTRICT keyword, the DBMS checks to see whether the privilege being revoked was passed on to one or more other users. If it was, the privilege isn’t revoked, and you receive an error message instead. If a REVOKE statement includes the CASCADE keyword, the DBMS revokes the privilege, as well as any dependent instances of this privilege that were granted by the instance you’re revoking.
With the optional GRANT OPTION FOR clause, you can revoke a user’s ability to grant a privilege without revoking his ability to use the privilege himself. If you specify GRANT OPTION FOR along with CASCADE, not only is the grant option taken away, but also, everyone who obtained the privilege through that grant loses the privilege. If you specify GRANT OPTION FOR along with RESTRICT, and anyone was granted the privilege under consideration, you get an error message, and the grant option isn’t revoked.
If the optional GRANTED BY clause is present, only those privileges granted by the current user or current role (whichever is specified) are revoked.
If none of the privileges you’re trying to revoke exists, you get an error message, and nothing changes. If some of the privileges you’re trying to revoke exist, but others don’t, you get a warning.
Revoking a user’s privileges may not remove those privileges from the user. If you granted the SELECT privilege to Alice WITH GRANT OPTION, and Alice granted the privilege to Bob, Bob has the SELECT privilege. If you later grant the SELECT privilege to Bob, now he has that privilege from two sources. If you revoke the SELECT privilege from Bob, he still has SELECT access to the table in question because of the GRANT SELECT he received from Alice. This situation complicates revocation. If you want to truly be sure that a person no longer has access to a resource, you must make sure that all grants have been revoked.
Granting Roles
Just as you can grant a privilege to a user, you can grant a role to a user. Granting a role is a more significant action: When you grant a role to a person, you’re granting all the privileges that go along with that role in one action. Here’s the syntax:
GRANT <role name> [{ , <role name>}…]
TO <user list>
[WITH ADMIN OPTION]
[GRANTED BY {CURRENT_USER | CURRENT_ROLE}] ;
As you can see from the syntax, you can grant any number of roles to the names in a list of users with a single GRANT statement. The optional WITH ADMIN OPTION clause is similar to the WITH GRANT OPTION clause that may be a part of a grant of privileges. If you want to grant a role and extend to the grantee the right to grant the same role to others, you do so with the WITH ADMIN OPTION clause. The optional GRANTED BY clause specifies whether you want to record that this GRANT was granted by the current user or by the current role. This distinction may become meaningful when the time comes to revoke the role granted here.
Revoking Roles
The command for revoking a role is very similar to the command for revoking a privilege. Here’s what it looks like:
REVOKE [ADMIN OPTION FOR] <role name> [{ , <role name>}…]
FROM <user list>
[GRANTED BY {CURRENT_USER | CURRENT_ROLE}]
{RESTRICT | CASCADE}
Here, you revoke one or more roles from the users in the user list. You can revoke the admin option from a role without revoking the role itself.
The GRANTED BY clause requires a little explanation. If a role was specified as being granted by the current user, revoking it with a GRANTED BY CURRENT_USER clause works, but revoking it with GRANTED BY CURRENT_ROLE clause doesn’t. The RESTRICT or CASCADE keywords apply only if the admin option has been used to grant the specified role to other users or roles. If RESTRICT is specified, and this role or list of roles has been granted to a subgrantee, an error message is returned, and the revocation doesn’t take effect. If CASCADE is specified, and this role or list of roles has been granted to a subgrantee, the role and all the subgrantee roles are revoked.