Creates or adds privileges to an entry to the Oracle Rdb access
privilege set, called the access control list (ACL), for a
database, table, view, column, module, or external routine.
Each entry in an ACL consists of an identifier and a list of
privileges assigned to the identifier:
o Each identifier specifies a user or a set of users.
o The list of privileges specifies which operations that user or
user group can perform on the database, table, view, column,
module, or external routine.
When a user tries to perform an operation on a database, SQL
reads the associated ACL from top to bottom, comparing the
identifier of the user with each entry. As soon as SQL finds
the first match, it grants the rights listed in that entry and
stops the search. All identifiers that do not match a previous
entry "fall through" to the entry [*,*] (equivalent to the SQL
keyword PUBLIC). If no entry has the identifier [*,*], then users
with unmatched identifiers are denied all access to the database,
table, view, column, module, or external routine.
For this reason, both the entries and their order in the list are
important.
Under the Oracle Rdb default protection scheme, when you create
a new database, table, view, module, or external routine, you
get all access rights to that object, including DBCTRL. All
other users of that object are given no access rights to it.
For any tables or views created under the Oracle Rdb default
protection scheme, the creator of the table or view receives all
the access rights to the object, including DBCTRL, and all other
users receive no access rights to the object.
The DBCTRL access right enables an object's creator to grant
DBCTRL to other users. See the Oracle Rdb SQL Reference Manual
for information on how you can tailor the default protection for
any new tables that you create within a database.
To remove privileges from or entirely delete an entry to the
Oracle Rdb access privilege set for a database, table, column,
module, or external routine, see the REVOKE.
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
(B)0[m[1;4mGRANT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqk [m [1m [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m [m
[1mmwq> db-privs q> [1;4mON[m[1m [1;4mDATABASE[m[1m [1;4mALIAS[m[1m qwqwqq> <alias> wqwqqqqqqqqqqqwk [m
[1m x x mqqqqq , <qqqj x [m [1mxx [m
[1mx[m [1mmqqqqqq> * qqqqqqj[m [1m [m [1mxx[m
[1m tq> table-privs q> [1;4mON[m[1m qwqqqqqqqqqqwwww> <table-name> qwwwqqqqqqqux [m
[1m x mq> [1;4mTABLE[m[1m qjxxm> <view-name> qqjxx [m [1mxx [m
[1m x xmqqqqqqqq , <qqqqqqjx [m [1mxx [m
[1m x[m [1mmqqqqqqqq> * qqqqqqqqj[m [1m [m [1mxx[m
[1m tq> column-privs qqqq> [1;4mON[m[1m [1;4mCOLUMN[m[1m qqqwq> <column-name> qqwqqqqqqqux [m
[1m x mqqqqqqq , <qqqqqqqqj [m [1mxx [m
[1m tq> module-privs qqqq> [1;4mON[m[1m [1;4mMODULE[m[1m qqwwq> <module-name> qqwwqqqqqqux [m
[1m x xmqqqqqqq , <qqqqqqqqjx xx [m
[1m x[m [1mmqqqqqqq> * qqqqqqqqqqj[m [1mxx[m
[1m tq> ext-routine-privs w> [1;4mON[m[1m [1;4mFUNCTION[m[1m qwwwq> <ext-routine-name> wux [m
[1m x m> [1;4mON[m[1m [1;4mPROCEDURE[m[1m jxmqqqqqqqqqqq , <qqqqqqqjxx [m
[1mx[m [1mmqqqqqqqqqqq> * qqqqqqqqqjx[m
[1m mq> sequence-privs qq> [1;4mON[m[1m [1;4mSEQUENCE[m[1m qww> <sequence-name> qwwqqqqqqu [m
[1m xmqqqqqqqqqqqq , <qqqjx [m [1m x [m
[1mmqqqqqqqqqqqq> * qqqqqj[m [1mx[m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmq> grant-to qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> [m
(B)0[m[1mgrant-to =[m [1m [m
[1m [m
[1mqq> [1;4mTO[m[1m wwq> identifier qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqq> [m
[1m xmq> [1;4mPUBLIC[m[1m qqqqqqj tq> [1;4mAFTER[m[1m qwq> identifier qqqwqu x [m
[1m x x mq> [1;4mPUBLIC[m[1m qqqqqqqj x x [m
[1m x mq> [1;4mPOSITION[m[1m <n> qqqqqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mdb-privs = [m
[1m [m
[1mqqqqwqqqwqwq> [1;4mSELECT[m[1m qqqqqwqwqqqqwq>[m
[1m x x tq> [1;4mINSERT[m[1m qqqqqu x x [m
[1m x x tq> [1;4mOPERATOR[m[1m qqqu x x [m
[1m x x tq> [1;4mDELETE[m[1m qqqqqu x x [m
[1m x x tq> [1;4mCREATE[m[1m qqqqqu x x [m
[1m x x tq> [1;4mALTER[m[1m qqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqu x x [m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqu x x [m
[1m x x tq> [1;4mDBADM[m[1m qqqqqqu x x [m
[1m x x tq> [1;4mSHOW[m[1m qqqqqqqu x x [m
[1m x x tq> [1;4mREFERENCES[m[1m qu x x [m
[1m x x tq> [1;4mUPDATE[m[1m qqqqqu x x [m
[1m x x tq> [1;4mSECURITY[m[1m qqqu x x [m
[1m x x mq> [1;4mDISTRIBTRAN[m[1m j x x [m
[1m x mqqqqqqq , <qqqqqqqqj x [m
[1m mqqqqqqq> [1;4mALL[m[1m PRIVILEGES qqqqj [m
(B)0[m[1mtable-privs= [m
[1m [m
[1mqwqqwqwq> [1;4mSELECT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqwq>[m
[1m x x tq> [1;4mINSERT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mOPERATOR[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDELETE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mCREATE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mALTER[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mSHOW[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mREFERENCES[m[1m wqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x x m> ( w> <column-name> w> ) u x x [m
[1m x x x mqqqqq , <qqqqqqqj x x x [m
[1m x x mq> [1;4mUPDATE[m[1m wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x m> ( w> <column-name> qwqqq> ) j x x [m
[1m x x mqqqqq , <qqqqqqqqj x x [m
[1m x mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqqqq> [1;4mALL[m[1m PRIVILEGES qqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mcolumn-privs= [m
[1m [m
[1mqqqwqwqwq> [1;4mUPDATE[m[1m qqqqqwqwqqwqq>[m
[1m x x mq> [1;4mREFERENCES[m[1m qj x x [m
[1m x mqqqqqqq , <qqqqqqqqj x [m
[1m mqqqqq> [1;4mALL[m[1m PRIVILEGES qqj [m
(B)0[m[1mmodule-privs = [m
[1m [m
[1mqwqqwqwq> [1;4mALTER[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwqq>[m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mEXECUTE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1mx[m [1mx[m [1mtq>[m [1;4mREFERENCES[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m [1mx[m [1m [m
[1m x x mq> [1;4mSHOW[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x x [m
[1m x mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqqqq> [1;4mALL[m[1m PRIVILEGES qqqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mext-routine-privs = [m
[1m [m
[1mqwqqwqwq> [1;4mALTER[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwqq>[m
[1m x x tq> [1;4mDBCTRL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mDROP[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1m x x tq> [1;4mEXECUTE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x [m
[1mx[m [1mx[m [1mtq>[m [1;4mREFERENCES[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mx[m [1mx[m [1m [m
[1m x x mq> [1;4mSHOW[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x x [m
[1m x mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqqqq> [1;4mALL[m[1m PRIVILEGES qqqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1msequence-privs = [m
[1mqwqq> [1;4mALL[m[1m qqwqqqqqqqqqqqqqqqqqwqqqwqq> [m
[1m x mqq> [1;4mPRIVILEGES[m[1m qqj x [m
[1m mwwq> [1;4mALTER[m[1m qqwqwqqqqqqqqqqqqqqqqj [m
[1m xtq> [1;4mDROP[m[1m qqu x [m
[1m xmq> [1;4mSELECT[m[1m qv x [m
[1m mqqqqq , <qqqqqj [m
(B)0[m[1midentifier = [m
[1m [m
[1mqqwqwq> user-identifier qqqqwqwq> [m
[1m x tq> general-identifier qu x [m
[1m x tq> system-identifier qu x [m
[1m x[m [1mmq> role-name qqqqqqqqqqj[m [1mx [m
[1m mqqqqqqqqqqqq + <qqqqqqqqqqqj [m
3 – Arguments
3.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.
3.2 – ALL_PRIVILEGES
Specifies that SQL should grant all privileges in the ACL entry.
3.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
3.4 – ON SEQUENCE sequence-name
Specifies whether the GRANT statement applies to ACLs for the
named sequence or sequences.
3.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.
3.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.
3.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
3.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.)
3.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.
3.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
3.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.
4 – Examples
Example 1: Redeclaring a database to make ACL changes take effect
This example illustrates that GRANT and REVOKE statements do not
take effect until you attach to the database again.
SQL> -- Display the ACL for the EMPLOYEES table:
SQL> SHOW PROTECTION ON TABLE EMPLOYEES;
Protection on Table EMPLOYEES
(IDENTIFIER=[sql,warring],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+
ALTER+DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=[*,*],ACCESS=SELECT+INSERT+UPDATE+DELETE+ALTER+DROP)
SQL>
SQL> -- User warring, the owner of the database, denies
SQL> -- herself INSERT access to the EMPLOYEES table:
SQL> REVOKE INSERT ON TABLE EMPLOYEES FROM warring;
SQL> COMMIT;
SQL>
SQL> -- The SHOW PROTECTION statement displays the change
SQL> -- (INSERT is no longer part of the ACL entry
SQL> -- for warring):
SQL> SHOW PROTECTION ON TABLE EMPLOYEES;
Protection on Table EMPLOYEES
(IDENTIFIER=[sql,warring],ACCESS=SELECT+UPDATE+DELETE+SHOW+CREATE+ALTER+
DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=[*,*],ACCESS=SELECT+INSERT+UPDATE+DELETE+ALTER+DROP)
SQL>
SQL> -- But the change is not yet effective.
SQL> -- User warring can still store rows in the EMPLOYEES table:
SQL> INSERT INTO EMPLOYEES (EMPLOYEE_ID) VALUES ('99999');
1 row inserted
SQL> SELECT EMPLOYEE_ID
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID = '99999';
EMPLOYEE_ID
99999
1 row selected
SQL> ROLLBACK;
SQL>
SQL> -- To make the ACL change take effect, issue another ATTACH statement
SQL> -- to override the current declaration:
SQL> ATTACH 'FILENAME personnel';
This database context has already been declared.
Would you like to override this declaration (No)? Y
SQL>
SQL> -- Now warring cannot insert new rows into the EMPLOYEES table:
SQL> INSERT INTO EMPLOYEES (EMPLOYEE_ID) VALUES ("99999");
%RDB-E-NO_PRIV, privilege denied by database facility
SQL>
SQL> -- A GRANT statement gives all privileges back to warring:
SQL> GRANT ALL ON TABLE EMPLOYEES TO warring;
SQL> COMMIT;
Example 2: Creating an ACL with an SQL command file
The following SQL command file creates an ACL for the default
database by specifying the default alias RDB$DBHANDLE. It uses
two general guidelines for ordering ACL entries:
o The less restrictive the user identifier, the lower on the
list that ACL should go.
o The more powerful the privilege, the higher on the list that
ACL should go.
Because SQL reads the list from top to bottom, you should place
entries with more specific identifiers earlier, and those with
more general ones later. For example, if you place the entry with
the most general user identifier, [*,*], first in the list, all
users match it, and Oracle Rdb grants or denies all the access
rights specified there to all users.
Similarly, if you place the general entry [admin,*] before
the specific entry [admin,ford], SQL matches user [admin,ford]
with [admin,*] and denies the access rights INSERT, UPDATE, and
DELETE, which user [admin,ford] needs.
-- Database Administrator -- needs all privileges.
--
GRANT ALL
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,adams]
POSITION 1;
-- Assistant -- needs to be able to use data definition statements.
--
GRANT SELECT,CREATE,ALTER,DROP
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,clark]
POSITION 2;
-- Operator -- needs to be able to perform database maintenance tasks.
--
GRANT SELECT, ALTER, DBADM
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,lawrence]
POSITION 3;
-- Security Administrator -- needs to specify and show security events
-- audited for a database and review the audit trail.
--
GRANT SECURITY
ON DATABASE ALIAS RDB$DBHANDLE
TO [group2,davis]
POSITION 4;
-- Manager -- needs to be able to use all data manipulation statements.
--
GRANT SELECT,INSERT,UPDATE,DELETE
ON DATABASE ALIAS RDB$DBHANDLE
TO [admin,smith]
POSITION 5;
-- Secretary -- needs to be able to read, write, and delete data.
-- No access to data definition or maintenance.
--
GRANT SELECT,INSERT,UPDATE,DELETE
ON DATABASE ALIAS RDB$DBHANDLE
TO [admin,ford]
POSITION 6;
-- Programmers -- need to perform data definition and data manipulation
-- on some tables and constraints to test application programs.
--
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,ALTER,DROP,REFERENCES
ON DATABASE ALIAS RDB$DBHANDLE
TO PROGRAMMERS
POSITION 7;
-- Clerks -- need to be able only to read data. No access to modify, erase,
-- store, data definition, or maintenance statements.
--
GRANT SELECT
ON DATABASE ALIAS RDB$DBHANDLE
TO [admin,*]
POSITION 8;
-- Deny access to all users not explicitly granted access to the database.
--
REVOKE ALL
ON DATABASE ALIAS RDB$DBHANDLE
FROM PUBLIC
POSITION 9;
Example 3: Granting column access and denying table access
You need the REFERENCES privilege to define constraints that
affect a particular column. You need the UPDATE privilege to
update data in a column. A user with the UPDATE privilege for
a table automatically receives the UPDATE privilege for all
columns in that table. To update a column, you must have the
UPDATE privilege either for the column or for the table. However,
a database administrator can restrict UPDATE privileges by
defining them only for columns users should be able to update,
and then removing the UPDATE privilege from the table entry.
Because current salary is sensitive information, you might want
to restrict the ability to update this amount.
The following example prevents user [admin,ford] from updating
any column in the SALARY_HISTORY table except SALARY_START and
SALARY_END. For instance, user [admin,ford] cannot update the
SALARY_AMOUNT column.
SQL> GRANT UPDATE ON COLUMN SALARY_HISTORY.SALARY_START
cont> TO [admin,ford];
SQL> GRANT UPDATE ON COLUMN SALARY_HISTORY.SALARY_END
cont> TO [admin,ford];
SQL> --
SQL> REVOKE UPDATE ON TABLE SALARY_HISTORY FROM [admin,ford];
SQL> --
SQL> COMMIT;
SQL> --
SQL> SHOW PROTECTION ON TABLE SALARY_HISTORY;
Protection on Table SALARY_HISTORY
(IDENTIFIER=[grp2,jones],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+
ALTER+DROP+DBCTRL+DBADM+REFERENCES+SECURITY+DISTRIBTRAN)
(IDENTIFIER=[*,*],ACCESS=NONE)
SQL> --
SQL> SHOW PROTECTION ON COLUMN SALARY_HISTORY.SALARY_START;
Protection on Column SALARY_HISTORY.SALARY_START
(IDENTIFIER=[admin,ford],ACCESS=UPDATE)
(IDENTIFIER=[*,*],ACCESS=NONE)
Example 4: Granting SELECT Privilege to All Users for a Sequence
SQL> SHOW PROTECTION ON SEQUENCE EMPID
Protection on Sequence EMPID
(IDENTIFIER=[RDB,STRAUTS],ACCESS=SELECT+SHOW+ALTER+DROP+DBCTRL)
(IDENTIFIER=[*,*],ACCESS=NONE)
SQL> GRANT SELECT ON SEQUENCE EMPID TO PUBLIC;
SQL> SHOW PROTECTION ON SEQUENCE EMPID;
Protection on Sequence EMPID
(IDENTIFIER=[RDB,STRAUTS],ACCESS=SELECT+SHOW+ALTER+DROP+DBCTRL)
(IDENTIFIER=[*,*],ACCESS=SELECT)
Example 5: Granting INSERT ON TABLE Privilege to a Role
SQL> SHOW PROTECTION ON TABLE JOBS
Protection on Table JOBS
(IDENTIFIER=[250,254],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+
DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=PUBLIC,ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+DROP
+DBADM+REFERENCES)
SQL> CREATE ROLE ADMINISTRATOR;
SQL> GRANT INSERT ON TABLE JOBS TO ADMINISTRATOR AFTER [250,254];
SQL> SHOW PROTECTION ON TABLE JOBS
Protection on Table JOBS
(IDENTIFIER=[250,254],ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+
DROP+DBCTRL+DBADM+REFERENCES)
(IDENTIFIER=ADMINISTRATOR,ACCESS=INSERT)
(IDENTIFIER=PUBLIC,ACCESS=SELECT+INSERT+UPDATE+DELETE+SHOW+CREATE+ALTER+DROP
+DBADM+REFERENCES)
Example 6: Allowing All Access to a User
SQL> -- Allow all access to user JAIN
SQL> GRANT SELECT ON DATABASE ALIAS * to jain;
SQL> GRANT SELECT ON TABLE * to jain;
SQL> GRANT EXECUTE ON MODULE * to jain;
SQL> GRANT EXECUTE ON PROCEDURE * to jain;
SQL> GRANT EXECUTE ON FUNCTION * to jain;
Example 7: Automatically Creating a User While Granting
Privileges
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> SHOW USERS
Users in database with filename mf_personnel.rdb
tsmith
jstuart
SQL> GRANT ALL ON DATABASE ALIAS RDB$DBHANDLE TO CDAY;
%RDB-W-META_WARN, metadata successfully updated with the reported warning
-RDMS-W-PRFCREATED, some users or roles were created
SQL> SHOW USERS
Users in database with filename mf_personnel.rdb
tsmith
jstuart
cday