In this chapter, we describe the features that SQL offers for protecting data in the tables against deliberate or accidental unauthorized use. For the security of data, SQL users, passwords, and privileges are required.

SQL users must be known to SQL before they can access the database data. In Chapter 3, “Installing the Software,” we showed you how one user is created automatically during SQL installation. In Chapter 4, “SQL in a Nutshell,” we showed how a new SQL user called BOOKSQL was introduced. Logging on to SQL without an existing user name is just not possible.

To each SQL user, a password can be assigned. When a password is required, accessing the database data becomes even more difficult because the name of an SQL user is no longer sufficient. After following the procedure to install the sample database as described in this book, the user BOOKSQL has the password BOOKSQLPW. You have probably entered this password many times, and you already have discovered what happens if you make a typing error: no access!

New SQL users are not allowed to access tables belonging to other SQL users, even with the SELECT statement. Nor can they create their own tables. New SQL users must explicitly be granted privileges. We can indicate, for example, that an SQL user is allowed to query a certain table or change specific columns of a table. Another SQL user might be allowed to create tables, and the third to create and remove complete databases.

The privileges that can be granted are divided into four groups:

In this chapter, we explain how new SQL users can be entered and how privileges can be assigned with the GRANT statement. All privileges are stored in the catalog, of course. We also describe how privileges can be recalled with the REVOKE statement and how SQL users can be removed from the catalog.

Besides BOOKSQL, we can add other users. In Section 4.13, in Chapter 4, we used an example to show how a new user can be added. In this section, we explain it in more detail.

To add new users in the catalog, SQL uses the simple CREATE USER statement.

<create user statement> ::=
   CREATE USER <user name> IDENTIFIED BY <password>

<user name> ;
<password>  ::= <name>

In a CREATE USER statement, a username and a password are entered. In most SQL products, the username is just a name consisting of letters and numbers. The password is a simple name as well.

CREATE USER CHRIS IDENTIFIED BY CHRISSEC

CREATE USER PAUL IDENTIFIED BY LUAP

<user name> ::=
   <name> | '<name>' | '<name>'@'<host name>'

<password> ::= <alphanumeric literal>

CREATE USER 'CHRIS'@'%' IDENTIFIED BY 'CHRISSEC'

CREATE USER 'SAM'@'TEST' IDENTIFIED BY 'CHRISSEC'

<alter user statement> ::=
   ALTER USER <user name> IDENTIFIED BY <password>

ALTER USER JIM IDENTIFIED BY JIM1

<drop user statement> ::=
   DROP USER <user name>

DROP USER JIM

SQL supports the following table privileges.

A table privilege may be granted only by users who have enough privileges themselves.

<grant statement> ::=
   <grant table privilege statement>

<grant table privilege statement> ::=
   GRANT <table privileges>
   ON    <table specification>
   TO    <grantees>
   [ WITH GRANT OPTION ]

<table privileges> ::=
   ALL [ PRIVILEGES ] |
   <table privilege> [ { , <table privilege> }... ]

<table privilege> ::=
   SELECT                       |
   INSERT                       |
   DELETE                       |
   UPDATE [ <column list> ]     |
   REFERENCES [ <column list> ] |
   ALTER                        |
   INDEX

<column list> ::=
   ( <column name> [ { , <column name> }... ] )

<grantees> ::=
   PUBLIC |
   <user name> [ { , <user name> }... ]

Here are a few examples of how table privileges must be granted. We assume, unless otherwise mentioned, that the user called BOOKSQL enters the statements.

GRANT   SELECT
ON      PLAYERS
TO      JAMIE

Explanation. After this GRANT statement has been processed, JAMIE may use any SELECT statement to query the PLAYERS table, regardless of who has created the table.

Multiple table privileges can be granted to multiple users simultaneously.

GRANT   INSERT, UPDATE
ON      TEAMS
TO      JAMIE, PIET

GRANT   SELECT, INSERT
ON      PENALTIES
TO      PUBLIC

