1  GRANT
   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.
 

2  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)0GRANT qqqqqqqqqqqqqqqqqqqqqqqqqk                                     
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj                                     
 mwq> db-privs q> ON DATABASE ALIAS qwqwqq> <alias> wqwqqqqqqqqqqqwk  
  x                                  x mqqqqq , <qqqj x           xx  
  x                                  mqqqqqq> * qqqqqqj           xx
  tq> table-privs q> ON qwqqqqqqqqqqwwww> <table-name> qwwwqqqqqqqux  
  x                      mq> TABLE qjxxm> <view-name> qqjxx       xx  
  x                                  xmqqqqqqqq , <qqqqqqjx       xx  
  x                                  mqqqqqqqq> * qqqqqqqqj       xx
  tq> column-privs qqqq> ON COLUMN qqqwq> <column-name> qqwqqqqqqqux  
  x                                   mqqqqqqq , <qqqqqqqqj       xx  
  tq> module-privs qqqq> ON MODULE qqwwq> <module-name> qqwwqqqqqqux  
  x                                  xmqqqqqqq , <qqqqqqqqjx      xx  
  x                                  mqqqqqqq> * qqqqqqqqqqj      xx
  tq> ext-routine-privs w> ON FUNCTION qwwwq> <ext-routine-name> wux  
  x                     m> ON PROCEDURE jxmqqqqqqqqqqq , <qqqqqqqjxx  
  x                                      mqqqqqqqqqqq> * qqqqqqqqqjx
  mq> sequence-privs qq> ON SEQUENCE qww> <sequence-name> qwwqqqqqqu  
                                      xmqqqqqqqqqqqq , <qqqjx      x  
                                      mqqqqqqqqqqqq> * qqqqqj      x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  
 mq> grant-to qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> 



 (B)0grant-to =                                                         
                                                                    
 qq> TO wwq> identifier qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqq>  
        xmq> PUBLIC qqqqqqj tq> AFTER qwq> identifier qqqwqu x      
        x                   x          mq> PUBLIC qqqqqqqj x x      
        x                   mq> POSITION <n> qqqqqqqqqqqqqqj x    
        mqqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqqqqqqqj    



 (B)0db-privs =                          
                                     
 qqqqwqqqwqwq> SELECT qqqqqwqwqqqqwq>
     x   x tq> INSERT qqqqqu x    x  
     x   x tq> OPERATOR qqqu x    x  
     x   x tq> DELETE qqqqqu x    x  
     x   x tq> CREATE qqqqqu x    x  
     x   x tq> ALTER qqqqqqu x    x  
     x   x tq> DROP qqqqqqqu x    x  
     x   x tq> DBCTRL qqqqqu x    x  
     x   x tq> DBADM qqqqqqu x    x  
     x   x tq> SHOW qqqqqqqu x    x  
     x   x tq> REFERENCES qu x    x  
     x   x tq> UPDATE qqqqqu x    x  
     x   x tq> SECURITY qqqu x    x  
     x   x mq> DISTRIBTRAN j x    x  
     x   mqqqqqqq , <qqqqqqqqj    x  
     mqqqqqqq> ALL PRIVILEGES qqqqj  



 (B)0table-privs=                                           
                                                        
 qwqqwqwq> SELECT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqwq>
  x  x tq> INSERT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> OPERATOR qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> DELETE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> CREATE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> ALTER qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> DROP qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> DBCTRL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> SHOW qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x tq> REFERENCES wqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x x              m> ( w> <column-name> w> ) u x x  
  x  x x                   mqqqqq , <qqqqqqqj    x x x  
  x  x mq> UPDATE wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x  
  x  x            m> ( w> <column-name> qwqqq> ) j x x  
  x  x                 mqqqqq , <qqqqqqqqj         x x  
  x  mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqqqj x  
  mqqqqqq> ALL PRIVILEGES qqqqqqqqqqqqqqqqqqqqqqqqqqqj  



 (B)0column-privs=                   
                                 
 qqqwqwqwq> UPDATE qqqqqwqwqqwqq>
    x x mq> REFERENCES qj x  x   
    x mqqqqqqq , <qqqqqqqqj  x   
    mqqqqq> ALL PRIVILEGES qqj   



 (B)0module-privs =                                         
                                                        
 qwqqwqwq> ALTER qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwqq>
  x  x tq> DBCTRL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x tq> DROP qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x tq> EXECUTE qqqqqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x tq> REFERENCES qqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x mq> SHOW qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x  x   
  x  mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqj  x   
  mqqqqqq> ALL PRIVILEGES qqqqqqqqqqqqqqqqqqqqqqqqqqj 



 (B)0ext-routine-privs =                                    
                                                        
 qwqqwqwq> ALTER qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwqq>
  x  x tq> DBCTRL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x tq> DROP qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x tq> EXECUTE qqqqqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x tq> REFERENCES qqqqqqqqqqqqqqqqqqqqqqqqqu x  x   
  x  x mq> SHOW qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x  x   
  x  mqqqqqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqqqqj  x   
  mqqqqqq> ALL PRIVILEGES qqqqqqqqqqqqqqqqqqqqqqqqqqj 



 (B)0sequence-privs =                        
 qwqq> ALL qqwqqqqqqqqqqqqqqqqqwqqqwqq>  
  x          mqq> PRIVILEGES qqj   x     
  mwwq> ALTER qqwqwqqqqqqqqqqqqqqqqj     
   xtq> DROP  qqu x                      
   xmq> SELECT qv x                      
   mqqqqq , <qqqqqj                      



 (B)0identifier =                          
                                       
 qqwqwq> user-identifier qqqqwqwq>     
   x tq> general-identifier qu x       
   x tq> system-identifier  qu x       
   x mq> role-name qqqqqqqqqqj x 
   mqqqqqqqqqqqq + <qqqqqqqqqqqj       

 

2  Arguments
 

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

3  ON_SEQUENCE_sequence-name

   Specifies whether the GRANT statement applies to ACLs for the
   named sequence or sequences.
 

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

2  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