Managing Security with Privileges

An RDBMS is essentially a collection of objects — schemas, tables, views, procedures, and so on, in addition to the processes that manage these objects. Restricting access to these objects is an essential security mechanism implemented on the SQL level through the privileges system.

Privileges represent the rights of a particular user to access, create, manipulate, and destroy various objects inside a database, as well as perform administrative tasks. Privileges can be granted to a user, or ROLE, or both (the concept of ROLE is discussed in the next paragraph).

All the privileges can be divided into two broad categories — system privileges and object privilege — and they vary widely among different database vendors. For a more detailed look, refer to the section on specific RDBMS implementations later in this chapter.

GRANT statement

The SQL92/99 standard defines privileges as the types of actions a user is authorized to perform on the objects and in the system to which s/he is granted access. All these privileges are valid across the three RDBMS discussed in this book.

A privilege can be granted — either to an individual user or to a role. The GRANT statement can be used for granting either system privileges or object privileges. The syntax for granting the privilege is fairly consistent across all three RDBMS packages, and multiple privileges can be granted in a single statement, such as:

GRANT [ALL [PRIVILEGES]] |   <privilege,...> [ON <object_name>] TO <user> | <group>   | <role> [WITH GRANT OPTION] 

The privilege can be any of the ones listed in Table 12-6; the options clauses vary among databases, and are listed in Table 12-2.

Table 12-2: GRANT Statement Options

Option	Description	Applies To
ADMIN OPTION	Allows the grantee to GRANT this system level privilege to other users or roles.	Oracle 9i
GRANT OPTION	Allows the grantee to GRANT this object level privilege to other users or roles.	Oracle 9i, Microsoft SQL Server 2000, IBM DB2 UDB 8.1 SQL92/99 Standard
HIERARCHY OPTION	The WITH HIERARCHY OPTION (Oracle only) indicates that the object privilege is granted not only for the object itself but also for all derived objects.	Oracle 9i

Granting system-level privileges

System privileges in general allow users to perform some administrative tasks within a given RDBMS (creating a database; creating and dropping users; creating, altering, and destroying database objects; and so on). You need a sufficiently high level of authority within the RDBM system to be able to exercise or grant system privileges. The features that distinguish these system privileges from object privileges are their scope and, sometimes, the types of activities they allow the user to perform.

System privileges are strictly database specific: each vendor implements its own set of system privileges and some system privileges may have different meanings for different vendors. Some systems — the Microsoft SQL Server, for instance — do not even define system privileges, using privileges for SQL statements instead.

Oracle 9i has literally dozens of system privileges (and roles that bundle them together), the most common of which are given in Table 12-3. The SQL statement syntax that grants a system privilege is very much in line with the SQL standard. All granted privileges enable the grantee immediately (if the grantee is a ROLE, it acquires the privileges once enabled). The following code presents a basic syntax for granting system privileges in Oracle 9i.

 GRANT ALL [PRIVILEGES] |  <system privilege,...> | <role> TO <user> | <role> |  <PUBLIC> [IDENTIFIED BY <password>] [WITH ADMIN OPTION]; 

System privileges might be any of those listed in Table 12-3 (and some more complex or obscure ones, which were not included here). You can either grant a privilege or a role (that was granted some privileges); the ALL keyword refers to all privileges at once and might be followed by an optional PRIVILEGES keyword, introduced in Oracle for compatibility with the SQL99 standard.

Table 12-3: Common Oracle 9i System Privileges