GRANT   UPDATE (PLAYERNO, DIVISION)
ON      TEAMS
TO      PETE

Table privileges are effective for a certain table. SQL also supports privileges for an entire database, such as the privilege to create tables or views in a certain database.

SQL supports the following database privileges:

The definition of this GRANT statement resembles the one for granting table privileges. However, there are two important differences. First, the list with privileges is longer and the ON clause looks different.

<grant statement> ::=
   <grant database privilege statement>

<grant database privilege statement> ::=
   GRANT <database privileges>
   ON    [ <database name> . ] *
   TO    <grantees>
   [ WITH GRANT OPTION ]

<database privileges> ::=
   ALL [ PRIVILEGES ] |
   <database privilege> [ { , <database privilege>
 }... ]

<database privilege> ::=
   SELECT                  |
   INSERT                  |
   DELETE                  |
   UPDATE                  |
   REFERENCES              |
   CREATE                  |
   ALTER                   |
   DROP                    |
   INDEX                   |
   CREATE TEMPORARY TABLES |
   CREATE VIEW             |
   CREATE ROUTINE          |
   ALTER ROUTINE           |
   EXECUTE ROUTINE         |
   LOCK TABLES

GRANT   SELECT
ON      TENNIS.*
TO      PETE

Explanation: So, this privilege is effective for all existing tables and also the tables that will be added to the TENNIS database later.

GRANT   CREATE, ALTER, DROP, CREATE VIEW
ON      TENNIS.*
TO      JIM

GRANT   SELECT
ON      INFORMATION_SCHEMA.*
TO      PETE

GRANT   SELECT, INSERT
ON      *
TO      ALYSSA

Explanation: The asterisk represents the current database here.

The most effective privileges are the user privileges. For all statements for which database privileges need to be granted, user privileges can be defined as well. For example, by granting someone the privilege CREATE on the user level, this user can create tables in all databases (instead of in one specific database). In addition, SQL supports the following additional user privileges:

<grant statement> ::=
   <grant user privilege statement>

<grant user privilege statement> ::=
   GRANT <user privileges>
   ON    *.*
   TO    <grantees>
   [ WITH GRANT OPTION ]

<user privileges> ::=
   ALL [ PRIVILEGES ] |
   <user privilege> [ { , <user privilege> }... ]

<user privilege> ::=
   SELECT                  |
   INSERT                  |
   DELETE                  |
   UPDATE                  |
   REFERENCES              |
   CREATE                  |
   ALTER                   |
   DROP                    |
   INDEX                   |
   CREATE TEMPORARY TABLES |
   CREATE VIEW             |
   CREATE ROUTINE          |
   ALTER ROUTINE           |
   EXECUTE ROUTINE         |
   LOCK TABLES             |
   CREATE USER

GRANT   CREATE, ALTER, DROP
ON      *.*
TO      MAX

Explanation: So, these privileges apply to all existing databases and also to all future databases.

GRANT   CREATE USER
ON      *.*
TO      ALYSSA

GRANT   ALL PRIVILEGES
ON      *.*
TO      ROOT

By way of conclusion, Table 23.1 lists the levels at which privileges for certain SQL statements can be granted.

A GRANT statement can be concluded with the WITH GRANT OPTION. This means that all users specified in the TO clause can themselves pass on the privilege (or part of the privilege) to other users. In other words, if a user is given a table privilege via the WITH GRANT OPTION, he or she can grant that privilege on the table without being the owner of it.

GRANT   REFERENCES
ON      TEAMS
TO      JOHN
WITH    GRANT OPTION

GRANT   REFERENCES
ON      TEAMS
TO      PETE

JIM can himself extend the statement with WITH GRANT OPTION so that PETE, in turn, can pass on the privilege.

Granting privileges to individual users is acceptable if there are not that many. But imagine that the database consists of 300 tables and has 500 users. If everyone is to be given privileges, at least 500 GRANT statements are required. However, it is likely that many more statements are necessary, and this is very difficult to manage. That is why the concept of role has been added to SQL.

