Types of privileges:
db-privs
table-privs
column-privs
module-privs
ext-routine-privs
sequence-privs
Specifies the list of privileges you want to add to an existing ACL
entry or create in a new one. The operations permitted by a given
privilege keyword differ, depending on whether you granted it for a
database, table, column, module, external routine, or sequence.
The following table lists the privilege keywords and their
meanings for databases, tables, columns, modules, external
routines, and sequences.
Table 1-4 SQL Privileges for Databases, Tables, Columns, Modules,
External Routines and Sequences
For the Access
Privilege Set of For the Access Privilege Set of
a Database, a Table, Column, View, Module,
Grants the External Routine or Sequence,
Privilege Privilege to: Grants the Privilege to:
ALTER Change database Alter the table, index, or storage
parameters or map. Alter a module, external
change a domain. routine, or sequence. Does not
apply to column privileges.
CREATE Create a Create a view, trigger, index,
catalog, schema, sequence, storage map, or outline
table, domain, that uses a table. Does not apply
collating to column privileges.
sequence,
storage area,
external
routine, module,
or sequence.
DBADM Perform any data Not applicable, but syntactically
manipulation or allowed.
data definition
operation
on any named
object. Override
many database
privileges.
DBCTRL Create, delete, Grant or revoke an access privilege
or modify an set entry for the table, sequence,
access privilege module, or external routine. Does
set entry for not apply to column privileges.
the database.
DELETE Delete data from Delete data from a table. Does not
a table defined apply to column privileges.
in the database.
DISTRIBTRANRun a Not applicable.
distributed
(two-phase
commit protocol)
transaction
against the
database.
DROP Delete a Delete the table, index or outline
catalog, that uses a table. Delete a view,
schema, domain, column, constraint, trigger,
collating sequence, or storage map. Delete
sequence, or a view, module, external routine,
path name. or sequence.
EXECUTE Not applicable. Allow the execution of a module or
external routine. Does not apply
to column, sequence, or table
privileges.
INSERT Store data in a Store data in the table. Does
table defined in not apply to column or sequence
the database. privileges.
OPERATOR Not applicable. Not applicable. Syntactically
Syntactically allowed, but not implemented.
allowed, but Reserved for future versions.
not implemented.
Reserved for
future versions.
REFERENCES Not applicable, Define constraints that refer to
but data in a table or column. Define
syntactically tables using the LIKE clause.
allowed. Define synonyms that reference
those objects.
SECURITY Override Not applicable.
many database
privileges.
SELECT Attach to a Read data from a table or
database and reference the NEXTVAL and CURRVAL
read data from a pseudocolumns in a sequence. Does
table defined in not apply to column privileges.
the database.
SHOW Not applicable. Not applicable. Syntactically
Syntactically allowed, but not implemented.
allowed, but Reserved for future versions.
not implemented.
Reserved for
future versions.
UPDATE Update data in a Update data in a table or column.
table defined in
the database.
Privileges on a column are determined by the privileges defined
for the table combined with those specified for the specific
column ACL.
The SELECT privilege is a prerequisite for all other data
manipulation privileges, except UPDATE and REFERENCES. If you
do not grant the SELECT privilege, you effectively deny SELECT,
INSERT, and DELETE privileges, even if they are specified in the
privilege list. It is not possible for you to deny yourself the
SELECT privilege.
For the SELECT, INSERT, UPDATE, and DELETE data manipulation
privileges, SQL checks the ACL for the database and for the
individual table before allowing access to a specific table. For
example, if you are granted SELECT privilege for the EMPLOYEES
table, you are not able to select rows from the table unless you
also have SELECT privilege for the database that contains the
EMPLOYEES table.
A user with the UPDATE privilege on the table automatically
receives the UPDATE privilege on all columns in the table. To
update a column, you must have the UPDATE privilege either for
the column or the table. However, you can restrict the UPDATE
privileges by defining them only on specific columns you want
users to be able to update, and by removing the UPDATE privilege
from the table entry.
You can modify the data in a column only with the UPDATE
privilege on the column and the SELECT privilege on the database.
The REFERENCES privilege lets you define a constraint for a
database with ANSI/ISO-style privileges. For a database with
ACL-style privileges, you need the CREATE privilege to define a
constraint.
You cannot deny yourself the DBCTRL privilege for a database
or table that you create. This restriction may cause GRANT
statements to fail when you might expect them to work.
For instance, suppose an ACL has no entry for PUBLIC. The
following GRANT statement fails because it creates an entry for
PUBLIC at the top of the ACL that does not include the DBCTRL
privilege, effectively denying DBCTRL to all other entries on the
list, including the owner:
SQL> GRANT SELECT, INSERT ON EMPLOYEES TO PUBLIC;
%RDB-E-NO_PRIV, privilege denied by database facility