System Privilege	Description
ALTER DATABASE	Permits grantee to alter Oracle database.
ALTER SYSTEM	Permits grantee to alter Oracle system allowing for execution of system-altering statements.
AUDIT SYSTEM	Permits grantee to issue AUDIT SQL statements.
CREATE [PUBLIC] DATABASE LINK	Permits grantee to create private/public database links in the grantee's schema.
DROP [PUBLIC] DATABASE LINK	Permits grantee to drop public database links.
CREATE ANY INDEX	Permits grantee to create a domain INDEX in any schema, or an index on any table in any schema.
ALTER ANY INDEX	Permits grantee to alter any INDEX in any schema.
DROP ANY INDEX	Permits grantee to drop any INDEX in any schema.
CREATE [ANY] MATERIALIZED VIEW	Permits grantee to create a materialized view in his/her own schema (or any schema if the clause is used).
ALTER ANY MATERIALIZED VIEW	Permits grantee to alter a materialized view in any schema.
DROP ANY MATERIALIZED VIEW	Permits grantee to drop a materialized view in any schema.
CREATE [ANY] OPERATOR	Permits grantee to create an operator and its bindings in his/her own schema (or any schema if the clause is used).
DROP ANY OPERATOR	Permits grantee to drop an operator in any schema.
EXECUTE ANY OPERATOR	Permits grantee to execute an operator in any schema.
CREATE [ANY] PROCEDURE	Permits grantee to create a procedure (or function), stand-alone or packaged, in his/her own schema (or any schema if the clause is used).
DROP ANY PROCEDURE	Permits grantee to drop a procedure (or function), stand-alone or packaged in any schema.
EXECUTE ANY PROCEDURE	Permits grantee to execute a procedure (or function), stand-alone or packaged in any schema.
CREATE PROFILE	Permits grantee to create profiles.
ALTER PROFILE	Permits grantee to alter existing profiles.
DROP PROFILE	Permits grantee to drop existing profiles.
CREATE ROLE	Permits grantee to create a role.
ALTER ANY ROLE	Permits grantee to alter any role in existing the database.
DROP ANY ROLE	Permits grantee to drop any role existing in the database.
GRANT ANY ROLE	Permits grantee to grant any existing role in the database.
CREATE [ANY] SEQUENCE	Permits grantee to create a sequence in his/her own schema (or any schema if the clause is used).
ALTER ANY SEQUENCE	Permits grantee to alter any sequence in any schema.
DROP ANY SEQUENCE	Permits grantee to drop any sequence in any schema.
SELECT ANY SEQUENCE	Permits grantee to access any sequence in any schema.
CREATE SESSION	Permits grantee to connect to the Oracle Database.
ALTER SESSION	Permits grantee to issue ALTER SESSION statements.
System Privilege	Description
CREATE [ANY | PUBLIC ] SYNONYM	Permits grantee to create private or public synonym in his/her own schema (or any schema if the clause is used).
DROP [ANY | PUBLIC ] SYNONYM	Permits grantee to drop public synonym in his/her own schema (or any schema if the clause is used).
CREATE [ANY] TABLE	Permits grantee to create a table in his/her own schema (or any schema if the clause is used).
ALTER ANY TABLE	Permits grantee to alter any table in any schema.
DELETE ANY TABLE	Permits grantee to delete data in any table or view in any schema.
DROP ANY TABLE	Permits grantee to drop or truncate any table in any schema.
INSERT ANY TABLE	Permits grantee to insert data into tables and views in any schema.
LOCK ANY TABLE	Permits grantee to lock tables and views in any schema.
SELECT ANY TABLE	Permits grantee to select data from any tables or views in any schema.
UPDATE ANY TABLE	Permits grantee to update data or view in any schema
CREATE TABLESPACE	Permits grantee to create a tablespace.
ALTER TABLESPACE	Permits grantee to alter a tablespace.
DROP TABLESPACE	Permits grantee to drop a tablespace.
CREATE [ANY] TRIGGER	Permits grantee to create a database trigger in his/her own schema (or any schema if the clause is used).
ALTER ANY TRIGGER	Permits grantee to alter (enable, disable, or compile) any trigger in any schema.
DROP ANY TRIGGER	Permits grantee to drop database trigger in any schema.
CREATE [ANY] TYPE	Permits grantee to create object types and object bodies in his/her own schema (or any schema if the clause is used).
ALTER ANY TYPE	Permits grantee to alter object types in any schema.
DROP ANY TYPE	Permits grantee to drop any object type and object bodies in any schema.
EXECUTE ANY TYPE	Permits grantee to use any user-defined object type or collection in any schema, and to invoke methods defined within these object types.
UNDER ANY TYPE	Permits grantee to create a subtype of any nonfinal object types.
CREATE USER	Permits grantee to create a user, and, at the same time, assign quotas on any tablespace, set default temporary tablespaces, and assign a PROFILE.
ALTER USER	Permits grantee to alter any user, i.e., change a user's authentication method, assign quotas on any tablespace, set default temporary tablespaces, and assign a PROFILE and default roles.
BECOME USER	Permits grantee to become another user.
DROP USER	Permits grantee to drop other users.
CREATE [ANY] VIEW	Permits grantee to create a view in his/her own schema (or any schema if the clause is used).
DROP ANY VIEW	Permits grantee to drop views in any schema.
UNDER ANY VIEW	Permits grantee to create subviews for any object views.
COMMENT ANY TABLE	Permits grantee to add comments on any table, view, or column in any schema.
GRANT ANY PRIVILEGE	Permits grantee to grant any system privilege.
SELECT ANY DICTIONARY	Permits grantee to query any data dictionary object in the Oracle SYS schema.

Tip 

