1 GRANT_ANSI 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. 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 GRANT -----------> ----------+ +-------------- <-------------+ ++-> db-privs-ansi -------> ON DATABASE ALIAS ++-> -++-------------++ | |+--- , <-----+| || | +----> * ------+ || +-> table-privs-ansi ----> ON +----->----+---+++-> -+--------+| | +-> TABLE -+ ||+-> --+ || | |+-------- , <---------------+| | +--------> * ----------------+| +-> column-privs-ansi ---> ON COLUMN ---+> --+--------------+| | +------- , <-------+ || +-> module-privs-ansi ---> ON MODULE --++-> --++------------+| | |+------- , <--------+| || | +-------> * ----------+ || +-> ext-routine-privs-ansi -+> ON FUNCTION --+-++-> -++-+| | +> ON PROCEDURE -+ |+--------- , <---------+| || | +---------> * -----------+ || +-> sequence-privs-ansi ---> ON SEQUENCE -++> -++---------+| |+------- , <--------+| | +-------> * ----------+ | +----------------------------<---------------------------------------------+ +-> 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 +---------------------------+ | | | | | +> ( +> +> ) -+ | | | | | +----- , <-------+ | | | | | +-> UPDATE -+-------------------------------+ | | | | +> ( +> --+--> ) -+ | | | | +----- , <---------+ | | | +------------------- , <------------------------+ | +------> 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 ---+ 2 Arguments 3 ALL_PRIVILEGES Specifies that SQL should grant all privileges to the specified users. 3 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 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 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 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 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 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). 2 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>