A role is a defined set of privileges (the same privileges we have already described) that is granted to users. If the privileges of one role are altered (a table privilege is added, for example), the privileges of all users belonging to that role are changed automatically. It is easier to manage the privileges this way. A user may have several roles.

With the CREATE ROLE statement, new roles can be created.

<create role statement> ::=
   CREATE ROLE <role name>

To assign privileges to roles, the definition of the concept grantees has been extended so that all kinds of privileges can be assigned to roles.

<grantees> ::=
   PUBLIC                               |
   <user name> [ { , <user name> }... ] |
   <role name> [ { , <role name> }... ]

A special version of the GRANT statement has also been created to assign roles to users.

<grant statement> ::=
   <grant role statement>

<grant role statement> ::=
   GRANT <role name> [ { , <role name> }... ]
   TO <grantees>

CREATE ROLE SALES

GRANT   SELECT, INSERT
ON      PENALTIES
TO      SALES

GRANT SALES TO ILENE, KELLY, JIM, MARC

Explanation: The first statement creates the new role. With the GRANT statement, table privileges are granted. The structure of this statement is the same as the one used for granting privileges to users. Next, with a special version of the GRANT statement, we give the role to the four users. It is now possible to extend the privileges of the SALES role with one statement instead of using an entire set of GRANT statements.

Roles can be removed with the DROP ROLE statement. And, of course, all privileges belonging to that role also are removed; in turn, the users lose their privileges.

DROP ROLE SALES

Several catalog tables are used to record users, roles, and privileges:

Contrary to many other SQL products, SQL does not remember who granted a privilege. When SQL has approved and processed a granted privilege, it does not record who granted the privilege. In the various catalog views, the column GRANTOR (the grantor of the privilege) has been included but has the value UNKNOWN everywhere. The GRANTEE (the person who receives the privilege), however, has been filled everywhere.

The USERS table contains only one column, the name of the user. This column also forms the primary key of this table.

The ROLES table also consists of only one column, the name of the role.

The USER_ROLES table has the following structure. (The columns USER_NAME and ROLE_NAME form the primary key of this table.)

The column privileges are recorded in a separate catalog table, the COLUMN_AUTHS table. The primary key of this table is formed by the columns GRANTOR, TABLE_NAME, GRANTEE, and COLUMN_NAME. The table has the following structure:

The TABLE_AUTHS table has the following structure. The primary key of this table is formed by the columns GRANTOR, GRANTEE, TABLE_CREATOR, TABLE_NAME, and PRIVILEGE. You can see that the column privileges are not recorded in this table.

The DATABASE_AUTHS table has the following structure. The primary key of this table is formed by the columns GRANTOR, GRANTEE, DATABASE_NAME, and PRIVILEGE.

The USER_AUTHS table has the following structure. The primary key of this table is formed by the columns GRANTOR, GRANTEE, and PRIVILEGE.

SELECT   GRANTEE
FROM     USER_AUTHS
WHERE    PRIVILEGE = 'SELECT'
UNION
SELECT   GRANTEE
FROM     DATABASE_AUTHS
WHERE    DATABASENAME = 'TENNIS'
AND      PRIVILEGE = 'SELECT'
UNION
SELECT   GRANTEE
FROM     TABLE_AUTHS
WHERE    TABLE_CREATOR = 'TENNIS'
AND      PRIVILEGE = 'SELECT'

Explanation. This example requires a search in three tables because SELECT privileges can be defined on three levels.

GRANT   SELECT
ON      PLAYERS
TO      PUBLIC

GRANT   INSERT
ON      PLAYERS
TO      RUDY
WITH    GRANT OPTION

GRANT   INSERT
ON      PLAYERS
TO      REGINA

GRANT   INSERT
ON      PLAYERS
TO      SUSAN
WITH    GRANT OPTION

GRANT   INSERT
ON      PLAYERS
TO      REGINA