You can view all system privileges associated with a user by querying DBA_SYS_PRIVS in the Oracle dictionary view; the privileges available for the session are shown in the catalog view SESSION_PRIVS. See more on system catalogs in Chapter 13.

The privilege or role can be granted to a user, role (either predefined or created), or PUBLIC (which effectively means all users defined in the RDBMS). The IDENTIFIED BY clause specifies a password for an existing user, or — if a user does not yet exist — tells Oracle to create such a user implicitly. This clause is invalid if the grantee is a role, because it has to be created explicitly.

WITH ADMIN OPTION is an Oracle-specific clause. Essentially it means that the user or members of a role will be allowed to GRANT the assigned system privilege to some other users or roles (with the exception of GLOBAL roles), revoke the privilege from another user or role, and so on. In that regard it works very much like the WITH GRANT OPTION clause for the object-level privilege, though there are some subtle differences in usage. Refer to vendor documentation for a full explanation.

Here are some examples based in the ACME database. To grant a user privilege to create a table in the database and, in turn, pass it onto others, the following statement could be used:

GRANT CREATE TABLE TO  new_user IDENTIFIED BY it_is_me WITH ADMIN OPTION; Grant  succeeded.

If you have sufficient privileges, the user NEW_USER identified by the password IT_IS_ME will be created, but you cannot use this user ID and password to connect to the Oracle database if the user NEW_USER has not been granted the CREATE SESSION system privilege, which it would need to access the database. The error ORA-01045: user NEW_USER lacks CREATE SESSION privilege; logon denied would be generated.

To fix the situation you need to grant the newly created user this privilege:

GRANT CREATE SESSION TO  new_user IDENTIFIED BY it_is_me WITH ADMIN OPTION; Grant succeeded. 

Now you can connect to the database using NEW_USER/IT_IS_ME credentials, and — because of the WITH ADMIN OPTION — grant this privilege to other users.

There are two more system privileges in Oracle deserving separate discussion: SYSDBA and SYSOPER, shown in Table 12-4. These privileges act like roles in that they include a number of other system privileges. When connecting to the Oracle database, you can specify to connect AS SYSDBA or AS SYSOPER, assuming that these privileges had been granted to the user. SYSDBA is one of the highest privileges that can be granted.

Table 12-4: Oracle 9i SYSDBA and SYSOPER System Privileges

Privilege	Description
SYSDBA	Permits grantee to perform STARTUP and SHUTDOWN operations, CREATE DATABASE, ALTER DATABASE  (open, mount, backup and change default character set) ARCHIVELOG and RECOVERY, CREATE SPFILE, and includes the RESTRICTED SESSION privilege.
SYSOPER	Permits grantee to perform STARTUP and SHUTDOWN operations, ALTER DATABASE (only open, mount and backup), ARCHIVELOG and RECOVERY, CREATE SPFILE, and includes the RESTRICTED SESSION privilege.

Note 

On some platforms for Oracle 9i it is possible to assign privileges to database users through the initialization parameter OS_USERS, which allows you to grant roles using operating system facilities. For such users, you cannot also use the GRANT statement to grant additional roles, though it is possible for all other users and roles.

IBM DB2 UDB is somewhat similar in this aspect to Oracle; it has system privileges, and some of the privileges are associated with authority levels (see more on this later in the chapter). All system-level privileges for DB2 UDB are shown in Table 12-5.

Table 12-5: Common IBM DB2 UDB System Privileges

System Privilege	Description
BINDADD	Permits grantee to create packages; the package creator automatically has object level CONTROL privilege.
CONNECT	Permits grantee to access the DB2 UDB database.
CREATETAB	Permits grantee to create tables within the database (with the CONTROL object level privilege granted automatically on all created objects, and retained afterward even if the CREATETAB system privilege is revoked).
CREATE_NOT_FENCED	Grants user the authority to register functions for execution in the database manager main process.
IMPLICIT_SCHEMA	Permits grantee to implicitly create schema.
DBADM	Grants database administrator's authority; the DBA has all the privileges and the ability to grant them to others.
LOAD	Permits grantee to use LOAD utility to transfer data into a database; additional object level permissions are required to successfully perform loading.

The generic GRANT statement in DB2 UDB follows the syntax:

GRANT PRIVILEGES |  <system privilege,...> ON DATABASE TO USER <user> | GROUP  <group> | PUBLIC

As you can see, DB2 UDB does not have WITH ADMIN OPTION clause (as in Oracle), and you cannot use ALL PRIVILEGES, though granting DBADM essentially serves the same purpose.

Here is the example of granting CREATETAB system privilege to PUBLIC (all users), in the database ACME:

