Implementing a security scheme and enforcing security restrictions are the responsibility of the DBMS software. The SQL language defines an overall framework for database security, and SQL statements are used to specify security restrictions. The SQL security scheme is based on three central concepts:
- Users. The actors in the database. Each time the DBMS retrieves, inserts, deletes, or updates data, it does so on behalf of some user. The DBMS permits or prohibits the action depending on which user is making the request.
- Database objects. The items to which SQL security protection can be applied. Security is usually applied to tables and views, but other objects such as forms, application programs, and entire databases can also be protected. Most users will have permission to use certain database objects but will be prohibited from using others.
- Privileges.The actions that a user is permitted to carry out for a given database object. A user may have permission to SELECT and INSERT rows in a certain table, for example, but may lack permission to DELETE or UPDATE rows of the table. A different user may have a different set of privileges.
Figure 15-1 shows how these security concepts might be used in a security scheme for the sample database.
To establish a security scheme for a database, you use the SQL GRANT statement to specify which users have which privileges on which database objects. For example, here is a GRANT statement that lets Sam Clark retrieve and insert data in the OFFICES table of the sample database:
Let Sam Clark retrieve and insert data in the OFFICES table.
GRANT SELECT, INSERT
ON OFFICES
TO SAM
The GRANT statement specifies a combination of a user-id (SAM), an object (the OFFICES table), and privileges (SELECT and INSERT). Once granted, the privileges can be rescinded later with this REVOKE statement:
Take away the privileges granted earlier to Sam Clark.
REVOKE SELECT, INSERT
ON OFFICES
FROM SAM
The GRANT and REVOKE statements are described in detail later in this chapter, in the sections “Granting Privileges” and “Revoking Privileges.”
1. User-Ids
Each user of a SQL-based database is typically assigned a user-id, a short name that identifies the user to the DBMS software. The user-id is at the heart of SQL security. Every SQL statement executed by the DBMS is carried out on behalf of a specific user-id. The user-id determines whether the statement will be permitted or prohibited by the DBMS. In a production database, user-ids are assigned by the database administrator.
A personal computer database may have only a single user-id, identifying the user who created and who owns the database. In special-purpose databases (for example, those designed to be embedded within an application or a special-purpose system), there may be no need for the additional overhead associated with SQL security. These databases typically operate as if there were a single user-id.
In practice, the restrictions on the names that can be chosen as user-ids vary from implementation to implementation. The SQL1 standard permitted user-ids of up to 18 characters and required them to be valid SQL names. In some mainframe DBMS systems, user-ids may have no more than eight characters. In Sybase and SQL Server, user-ids may have up to 30 characters. If portability is a concern, it’s best to limit user-ids to eight or fewer characters. Figure 15-2 shows various users who need access to the sample database and typical user-ids assigned to them. Note that all of the users in the orderprocessing department can be assigned the same user-id because they are to have identical privileges in the database.
The ANSI/ISO SQL standard uses the term authorization-id instead of user-id, and you will occasionally find this term used in other SQL documentation. Technically, authorization-id is a more accurate term because the role of the ID is to determine authorization or privileges in the database. There are situations, as in Figure 15-2, where it makes sense to assign the same user-id to different users. In other situations, a single person may use two or three different user-ids. In a production database, authorization- ids may be associated with programs and groups of programs, rather than with human users. In each of these situations, authorization-id is a more precise and less confusing term than user-id. However, the most common practice is to assign a different user-id to each person, and most SQL-based DBMS use the term user-id in their documentation.
2. User Authentication
The SQL standard specifies that user-ids provide database security; however, the specific mechanism for associating a user-id with a SQL statement is outside the scope of the standard because a database can be accessed in many different ways. For example, when you type SQL statements into an interactive SQL utility, how does the DBMS determine which user-id is associated with the statements? If you use a forms-based data entry or query program, how does the DBMS determine your user-id? On a database server, a report-generating program might be scheduled to run at a preset time every evening; what is the user-id in this situation, where there is no human user? Finally, how are user-ids handled when you access a database across a network, where your user-id on the system where you are actively working might be different than the user-id established on the system where the database resides?
Most commercial SQL implementations establish a user-id for each database session. In interactive SQL, the session begins when you start the interactive SQL program, and it lasts until you exit the program. In an application program using programmatic SQL, the session begins when the application program connects to the DBMS, and it ends when the application program terminates. All of the SQL statements used during the session are associated with the user-id specified for the session.
Usually, you must supply both a user-id and an associated password at the beginning of a session. The DBMS checks the password to verify that you are, in fact, authorized to use the user-id that you supply. Although user-ids and passwords are common across most SQL products, the specific techniques used to specify the user-id and password vary from one product to another.
Some DBMS brands, especially those that are available on many different operating system platforms, implement their own user-id/password security. For example, when you use Oracle’s interactive SQL program, called SQLPLUS, you specify a user name and associated password in the command that starts the program, like this:
SQLPLUS SCOTT/TIGER
The Sybase interactive SQL program, called ISQL, also accepts a user name and password, using this command format:
ISQL /USER=SCOTT /PASSWORD=TIGER
In each case, the DBMS validates the user-id (SCOTT) and the password (TIGER) before beginning the interactive SQL session.
Many other DBMS brands, including Ingres and Informix, use the user names of the host computer’s operating system as database user-ids. For example, when you log in to a UNIX-based computer system, you must supply a valid UNIX user name and password to gain access. To start the Ingres interactive SQL utility, you simply give the command:
ISQL SALESDB
where SALESDB is the name of the Ingres database you want to use. Ingres automatically obtains your UNIX user name and makes it your Ingres user-id for the session. Thus, you don’t have to specify a separate database user-id and password. DB2’s interactive SQL, running under MVS/TSO, uses a similar technique. Your TSO login name automatically becomes your DB2 user-id for the interactive SQL session.
SQL security also applies to programmatic access to a database, so the DBMS must determine and authenticate the user-id for every application program that tries to access the database. Again, the techniques and rules for establishing the user-id vary from one brand of DBMS to another. For widely used utility programs, such as a data entry or an inquiry program, it is common for the program to ask the user for a user-id and password at the beginning of the session, via a screen dialog. For more specialized or custom-written programs, the appropriate user-id may be obvious from the application to be performed and hard-wired into the program.
The SQL2 standard also allows a program to use an authorization-id associated with a specific set of SQL statements (called a module), rather than the user-id of the particular person running the program. With this mechanism, a program may be given the ability to perform very specific operations on a database on behalf of many different users, even if those users are not otherwise authorized to access the target data. This is a convenient capability that is finding its way into mainstream SQL implementations. The specifics of SQL security for database access programs are described in Chapter 17, which covers programmatic SQL.
3. User Groups
A large production database often has groups of users with similar needs. In the sample database, for example, the three people in the order-processing department form a natural user group, and the two people in the accounts receivable department
form another natural group. Within each group, all of the users have identical needs for data access and should have identical privileges.
Under the ANSI/ISO SQL security scheme, you can handle groups of users with similar needs in one of two ways:
- You can assign the same user-id to every person in the group, as shown in Figure 15-2. This scheme simplifies security administration because it allows you to specify data access privileges once for the single user-id. However, under this scheme, the people sharing the user-id cannot be distinguished from one another in system operator displays and DBMS reports.
- You can assign a different user-id to every person in the group. This scheme lets you differentiate between the users in reports produced by the DBMS, and it lets you establish different privileges for the individual users later. However, you must specify privileges for each user individually, making security administration tedious and error-prone.
The scheme you choose depends on the trade-offs in your particular database and application.
Several DBMS brands, including Sybase and SQL Server, offer a third alternative for dealing with groups of similar users. They support group-ids, which identify groups of related user-ids. Privileges can be granted both to individual user-ids and to group-ids, and a user may carry out a database action if it is permitted by either the user-id or group-id privileges. Group-ids thus simplify the administration of privileges given to groups of users. However, they are nonstandard, and a database design using them may not be portable to another DBMS brand.
DB2 also supports groups of users but takes a different approach. The DB2 database administrator can configure DB2 so that when you first connect to DB2 and supply your user-id (known as your primary authorization-id), DB2 automatically looks up a set of additional user-ids (known as secondary authorization-ids) that you may use. When DB2 later checks your privileges, it checks the privileges for all of your authorization-ids, primary and secondary. On an IBM mainframe system, the DB2 database administrator normally sets up the secondary authorization-ids so that they are the same as the user group names used by ResourceAccess Control Facility (RACF), the IBM mainframe security facility. Thus, the DB2 approach effectively provides group-ids but does so without adding to the user-id mechanism.
4. Security Objects
SQL security protections apply to specific objects contained in a database. The SQL1 standard specified two types of security objects—tables and views. Thus, each table and view can be individually protected. Access to a table or view can be permitted for certain user-ids and prohibited for other user-ids. The SQL2 standard expanded security protections to include other objects, including domains and user-defined character sets, and added a new type of protection for table or view access.
Most commercial SQL products support additional security objects. In a SQL Server database, for example, a stored procedure is an important database object. The SQL security scheme determines which users can create and drop stored procedures and which users are allowed to execute them. In IBM’s DB2, the physical tablespaces where tables are stored are treated as security objects. The database administrator can give some user-ids permission to create new tables in a particular tablespace and deny that permission to other user-ids. Other SQL implementations support other security objects. However, the underlying SQL security scheme—of specific privileges applied to specific objects, granted or revoked through the same SQL statements—is almost universally applied.
5. Privileges
The set of actions that a user can carry out against a database object are called the privileges for the object. The SQL1 standard specifies four basic privileges for tables and views:
- The SELECT privilege allows you to retrieve data from a table or view. With this privilege, you can specify the table or view in the FROM clause of a SELECT statement or subquery.
- The INSERT privilege allows you to insert new rows into a table or view. With this privilege, you can specify the table or view in the INTO clause of an INSERT
- The DELETE privilege allows you to delete rows of data from a table or view. With this privilege, you can specify the table or view in the FROM clause of a DELETE
- The UPDATE privilege allows you to modify rows of data in a table or view. With this privilege, you can specify the table or view as the target table in an UPDATE The UPDATE privilege can be restricted to specific columns of the table or view, allowing updates to these columns but disallowing updates to any other columns.
These four privileges are supported by virtually all commercial SQL products.
6. SQL2 Extended Privileges
The SQL2 standard expanded the basic SQL1 privileges in several dimensions. It added new capabilities to the SQL1 INSERT and UPDATE privileges. It added a new REFERENCES privilege that restricts a user’s ability to create a reference to a table from a foreign key in another table. It also added a new USAGE privilege that controls access to the new SQL2 database structures of domains, character sets, collation sequences, and translations.
The SQL2 extensions to the INSERT and UPDATE privileges are straightforward. These privileges may now be granted for a specific column or columns within a table, instead of applying to the entire table. The sample database provides a simple example of how this capability can be useful. Suppose you wanted to give your human resources manager the responsibility to insert new employees into the SALESREPS table, once the hiring paperwork is complete. The HR manager should supply the employee number, name, and similar information. But it should be the responsibility of the sales VP to set the QUOTA column for the new employee. Adjustments to the SALES column for existing employees would be similarly restricted.
Using the new SQL2 capabilities, you could implement this scheme by giving the HR manager INSERT privileges on the appropriate columns. The other columns (such as SALES and QUOTA) for any newly inserted employees would initially have the NULL value. With the UPDATE privilege on the other columns, the sales VP can then set the appropriate quota. Without the ability to specify these privileges on specific columns, you would have to either relax the restrictions on column access or define extraneous views on the table simply to restrict access.
The SQL2 standard does not allow the SELECT privilege to be applied to specific columns like the new INSERT and UPDATE capabilities; it must still be specified for an entire table. Theoretically, this capability isn’t really needed, since you can achieve the same effect by defining a view on the table, limiting the view to specific columns, and then defining the appropriate privileges on the view. However, a column-specific SELECT privilege can be a much more straightforward approach. It keeps the structure of the database simpler (fewer view definitions) and concentrates the security scheme more tightly in one place (the GRANT statements). Several major DBMS brands, including Sybase and SQL Server, allow you to specify column-specific SELECT privileges, using the same syntax as for the column-specific UPDATE and INSERT. The SQL2 standard includes a note that this capability is also intended to be considered for future updates of the standard.
The new SQL2 REFERENCES privilege deals with a more subtle SQL security issue posed by the SQL2 capabilities of foreign keys and check constraints. Using the sample database as an example, suppose an employee has the ability to create a new table in the database (for example, a table containing new product information) but does not have any access to the employee information in the SALESREPS table. You might assume, given this security scheme, that there is no way for him to determine the employee numbers being used or whether a new employee has been hired.
However, this isn’t strictly true. The employee could create a new table, with a column that is defined as a foreign key to the SALESREPS table. (Recall that this means the only legal values for this column are primary key values for the SALESREPS table— that is, valid employee numbers.) With this new table, the employee can simply try to insert new rows with different values in the foreign key column. The INSERT statements that succeed tell the employee that that he has discovered a valid employee number; those that fail represent invalid employee numbers.
Even more serious problems can be created by a new table defined with a check constraint on a column. For example, suppose the employee tries to execute this CREATE TABLE statement:
CREATE TABLE XYZ (TRYIT MONEY,
CHECK ((SELECT QUOTA
FROM SALESREPS
WHERE TITLE = ‘VP Sales’)
BETWEEN 400000 AND 500000))
Because of the column constraint linked to a value from the SALESREPS table, if this statement succeeds, it means the VP of sales has a quota in the specified range. If it doesn’t, the employee can keep trying similar CREATE TABLE statements until he has determined the appropriate quota.
To eliminate this backdoor access to data, the SQL2 standard specifies a new REFERENCES privilege. Like the INSERT and UPDATE privileges, the REFERENCES privilege is granted for specific columns of a table. Only if a user has the REFERENCES privilege for a column is he or she allowed to create a new table that refers to that existing column in any way (for example, as the target of a foreign key reference, or in a check constraint, as in the previous examples). In databases that don’t yet implement the REFERENCES privilege but do support foreign keys or check constraints, the SELECT privilege is sometimes used in this role.
Finally, the SQL2 standard specifies the USAGE privilege to control access to domains (sets of legal column values), user-defined character sets, collating sequences, and translations. The USAGE privilege is a simple on/off switch that either allows or disallows the use of these SQL2 database objects, by name, for individual user-ids. For example, with the USAGE privilege on a domain, you can define a new table with a column whose data type is defined as that domain. Without the privilege, you cannot create such a column definition. These privileges are directed mostly toward simplifying administration of large corporate databases that are used and modified by many different development teams. They typically do not present the same kinds of security issues as the table and column access privileges.
7. Ownership Privileges
When you create a table with the CREATE TABLE statement, you become its owner and receive full privileges for the table (SELECT, INSERT, DELETE, UPDATE, and any other privileges supported by the DBMS). Other users initially have no privileges on the newly created table. If they are to be given access to the table, you must explicitly grant privileges to them, using the GRANT statement.
When you create a view with the CREATE VIEW statement, you become the owner of the view, but you do not necessarily receive full privileges on it. To create the view successfully, you must already have the SELECT privilege on each of the source tables for the view; therefore, the DBMS gives you the SELECT privilege for the view automatically. For each of the other privileges (INSERT, DELETE, and UPDATE), the DBMS gives you the privilege on the view only if you hold that same privilege on every source table for the view.
8. Other Privileges
Many commercial DBMS products offer additional table and view privileges beyond the basic SELECT, INSERT, DELETE, and UPDATE privileges. For example, Oracle and the IBM mainframe databases support an ALTER and an INDEX privilege for tables. A user with the ALTER privilege on a particular table can use the ALTER TABLE statement to modify the definition of the table; a user with the INDEX privilege can create an index for the table with the CREATE INDEX statement. In DBMS brands that do not support the ALTER and INDEX privileges, only the owner may use the ALTER TABLE and CREATE INDEX statements.
Additional privileges are frequently supported for DBMS security objects other than tables and views. For example, Sybase and SQL Server support an EXECUTE privilege for stored procedures, which determines whether a user is allowed to execute a stored procedure. DB2 supports a USE privilege for tablespaces, which determines whether a user can create tables in a specific tablespace.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.