13.8. Security and Metadata

This section looks briefly at two topics that any database administrator needs to be familiar with: security and metadata in SQL. Let’s begin with security. A database is secureif and only if operations on it can be performed only by users authorized to do so. Each system user is assigned a user identifier (AuthID). The user who “owns” or creates a database has all access privileges on it and can grant and revoke access privileges on it to other users. SQL provides a grant statementfor granting various kinds of privileges to users. The SQL-92 syntax is:

grant all privileges |
select linsert[(col)] | update [ (col) ] | delete | usage | references [ ( col) ] [ .... ]
on object
   to user-list[ with grant option ]

where objectis one of [table] tablename, domain domainname, etc. The user list may include public, meaning all users. SQL: 1999 extended the statement to cater for “roles” (user groups). If with grant option is included, the user list has the power to grant the same privilege to others. For example, the following statement grants all users read access to the Stock table: grant select on Stock to public. The following grants the user with authorization id “Mgr” read access to the Stock table, as well as the right to update values in the price column: grant select, update(price) on Stock to Mgr. Privileges may be removed with the revoke statement:

revoke [ grant option for ] privilege-list
on object
from user-list [ restrict I cascade ]

For example, to revoke the update right granted earlier, use: revoke update ( price ) on Stock from Mgr. The table name in grant or revoke statements may be the name of a base table or view. Granting access to views but not the base tables is one convenient way of enforcing security. The restrict and cascade options are not yet widely supported.

In addition to database privileges, commercial systems also provide controls over operations related to database servers and instances of a DBMS. One server may contain multiple instances and one instance may support many individual databases. Typically, roles are arranged hierarchically, with lower levels of the hierarchy having fewer privileges than higher levels. For example, a lower level administrator may be able to carry out maintenance and utility operations but not access the data stored in the databases.

Now let’s move onto metadata(data about data). SQL systems automatically maintain a set of system tablesholding information about the database schema itself (e.g., base tables, views, domains, constraints, and privileges). In commercial DBMSs, such tables are collectively referred to under various names such as “system catalog”. SQL-92 uses the term “catalog” in a different sense (for a collection of schemas within an environment). System tables of commercial systems also include physical information (e.g., about indexes) that is used by the optimizer. Users with access to the system tables may query them in SQL (just like the application tables).

From SQL-92 onward, the SQL standard defines metadata in terms of tables and assertions in a special schema named DEFINITION_SCHEMA. This provides a reference model for metadata, but there is no intention that this should be implemented in this way in any commercial realization—it’s sufficient for an implementation to simulate its existence. The intended access to metadata is though viewsdefined in another schema, named INFORMATION_SCHEMA. Views in the INFORMATION_SCHEMA are defined in terms of the model in the DEFINITION_SCHEMA. Most of the views are defined with a select privilege granted to public, but no other privileges are granted, so the views cannot be updated. These schemas have been extended as new features of SQL, such as triggers, have been added in subsequent versions of the standard.

To conform to the standard, a DBMS should support these views, and many popular DBMSs are at least in the process of doing so. Commercial systems may still use their own specific names (e.g., SysTables, SysCatalog, etc.) and structures for the metadata. These generally provide equivalent features to the standard schemas, but with some variation in the detailed syntax.

As a simple example, the following query uses the standard Columns view to request the number of columns in the Customer table:

select count (*)
from   Columns
where table_name = 'CUSTOMER'

To give an idea of the variation to be found among commercial systems, the equivalent query in SQL Server 2005 might look like:

select count (*)
from sys.C01umns
where object_id = object_id ('CUSTOMER', 'U')

whereas in DB2 we might see:

select count (*)
from sysibm.Columns
where table_name = 'CUSTOMER'