GRANT CREATETAB ON DATABASE  TO PUBLIC DB0000I The SQL command completed successfully

Note that unlike Oracle or the MS SQL Server, the keywords USER and GROUP must be specified in DB2 UDB. Granting the system privilege (database authority in IBM DB2 jargon) to a group called SALES would have the following syntax:

GRANT CREATETAB ON DATABASE  TO GROUP sales DB0000I The SQL command completed successfully 

If neither USER nor GROUP keywords are specified, then DB2 UDB employs a set of security authorization rules to resolve potential conflicts: if the name is defined in the OS as GROUP, then GROUP would be assumed; if it is defined in the OS as USER, or is undefined, then USER would be assumed; if the name refers to both GROUP and USER (it is possible to have a GROUP and a USER with the same name) then an error is generated. The same error would also be generated if external DCE authentication were used. There is more on authentication methods later in this chapter, and a detailed discussion can be found in the vendor's documentation.

To GRANT the DBADM authority, a user must have SYSADM authority. Both SYSADM and DBADM can grant the other privileges to users or groups. There is more on IBM DB2 UDB's authorities later in this chapter.

The Microsoft SQL Server 2000 does not have system privileges, or at least not in the sense that Oracle or IBM have it. The privileges are granted to a user (or role) for specific SQL statements. Once the privilege is granted, a user can execute the statement to perform operations that they define. Note that the SQL Server has no WITH ADMIN OPTION clause for these privileges:

GRANT ALL |  <statement,...> TO <security_account>

The statements that require special permissions (privileges) are those that could do the most harm, if misused: adding new objects to a database, altering existing ones, and performing some administrative tasks. Most of these statements are discussed in detail in Chapter 4. The statement list includes (among others) the following:

• CREATE VIEW

• CREATE TABLE

• CREATE DEFAULT

• CREATE PROCEDURE

• CREATE RULE

• BACKUP DATABASE

• BACKUP LOG

The system permissions are tied to a database (MS SQL Server also uses this concept; the closest Oracle equivalent would be schema) and are hierarchical. For example, to GRANT the privilege to execute a CREATE DATABASE statement, you must be in the context of the SQL Server master database as this statement produces results affecting the whole instance of the SQL Server 2000.

The security account refers to the SQL Server user, SQL Server role, Windows NT user, or Windows NT group. There is some granularity to the security accounts defined by the SQL Server: privileges granted to a user (either on the SQL Server or Windows NT) affect this user only; privileges granted to a role or Windows NT group affect all members of this role or group. In the case of a privileges conflict between a group/role and their members, the most restrictive privilege — DENY — takes precedence (discussed later in the chapter).

Tip 

In order to effectively manage SQL Server security using Windows NT groups and accounts, you must understand underlying Windows OS security.

Granting the CREATE DATABASE statement to a user/role while being in context of the ACME database would produce an error, as follows:

USE acme  GRANT CREATE DATABASE TO PUBLIC CREATE DATABASE permission can only be granted  in the master database.

Note 

USE keyword is not a part of the SQL standard; it is valid though in Transact-SQL dialect, which is used by the Microsoft SQL Server and the Sybase Adaptive Server.

Changing the context to the master database resolves the issue:

USE master GRANT CREATE  DATABASE TO PUBLIC The command(s) completed successfully.

Granting something more local, pertaining to a database itself, requires a narrower scope. To grant a privilege to create a view in the ACME database, one must be in ACME database context:

USE acme GRANT CREATE VIEW  TO PUBLIC The command(s) completed successfully. 

Some Transact-SQL statements cannot be granted through privileges; the grantee must be a member of a predefined fixed server role (discussed later in this chapter). This means that in order to be able to execute, for example, the KILL statement (that stops a process inside an SQL Server installation) you have to be a member of the processadmin fixed role, in order to be able to grant ALL statement permissions you have to be a member of the sysadmin fixed role, the members of the db_owner role can grant and/or revoke any privilege within their database, and so on.

Granting object-level privileges

By their very nature, the object-level privileges are much more fine-grained than system-level ones. This is reflected in the syntax of the GRANT statement. These privileges could go all the way down to column level (if the object is a database table or view), or to any other object within the database such as stored procedures, functions, and triggers. The SQL Object-Level privileges are listed in Table 12-6.

Table 12-6: SQL Object-Level Privileges

