1 – AFTER
Syntax options:
AFTER identifier | AFTER PUBLIC
Specifies the position of the entry within the ACL to be modified
or created.
With the AFTER or POSITION argument, you can specify the position
in the list after which SQL searches for an ACL entry with an
identifier that matches the one specified in the TO clause of the
GRANT statement.
Following are specifics about the AFTER and POSITION arguments:
o In the AFTER argument, the identifier specifies the entry in
the ACL after which SQL begins its search for the entry to be
modified or created. If none of the entries in the ACL has an
identifier that matches the identifier specified in the AFTER
argument, SQL generates an error and the statement fails.
Starting after the entry specified by the identifier in the
AFTER argument, SQL searches the entries in the ACL. If an
entry has an identifier that matches the identifier specified
by the TO clause of the GRANT statement, SQL creates a new
entry that contains only those privileges specified in the
GRANT statement. SQL retains only the entry appearing first in
the ACL, and deletes any entries with duplicate identifiers.
If none of the entries has an identifier that matches the
identifier specified by the TO clause of the GRANT statement,
SQL creates a new ACL entry immediately following the
identifier specified in the AFTER argument.
Specifying PUBLIC is equivalent to a wildcard specification of
all user identifiers.
o In the POSITION argument, the integer specifies the earliest
relative position in the ACL of the entry to be modified or
created.
Starting with the position specified by the POSITION argument,
SQL searches the entries in the ACL. If an entry has an
identifier that matches the identifier specified by the
TO clause of the GRANT statement, SQL creates a new entry
that contains only those privileges specified in the GRANT
statement. SQL retains only the entry appearing first in the
ACL, and deletes any entries with duplicate identifiers.
If none of the entries has an identifier that matches the
identifier specified by the TO clause of the GRANT statement,
SQL creates a new entry for that identifier at the relative
position specified in the POSITION argument (even if an entry
before the position at which SQL began its search had an
identifier that matched).
If you specify a position higher than the number of entries in
the list, SQL places the entry last in the ACL. For example,
if you specify position 12 and there are only 10 entries in
the list, the new entry is placed in position 11 and given
that position number.
o If you omit the AFTER or POSITION argument, SQL searches
the entire ACL for an identifier list that matches the one
specified in the TO clause of the GRANT statement. If it finds
a match, it modifies the ACL entry by adding those privileges
specified in the privilege list that are not already present.
If there is no match, SQL creates a new entry at the beginning
of the ACL.
2 – ALL_PRIVILEGES
Specifies that SQL should grant all privileges in the ACL entry.
3 – general-identifier
Identifies groups of users on the system and are defined by
the OpenVMS system manager in the system rights database. The
following are possible general identifiers:
DATAENTRY
SECRETARIES
MANAGERS
4 – ON SEQUENCE sequence-name
Specifies whether the GRANT statement applies to ACLs for the
named sequence or sequences.
5 – ON
Syntax options:
ON DATABASE ALIAS *
ON TABLE *
ON TABLE *
ON MODULE *
ON FUNCTION *
ON PROCEDURE *
ON SEQUENCE *
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.
6 – POSITION n
Specifies the position of the entry within the ACL to be modified
or created.
With the AFTER or POSITION argument, you can specify the position
in the list after which SQL searches for an ACL entry with an
identifier that matches the one specified in the TO clause of the
GRANT statement.
Following are specifics about the AFTER and POSITION arguments:
o In the AFTER argument, the identifier specifies the entry in
the ACL after which SQL begins its search for the entry to be
modified or created. If none of the entries in the ACL has an
identifier that matches the identifier specified in the AFTER
argument, SQL generates an error and the statement fails.
Starting after the entry specified by the identifier in the
AFTER argument, SQL searches the entries in the ACL. If an
entry has an identifier that matches the identifier specified
by the TO clause of the GRANT statement, SQL creates a new
entry that contains only those privileges specified in the
GRANT statement. SQL retains only the entry appearing first in
the ACL, and deletes any entries with duplicate identifiers.
If none of the entries has an identifier that matches the
identifier specified by the TO clause of the GRANT statement,
SQL creates a new ACL entry immediately following the
identifier specified in the AFTER argument.
Specifying PUBLIC is equivalent to a wildcard specification of
all user identifiers.
o In the POSITION argument, the integer specifies the earliest
relative position in the ACL of the entry to be modified or
created.
Starting with the position specified by the POSITION argument,
SQL searches the entries in the ACL. If an entry has an
identifier that matches the identifier specified by the
TO clause of the GRANT statement, SQL creates a new entry
that contains only those privileges specified in the GRANT
statement. SQL retains only the entry appearing first in the
ACL, and deletes any entries with duplicate identifiers.
If none of the entries has an identifier that matches the
identifier specified by the TO clause of the GRANT statement,
SQL creates a new entry for that identifier at the relative
position specified in the POSITION argument (even if an entry
before the position at which SQL began its search had an
identifier that matched).
If you specify a position higher than the number of entries in
the list, SQL places the entry last in the ACL. For example,
if you specify position 12 and there are only 10 entries in
the list, the new entry is placed in position 11 and given
that position number.
o If you omit the AFTER or POSITION argument, SQL searches
the entire ACL for an identifier list that matches the one
specified in the TO clause of the GRANT statement. If it finds
a match, it modifies the ACL entry by adding those privileges
specified in the privilege list that are not already present.
If there is no match, SQL creates a new entry at the beginning
of the ACL.
7 – privileges
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
8 – role-name
The name of a role, such as one created with the CREATE ROLE
statement or one that can be created automatically. (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.)
9 – TO
Syntax options:
TO identifier | TO PUBLIC
Specifies the identifiers for the new or modified ACL entry.
Specifying PUBLIC is equivalent to a wildcard specification of
all user identifiers.
You can specify four types of identifiers:
o User identifiers
o General identifiers
o System-defined identifiers
o Role names
You can specify more than one identifier by combining them
with plus signs (+). Such identifiers are called multiple
identifiers. They identify only those users who are common to all
the groups defined by the individual identifiers. Users who do
not match all the identifiers are not controlled by that entry.
For instance, the multiple identifier SECRETARIES + INTERACTIVE
specifies only members of the group defined by the general
identifier SECRETARIES that are interactive processes. It does
not identify members of the SECRETARIES group that are not
interactive processes.
The following arguments briefly describe the three types of
identifiers. For more information about identifiers, see your
operating system documentation.
10 – system-identifier
System-defined identifiers are automatically defined by the
system when the rights database is created at system installation
time. System-defined identifiers are assigned depending on the
type of login you execute. The following are all valid system-
defined identifiers:
BATCH
NETWORK
INTERACTIVE
LOCAL
DIALUP
REMOTE
11 – user-identifier
Identifies each user on the system.
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]
You can use the asterisk (*) wildcard character as part of a
user identifier. For example, if you want to specify all users in
a group, you can enter [system3, *] as the identifier.
When Oracle Rdb creates a database, it automatically creates an
ACL entry with the identifier [*,*] (also known as PUBLIC), which
specifies the privileges given to all users on the system.
You cannot use more than one user identifier in a multiple
identifier.