SQL Bible [Electronic resources] نسخه متنی

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.

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.

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.

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.

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).

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.

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.

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.

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).

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]

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>]

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.

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

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

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.

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).

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.

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.