Object Privilege	Compliance	Description
INSERT	SQL92/99, IBM DB2 UDB, Oracle, Microsoft	Permits the grantee to insert data in a database table (or view). The permission could be further restricted to specific columns.
SELECT	SQL92/99, IBM DB2 UDB, Oracle, Microsoft	Permits the grantee to select data from a database table, view, or some other implementation-specific objects (sequences, snapshots, etc.). The permission could be further restricted to specific columns.
UPDATE	SQL92/99, IBM DB2 UDB, Oracle, Microsoft	Permits the grantee to update data in a database table or view. The permission could be further restricted to specific columns.
DELETE	SQL92/99, IBM DB2 UDB, Oracle, Microsoft	Permits the grantee to delete data in a database table or view.
ALTER	IBM DB2 UDB, Oracle, Microsoft	While generally considered a system-level privilege, it permits the grantee to alter certain database objects, e.g., tables and views. Some of the objects might be implementation-specific. Though this privilege is not part of a standard SQL, it is implemented by all three vendors.
INDEX	IBM DB2 UDB, Oracle, Microsoft	Permits the grantee to create an index on the existing table.
UNDER	Oracle	Permits grantee to create a subview under a view.
EXECUTE	IBM DB2 UDB, Oracle, Microsoft	Permits the grantee to execute an existing stored procedure or function.
REFERENCES	SQL92/99, IBM DB2 UDB, Oracle, Microsoft	Permits the grantee to modify an existing table (or create a new one) that incorporates a foreign key constraint referencing some other table.

This is the Oracle 9i generic syntax for granting privileges to the database objects:

GRANT [ALL [PRIVILEGES]] |  <object_privilege,...> [ON [<schema>].<object>] TO  <user> | <role> | <PUBLIC> [WITH {GRANT OPTION | HIERARCHY  OPTION}];

As with the GRANT system privileges statement, you need to supply a list of all the privileges you wish to grant (see Table 12-6 for a list of relevant object privileges). Specifying ALL would enable all privileges, but you as a user must have sufficient system privileges to grant this option yourself. Next comes the list of columns to which you may grant access (if applicable, as some database objects do not have columns), then you specify the object itself — table, view, procedure, package, sequence, synonym, and any other valid Oracle database object (the new JAVA and DIRECTORY clauses are not part of SQL and are beyond the scope of this book).

Note that not every object has a given privilege: some privileges are irrelevant to the objects. For example, the REFERENCES privilege does not make much sense if you are trying to assign it to an Oracle sequence, nor does the EXECUTE privilege make sense for a table. Consequently, if you specify ALL privileges, only those allowed for the object type would be granted. The following GRANT statement would generate an error:

SQL> GRANT EXECUTE ON  deduction TO PUBLIC; ORA-02224: EXECUTE privilege not allowed for  tables

The object privilege could be granted to a user, to a role, or to PUBLIC (which is a specific way to grant privileges to each and every user within that database).

The WITH GRANT OPTION indicates that the grantee will be able in his/her turn to GRANT this privilege to other users or roles.

The WITH HIERARCHY OPTION (Oracle only) indicates that the object privilege is granted not only for the object itself but also for all derived objects. For example, if a view is based upon a table, granting privileges to a table with such an option would automatically grant the same privileges for the view; however, it does not work the other way around — privileges for the view would not give the same access to the base table.

Note 

The WITH GRANT OPTION can be specified only when the grantee is a user or PUBLIC; this option is invalid when granting to a role.

Here is a real example that is less confusing; it grants ALL privileges in the ACME database table PRODUCT to the SALES_FORCE role. Whoever belongs to the SALES_FORCE role will be able to exercise these privileges as soon as the following statement is executed:

SQL> GRANT SELECT,  UPDATE, DELETE ON product TO sales_force; Grant succeeded.

IBM DB2 UDB has probably the most diverse syntax when it comes to object-level privileges. In addition to the object-level privileges shown in Table 12-6, it has a bunch of its own (Table 12-7).

Table 12-7: IBM DB2 UDB Object-Level Privileges

Object Privilege	Syntax	Description	Pertains To
CONTROL	GRANT CONTROL ON {OBJECT}<object_name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to drop the object.	Index package table view nickname
BIND	GRANT BIND ON PACKAGE <package TO USER <user> name>| GROUP <group>| PUBLIC	Permits grantee to bind the package.	Package
ALTERIN	GRANT ALTERIN ON SCHEMA<schema name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to alter the existing objects in the schema, or to add comments to them.	Schema
CREATEIN	GRANT ALTERIN ON SCHEMA<schema name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to create objects in the schema.	Schema
DROPIN	GRANT ALTERIN ON SCHEMA<schema name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to drop objects in the schema.	Schema
USAGE	GRANT USAGE ON SEQUENCE<sequence_name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to access the sequence through NEXTVAL or PREVVAL expressions.	Sequence
USE OF TABLESPACE	GRANT USE OF TABLESPACE <tablespace_name> TO USER <user> | GROUP <group> | PUBLIC	Permits grantee to access and use the specified tablespace.	Tablespace
PASSTHRU	GRANT PASSTHRU ON SERVER<server_name> TO USER <user>| GROUP <group>| PUBLIC	Permits grantee to access and use a data source in a pass-through mode.	Server