The REVOKE statement withdraws privileges from a user without deleting that user from the USERS table. This statement has the opposite effect of the GRANT statement.

<revoke statement> ::=
   <revoke table privilege statement>    |
   <revoke database privilege statement> |
   <revoke user privilege statement>     |
   <revoke role statement>

<revoke table privilege statement> ::=
   REVOKE <table privileges>
   ON     <table specification>
   FROM   <grantees>

<table privileges> ::=
   ALL [ PRIVILEGES ] |
   <table privilege> [ { , <table privilege> }... ]

<table privilege> ::=
   SELECT                       |
   INSERT                       |
   DELETE                       |
   UPDATE [ <column list> ]     |
   REFERENCES [ <column list> ] |
   ALTER                        |
   INDEX

<revoke database privilege statement> ::=
   REVOKE  <database privileges>
   ON      [ <database name> . ] *
   FROM    <user name> [ { , <user name> }... ]

<database privileges> ::=
   ALL [ PRIVILEGES ] |
   <database privilege> [ { , <database privilege>
 }... ]

<database privilege> ::=
   SELECT                  |
   INSERT                  |
   DELETE                  |
   UPDATE                  |
   REFERENCES              |
   CREATE                  |
   ALTER                   |
   DROP                    |
   INDEX                   |
   CREATE TEMPORARY TABLES |
   CREATE VIEW             |
   CREATE ROUTINE          |
   ALTER ROUTINE           |
   EXECUTE ROUTINE         |
   LOCK TABLES

<revoke user privilege statement> ::=
   REVOKE  <user privileges>
   ON      *.*
   FROM    <user name> [ { , <user name> }... ]

<user privileges> ::=
   ALL [ PRIVILEGES ] |
   <user privilege> [ { , <user privilege> }... ]

<user privilege> ::=
   SELECT                  |
   INSERT                  |
   DELETE                  |
   UPDATE                  |
   REFERENCES              |
   CREATE                  |
   ALTER                   |
   DROP                    |
   INDEX                   |
   CREATE TEMPORARY TABLES |
   CREATE VIEW             |
   CREATE ROUTINE          |
   ALTER ROUTINE           |
   EXECUTE ROUTINE         |
   LOCK TABLES             |
   CREATE USER

<column list> ::=
   ( <column name> [ { , <column name> }... ]

<revoke role statement> ::=
   REVOKE <role name> [ { , <role name> }... ]
   FROM   <grantees>

<grantees> ::=
   PUBLIC |
   <user name> [ { , <user name> }... ]

REVOKE   SELECT
ON       PLAYERS
FROM     JIM

REVOKE   REFERENCES
ON       TEAMS
FROM     JIM

REVOKE SALES FROM ILENE

REVOKE   SELECT
ON       PENALTIES
FROM     SALES

It could be that a user has been granted overlapping privileges. He received, for example, the table privilege UPDATE on the PLAYERS table and also the user privilege UPDATE for all tables in all databases. If one of the two is withdrawn, the other privilege remains.

A GRANT statement can refer not only to tables, but also to views. (See the definition of the GRANT statement in Section 23.3.) Let’s look at this more closely.

Because privileges can also be granted for views, you can provide users with access to only a part of a table or only to information derived or summarized from tables. The following are examples of both features.

CREATE USER DIANE IDENTIFIED BY 'SECRET'

CREATE   VIEW NAME_ADDRESS AS
SELECT   NAME, INITIALS, STREET, HOUSENO,
         TOWN
FROM     PLAYERS
WHERE    LEAGUENO IS NULL

GRANT   SELECT
ON      NAME_ADDRESS
TO      DIANE

CREATE USER GERARD IDENTIFIED BY 'XYZ1234'

CREATE   VIEW RESIDENTS (TOWN, NUMBER_OF) AS
SELECT   TOWN, COUNT(*)
FROM     PLAYERS
GROUP BY TOWN

GRANT   SELECT
ON      RESIDENTS
TO      GERARD

All types of table privilege can be granted on views.