Creates or adds ANSI/ISO-style privileges to an entry of the
Oracle Rdb access privilege set for a database, table, view,
column, module, sequence, or routine. At database creation time,
you specify whether the database protection mechanism will be
ANSI/ISO-style or ACL-style. For more information on creating or
changing the style of privileges associated with a database, see
the CREATE DATABASE statement.
Each entry in an ANSI/ISO-style access privilege set consists
of an identifier and a list of privileges assigned to the
identifier.
o Each identifier specifies a user or PUBLIC access.
o The set of privileges specifies what operations that user can
perform on the database object.
ANSI/ISO-style privileges:
o Grant access to the creator when an object is created. Because
only the creator is granted access to the newly created
object, additional access must be granted explicitly.
o Support only the PUBLIC identifier as a wildcard.
o Support only user identifiers that translate to an OpenVMS
user identification code (UIC), Rdb users, or roles.
For ANSI/ISO-style databases, a user's privileges are a
combination of all privilege sets that apply to that user. The
access privilege set is not order-dependent. The user matches the
entry in the access privilege set; receives whatever privileges
have been granted for the database object; and receives the
privileges defined for PUBLIC. A user without an entry in the
access privilege set receives only the privileges defined for
PUBLIC, which always has an entry in the access privilege set
even if PUBLIC has no access to the database, table, column,
module, or external routine.
To remove privileges from or entirely delete an entry to the
Oracle Rdb access privilege set for a database object, see the
REVOKE_ANSI statement.
1 – Environment
You can use the GRANT statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a nonstored procedure in a nonstored SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
GRANT -----------> ----------+
+-------------- <-------------+
++-> db-privs-ansi -------> ON DATABASE ALIAS ++-> <alias> -++-------------++
| |+--- , <-----+| ||
| +----> * ------+ ||
+-> table-privs-ansi ----> ON +----->----+---+++-> <table-name> -+--------+|
| +-> TABLE -+ ||+-> <view-name> --+ ||
| |+-------- , <---------------+|
| +--------> * ----------------+|
+-> column-privs-ansi ---> ON COLUMN ---+> <colunn-name> --+--------------+|
| +------- , <-------+ ||
+-> module-privs-ansi ---> ON MODULE --++-> <module-name> --++------------+|
| |+------- , <--------+| ||
| +-------> * ----------+ ||
+-> ext-routine-privs-ansi -+> ON FUNCTION --+-++-><ext-routine-name> -++-+|
| +> ON PROCEDURE -+ |+--------- , <---------+| ||
| +---------> * -----------+ ||
+-> sequence-privs-ansi ---> ON SEQUENCE -++> <sequence-name> -++---------+|
|+------- , <--------+| |
+-------> * ----------+ |
+----------------------------<---------------------------------------------+
+-> grant-ansi-to ---------------------------------------------------------->
grant-ansi-to =
---> TO -+-+-> identifier-ansi-style ---+-+-+-----------------------+---->
| +-> PUBLIC ------------------+ | +-> WITH GRANT OPTION --+
+------------- , <---------------+
db-privs-ansi =
----+---+-+-> SELECT -----+-+----+->
| | +-> INSERT -----+ | |
| | +-> OPERATOR ---+ | |
| | +-> DELETE -----+ | |
| | +-> CREATE -----+ | |
| | +-> ALTER ------+ | |
| | +-> DROP -------+ | |
| | +-> DBCTRL -----+ | |
| | +-> DBADM ------+ | |
| | +-> SHOW -------+ | |
| | +-> REFERENCES -+ | |
| | +-> UPDATE -----+ | |
| | +-> SECURITY ---+ | |
| | +-> DISTRIBTRAN + | |
| +------- , <--------+ |
+-------> ALL PRIVILEGES ----+
table-privs-ansi =
-+--+-+-> SELECT ---------------------------------+-+-+->
| | +-> INSERT ---------------------------------+ | |
| | +-> DELETE ---------------------------------+ | |
| | +-> CREATE ---------------------------------+ | |
| | +-> ALTER ----------------------------------+ | |
| | +-> DROP -----------------------------------+ | |
| | +-> DBCTRL ---------------------------------+ | |
| | +-> SHOW -----------------------------------+ | |
| | +-> REFERENCES +---------------------------+ | |
| | | +> ( +> <column-name> +> ) -+ | |
| | | +----- , <-------+ | | |
| | +-> UPDATE -+-------------------------------+ | |
| | +> ( +> <column-name> --+--> ) -+ | |
| | +----- , <---------+ | |
| +------------------- , <------------------------+ |
+------> ALL PRIVILEGES -----------------------------+
column-privs-ansi =
---+-+-+-> UPDATE -----+-+--+-->
| | +-> REFERENCES -+ | |
| +------- , <--------+ |
+-----> ALL PRIVILEGES --+
module-privs-ansi =
-+--+-+-> ALTER ------------------------------+-+--+-->
| | +-> DBCTRL -----------------------------+ | |
| | +-> DROP -------------------------------+ | |
| | +-> EXECUTE ----------------------------+ | |
| | +-> REFERENCES -------------------------+ | |
| | +-> SHOW -------------------------------+ | |
| +------------------- , <--------------------+ |
+------> ALL PRIVILEGES --------------------------+
ext-routine-privs-ansi =
-+--+-+-> ALTER ------------------------------+-+--+-->
| | +-> DBCTRL -----------------------------+ | |
| | +-> DROP -------------------------------+ | |
| | +-> EXECUTE ----------------------------+ | |
| | +-> REFERENCES -------------------------+ | |
| | +-> SHOW -------------------------------+ | |
| +------------------- , <--------------------+ |
+------> ALL PRIVILEGES --------------------------+
identifier-ansi-style =
----+-> uic-identifier ----+---->
+-> user-identifier ---+
+-> role-name ---------+
sequence-privs-ansi =
-+-+-+-> ALTER -----+-+-+-->
| | +-> DBCTRL ----+ | |
| | +-> DROP ------+ | |
| | +-> SELECT ----+ | |
| +------- , <-------+ |
+--> ALL PRIVILEGES ---+
3 – Arguments
3.1 – ALL_PRIVILEGES
Specifies that SQL should grant all privileges to the specified
users.
3.2 – ON object-type
Syntax options:
ON DATABASE ALIAS alias
ON TABLE table-name
ON TABLE view-name
ON MODULE module-name
ON FUNCTION routine-name
ON PROCEDURE routine-name
ON SEQUENCE sequence-name
Specifies whether the GRANT statement applies to ACLs for the
named object. You can specify a list of names for any form of
the ON clause. You must qualify a column name with at least the
associated table name.
Specifies whether the GRANT statement applies to ACLs for all
objects of the specified type. If privileges are denied for the
operation on some objects, then the GRANT is aborted.
3.3 – privileges
Type of privileges:
db-privs-ansi
table-privs-ansi
column-privs-ansi
module-privs-ansi
ext-routine-privs-ansi
sequence-privs-ansi
Specifies the set of privileges you want to add to an existing
access privilege set 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, or external
routine. The Privileges Table (in the GRANT) lists the privilege
keywords and their meanings for databases, tables, columns, modules,
external routines and sequences.
3.4 – role-name
The name of a role, such as one created with the CREATE ROLE
statement. If the role name exists as an operating system group
or rights identifier, then Oracle Rdb will create the role
automatically when you issue the GRANT statement. A role that
is created automatically always has the attribute of IDENTIFIED
EXTERNALLY.
3.5 – TO identifier ansi style
Specifies the identifiers for the new or modified access
privilege set entry. Specifying PUBLIC is equivalent to a
wildcard specification of all user identifiers.
In ANSI/ISO-style databases, you are allowed to specify only
single-user user identifiers; no general or system identifiers
are allowed. Access privilege set entries identify only those
users who are common to all groups defined by the individual
identifiers. Users who do not match all identifiers are not
controlled by that entry. ANSI/ISO-style access privilege sets
support only user identifiers.
3.6 – user-identifier
Specifies a user identifier that uniquely identifies each user on
the system.
On OpenVMS, the user identifier consists of the standard OpenVMS
user identification code (UIC), a group name and a member name
(user name). The group name is optional. The user identifier can
be in either numeric or alphanumeric format. The following are
all valid user identifiers that could identify the same user:
K_JONES
[SYSTEM3, K_JONES]
[341,311]
When Oracle Rdb creates an ANSI/ISO-style database, the creator
of the database gets all privileges, and the PUBLIC entry gets no
privileges.
In an ANSI/ISO-style database, you cannot use multiple user
identifiers.
In ANSI/ISO-style user identifiers, the only wildcard allowed is
in the public identifier [*,*].
For more information about identifiers, see the OpenVMS operating
system documentation.
3.7 – WITH_GRANT_OPTION
Allows the user who has been granted a privilege the option of
granting that privilege to other users.
The WITH GRANT OPTION clause specifies that the grantees in the
TO clause may grant the privileges in the privilege list to other
users for as long as they have the privileges. When the privilege
is revoked from the grantee who received the privileges with
the WITH GRANT OPTION clause, the privileges also are revoked
from all the users who received the privileges from that grantee
(unless these users have received the privilege from yet another
user who still has the privilege).
4 – Examples
Example 1: Using PUBLIC as a wildcard
This example shows that PUBLIC translates to [*,*] and can be
used to grant access to the database for all users.
SQL> show protection on database rdb$dbhandle;
Protection on Alias RDB$DBHANDLE
[RDB,DOCS]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP,
DBCTRL,OPERATOR,DBADM,SECURITY,DISTRIBTRAN
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
SQL> grant select on database alias rdb$dbhandle to public;
SQL> show protection on database rdb$dbhandle;
Protection on Alias RDB$DBHANDLE
[RDB,DOCS]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP,
DBCTRL,OPERATOR,DBADM,SECURITY,DISTRIBTRAN
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: SELECT
SQL> commit;
Example 2: Granting a privilege with the WITH GRANT OPTION
clause.
This example shows how the WITH GRANT OPTION causes Rdb to
maintain a separate list of privileges that were granted by a
user with ability to GRANT to others. This extra information
is queries using the SHOW USERS WITH and SHOW USERS GRANTING
commands.
SQL> show protection on table EMPLOYEES;
Protection on Table EMPLOYEES
[RDB,DOCS]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP,
DBCTRL,REFERENCES
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
SQL> grant delete on employees to freeman with grant option;
SQL>
SQL> show protection on table EMPLOYEES;
Protection on Table EMPLOYEES
[RDB,FREEMAN]:
With Grant Option: DELETE
Without Grant Option: NONE
[RDB,DOCS]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP,
DBCTRL,REFERENCES
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
SQL>
SQL> -- Show the list of users who will loose their DELETE
SQL> -- privilege if the privilege is taken away from DOCS
SQL>
SQL> show users with delete on employees from DOCS;
Users granted privileges on table EMPLOYEES by [RDB,DOCS]
[RDB,FREEMAN]
[RDB,DOCS]
[RDB,FREEMAN]
SQL>
SQL> -- Check if anyone on the list has given DELETE to anyone else
SQL>
SQL> show users granting delete on employees to PUBLIC;
Users granting privileges on table EMPLOYEES to [*,*]
No users found
SQL>
Example 3: Granting column privileges
This example shows the two forms of the GRANT column statement
and the effects it has on the target columns.
SQL> -- First show existing column protections
SQL>
SQL> show protection on column CANDIDATES.FIRST_NAME;
Protection on Column CANDIDATES.FIRST_NAME
SQL> show protection on column CANDIDATES.CANDIDATE_STATUS;
Protection on Column CANDIDATES.CANDIDATE_STATUS
SQL>
SQL> -- Show alternate formats for the GRANT column statement
SQL>
SQL> grant update (CANDIDATE_STATUS) on table CANDIDATES to freeman;
SQL> grant update on column CANDIDATES.FIRST_NAME to freeman;
SQL>
SQL> -- Show the effects of the GRANT statements
SQL>
SQL> show protection on column candidates.FIRST_NAME;
Protection on Column CANDIDATES.FIRST_NAME
[RDB,FREEMAN]:
With Grant Option: NONE
Without Grant Option: UPDATE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
SQL> show protection on column candidates.CANDIDATE_STATUS;
Protection on Column CANDIDATES.CANDIDATE_STATUS
[RDB,FREEMAN]:
With Grant Option: NONE
Without Grant Option: UPDATE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
SQL>
Example 4: Granting privileges on a Sequence
This example shows that the set of privileges granted by ALL for
sequences is a small subset of those used for other objects such
as tables and views.
SQL> create sequence EMPLOYEE_ID_GEN;
SQL> grant all on sequence EMPLOYEE_ID_GEN to freeman;
SQL> grant select on sequence EMPLOYEE_ID_GEN to public;
SQL> show protection on sequence EMPLOYEE_ID_GEN;
Protection on Sequence EMPLOYEE_ID_GEN
[RDB,FREEMAN]:
With Grant Option: NONE
Without Grant Option: SELECT,SHOW,ALTER,DROP,DBCTRL,REFERENCES
[RDB,DOCS]:
With Grant Option: SELECT,SHOW,ALTER,DROP,DBCTRL,REFERENCES
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: SELECT
SQL>