Each of these privileges abides by certain rules, and it should not be assumed that these rules are transferable between different objects. Refer to DB2 UDB documentation (or IBM DB2 UDB–specific books) for more information.

The most important thing (and relevant in the sense of being close to the SQL standard) is granting privileges to a table or a view. Here is a more specific syntax for granting such privileges to a table (or view, or nickname):

GRANT [ALL [PRIVILEGES]] |  ALTER | CONTROL | DELETE | INDEX | INSERT | REFERENCES (<column  name>,...) | SELECT [(<column name>,...)] | UPDATE [(<column  name>,...)] ON [TABLE] <table_name> | <view_name> |  <nickname> TO USER <user> | GROUP <group>| PUBLIC [WITH GRANT  OPTION]

Note 

In IBM DB2 UDB, the GRANT ALL statement grants all privileges except the CONTROL privilege.

To grant, for example, a privilege to reference a table (create a foreign key based on the table's column) to PUBLIC (all users), the following statement would be issued:

GRANT REFERENCES ON TABLE  customer(cust_paytermsid_fn) TO PUBLIC WITH GRANT OPTION DB0000I The SQL  command completed successfully

This grants privilege to create and drop a foreign key that references the table (as the parent). Of course, the grantor must have sufficient privileges him/herself to execute any of the GRANT statements.

The following generic syntax for granting object-level privileges is valid in Microsoft SQL Server 2000:

GRANT [ALL [PRIVILEGES]] |  <permission1>,<permission2>,......  [<column1>,<column2>...)] ON [<table> | <view>] | ON  [<stored_procedure> | <extended_procedure>] | ON  [<user_defined_function>] TO <security_account>,... [WITH GRANT  OPTION] [AS <group> | <role>]

Note 

PRIVILEGES is an optional SQL99 keyword that can be used for standards compliance in any of the three RDBMS discussed.

There are many similarities of the object-level privileges GRANT statement to that used with the system-level one: use of the ALL keyword to grant all the privileges en mass, the security_account refers to exactly the same thing (see previous paragraph), and so on.

Since the statement in the preceding code grants privileges for objects (and object parts — for example, columns within tables), it has many different options. You can grant a privilege (one from the list in the Table 12-6) on a table or a view, on one or more columns within these, and on some other valid existing objects within the SQL Server database. The syntax allows you to list several subobjects at the same time (columns, tables, and so on), though you cannot list several objects like tables and views at the same time.

The notable difference between the Microsoft SQL Server and both Oracle and IBM DB2 UDB is an additional AS clause. It is used when privileges (permissions in Microsoft terminology) granted to a group or role are, at the same time, granted to users that are not members of this group/role — and therefore the group does not have sufficient authority. In this case you must use both the WITH GRANT OPTION and AS clauses. This would permit the grantee to GRANT this privilege under the authority of the group/role specified in the AS clause.

The following SQL statement grants SELECT object-level privileges to the Microsoft SQL Server 2000 predefined role PUBLIC for the table PRODUCT (see the paragraph on security models overview later in the chapter):

GRANT SELECT ON product TO  PUBLIC The command(s) completed successfully.

Now any authenticated user belonging to the role PUBLIC would be able to issue a SELECT statement on the table PRODUCT. An example of a more complex statement, which limits columns for user viewing, follows this paragraph. This statement grants PUBLIC role permissions to select and update only two columns from the table PRODUCTS, while hiding the rest:

GRANT SELECT, UPDATE  (prod_id_n, prod_price_n) ON product TO PUBLIC The command(s) completed  successfully.

Tip 

In the generic MS SQL Server, it makes no difference which of the following syntaxes you choose for the GRANT statement:

GRANT <...> (prod_id_n, prod_price_n) ON product

or:

GRANT <...> ON product (prod_id_n, prod_price_n)

The list of columns may either follow or precede the table name.

REVOKE privileges

This command revokes privileges — either system-level or object-level — from a database user, role, or group. Roles, which are just are sets of privileges, are revoked in exactly the same way as users. The syntax that follows revokes system privileges using SQL99 standards:

REVOKE [GRANT OPTION FOR] ALL   [PRIVILEGES] | <privilege>,... FROM USER <user> | GROUP   <group> | PUBLIC [CASCADE | RESTRICT]

As you can see, you can either revoke a privilege or a GRANT OPTION of that privilege, meaning that the user/role would lose his/her ability to GRANT this privilege in its turn. The privileges themselves remain unaffected.

SQL99 also specifies two optional clauses, RESTRICT and CASCADE. With the first option, the statement succeeds only if there are no abandoned privileges in the database. Such a situation might occur when the user, for example, already granted this privilege to some other user. When you are determined to revoke the privilege no matter what, and propagate this change across all the users that have received this privilege from the user, the CASCADE clause must be specified.

As usual, each of the vendors has its own ideas for implementing this statement.

Revoking system-level privileges

Oracle 9i syntax follows that of the SQL99 standard, but does not support the CASCADE and RESTRICT clauses. It also does not allow for revoking GRANT/ADMIN OPTION.

REVOKE ALL [PRIVILEGES] |  <role> | <system_privilege>,... FROM <user> | <role> |  PUBLIC;

To revoke a system privilege or role requires sufficient privileges, or just the ADMIN OPTION privilege. This preceding statement can only revoke the privileges and roles that have been granted through the GRANT statement; if the privilege was granted with initialization parameter OS_USERS (see GRANT privileges section's Note), you cannot revoke it with the REVOKE statement.

You can revoke the system-level privilege CREATE TABLE from a user NEW_USER with the following statement:

SQL> REVOKE CREATE TABLE  FROM new_user; Revoke succeeded. 

The effects of REVOKE are immediate — the user loses the privilege the very moment the statement is executed by Oracle. If the privilege is revoked from PUBLIC, each user loses that privilege if it was granted through PUBLIC; privileges granted to the user directly or through a role remain unaffected in this case. The rules for revoking system privileges are complex. For example, if a privilege (or role) is revoked from a role, it is revoked from that role only; if the role had granted the privilege (or role) to another user (or role), the user would continue to exercise the privilege (or role). There is no cascading effect for revoking the system privileges in Oracle. For example, say user1 was granted the system-level privilege WITH ADMIN option, and, in turn, user1 granted this privilege to user2. The latter (user2) retains this privilege even if it is revoked from user1 some time later.

Also, keep in mind that there is no reversal for all the objects that a user had created. When privileges are revoked, all objects created with these privileges up to the moment will remain in the database.

Revoking system-level privileges in IBM DB2 UDB is simple — just follow the GRANT statement path in reverse:

REVOKE PRIVILEGES |  <system privilege>,... ON DATABASE FROM USER <user> | GROUP  <group> | PUBLIC

Here is the example from the section discussing the GRANT statement in DB2 UDB, which revokes the CREATETAB system privilege to PUBLIC (all users), in the database ACME:

REVOKE CREATETAB ON DATABASE  FROM PUBLIC DB0000I The SQL command completed successfully

When neither USER nor GROUP is specified, DB2 UDB looks into the system catalog (more on system catalogs in Chapter 13) to determine grantee type. Revoking a privilege does not necessarily revoke the ability to perform the action if the user has some higher authority, or belongs to a group that holds this privilege.

The Microsoft SQL Server 2000 REVOKE statement for statement permissions (which is Microsoft's system privileges) is similar to that of Oracle. Like Oracle, it does not support CASCADE and RESTRICT at this level, nor revoking the GRANT OPTION:

REVOKE [ALL] |  <privilege>,... FROM <security_account>,... 

The ALL keyword in the REVOKE statement on the system level can only be used by members of the SYSADMIN fixed role. All restrictions and notes mentioned in the GRANT section of this chapter for the SQL Server 2000 also apply here.

To revoke, for example, the CREATE table privilege from the user NEW_USER, the following statement might be used:

REVOKE CREATE TABLE FROM  new_user The command(s) completed successfully.

Revoking object-level privileges

The SQL99 standard does not differentiate between revoking system-level or object-level privileges, providing the same syntax for both. In the vendor RDBMS implementations, the situation is dramatically different; as with the GRANT statement for the object-level privilege, the REVOKE statement is quite complex. The information in this book barely covers the basics of this topic as it is implemented by the vendors; refer to the vendor-specific documentation for more information.

Oracle 9i syntax for revoking object privileges is fairly standard, except for the number of the optional clauses like JAVA and DIRECTORY (skipped for simplicity):

REVOKE  ALL [PRIVILEGES] | <object_privilege,...> [(<column>)] ON  [<schema>].<object>] FROM <user> | <role> | PUBLIC  CASCADE CONSTRAINTS [FORCE];

The object PRIVILEGE can be any of those listed in Table 12-6 (assuming that it is relevant to the object). The COLUMN clause specifies an optional column or a list of columns for which the statement is applicable; the object for which privileges are revoked could be any of the database objects.

The privilege can be revoked from a user, a role, or from PUBLIC. The users cease to exercise the privilege immediately after it is revoked from them.

If the privilege is revoked from a role, all users to whom this role was granted lose this privilege, effective immediately. A privilege revoked from PUBLIC is revoked from every user who had been granted this privilege through the PUBLIC. If, however, a user was granted the privilege directly or through the role, the revoke statement has no effect on him/her.

The following statement revokes INSERT, DELETE , and UPDATE privileges from the PUBLIC for columns CUST_PAYTERMSID_FN and CUST_SALESMANID_FN (assuming that these were granted before) of the table CUSTOMER (database ACME):

SQL> REVOKE INSERT,  DELETE, UPDATE (cust_paytermsid_fn, cust_salesmanid_fn) ON CUSTOMER FROM  PUBLIC; Revoke succeeded.

All dependent objects (for example, views created by the user who lost his/her privilege for the underlying table) related to the revoked privilege become invalid.

Note 

If there were no privileges granted on the object, Oracle neither takes any action nor returns an error.

The CASCADE CONSTRAINTS clause is relevant only if you revoke the REFERENCES privilege, or ALL. It gets rid of all the referential integrity constraints that were defined by the user using this privilege.

Specifying FORCE when revoking the EXECUTE object privilege causes the privileges to be dropped. Even if it leaves user-defined type objects behind, all dependent objects are invalidated.

Unlike system privileges, revoking object privileges in Oracle has a cascading effect. Say a privilege is granted to user1 via the WITH GRANT option, and user1, in turn, grants the privilege to user2. If the privilege is subsequently revoked from user1, user2 loses this privilege the very moment the user1 loses it.

As with system privileges in IBM DB2 UDB, its object-level privileges are revoked with a syntax similar to that of a GRANT statement. DB2 UDB does not support revoking GRANT OPTION nor the CASCADE | RESTRICT clause. For example:

REVOKE ALL [PRIVILEGES] |  <privilege,...> ON <object> <object_name> FROM USER  <user> | GROUP <group> | PUBLIC

The following example revokes a privilege to reference a table (create a foreign key based on the table's column) to PUBLIC (all users):

REVOKE REFERENCES ON TABLE  customer (cust_paytermsid_fn) FROM PUBLIC DB0000I The SQL command completed  successfully

Again, if neither USER nor GROUP is specified, DB2 UDB will look up the information in the system catalog and raise an error if the user from whom the privilege is revoked is authenticated by an external DCE (see OS security integration later in this chapter).

Note 

There all rules to follow when revoking a privilege that was used to create a dependent object. For example, the SELECT privilege for a table passed onto a view would be revoked for the user when s/he loses this privilege; the user would be unable to select from the view just as s/he would not be able to select from the table.

Revoking an object-level privilege might not necessarily revoke the ability to perform the action if the user has some higher authority, or belongs to a group that holds the revoked privilege.

The Microsoft SQL Server 2000 supports revoking GRANT OPTION and the CASCADE clause (but not RESTRICT). The following REVOKE statement is applicable to the context of the current database only:

REVOKE [GRANT OPTION FOR]  ALL [PRIVILEGES] | <permission>,... [(<column,...>)] ON  [<table> | <view>] | ON [<table> | <view>]  [(<column>,...)] | ON [<stored_procedure> |  <extended_procedure>] | ON [<user_defined_function>] FROM  <security_account>,... [CASCADE] [AS <group> |  <role>]

Privileges cannot be revoked from system-fixed roles. When permissions are revoked from a Windows NT group, the security account argument should be in the format BUILTIN\<domain>, where domain represents either a computer name or a domain where this user is defined.

Note 

You must have sufficient authority to revoke privileges; members of the SYSADMIN fixed-server role, DB_OWNER, and DB_SECURITYADMIN are granted REVOKE privilege by default, within their corresponding scope.

To revoke, for example, SELECT permission on the table PRODUCT in the ACME database, the following statement would be issued:

REVOKE SELECT ON product  FROM PUBLIC The command(s) completed successfully.

If the privilege was initially granted WITH GRANT OPTION, both GRANT OPTION FOR and CASCADE must be specified. If the CASCADE option is not specified (default), the user who was granted the privilege through the security account, from which this privilege was revoked, does not lose the privilege.