GRANT Format 1 (Grant privileges)

Function

GRANT format 1 grants to users the DBA, schema definition, CONNECT, and private user RDAREA usage privileges (see subsection (1) below); it also grants access privileges to users (see subsection (2) below).

(1) GRANT DBA (grant DBA privilege), GRANT SCHEMA (grant schema definition privilege), GRANT CONNECT (grant CONNECT privilege), and GRANT RDAREA (grant RDAREA usage privilege)

Privileges

Users with the DBA privilege

A user with the DBA privilege can grant the DBA, schema definition, CONNECT, and private user RDAREA usage privileges.

Users with the CONNECT privilege

A user with the CONNECT privilege can change passwords.

Format

GRANT {DBA TO authorization-identifier[, authorization-identifier]...
        [IDENTIFIED BY password[, password]...]
      |SCHEMA TO authorization-identifier[, authorization-identifier]...
      |CONNECT TO authorization-identifier[, authorization- identifier]...
         [IDENTIFIED BY password[, password]...]
      |RDAREA RDAREA-name [RDAREA-name]...
          TO {authorization-identifier[, authorization-identifier]...|PUBLIC}}

Operands

DBA TO
Specifies that the DBA privilege is to be granted to one or more users.
authorization-identifier[, authorization-identifier]...
Specifies the authorization identifiers of the users to whom the DBA privilege is to be granted.
IDENTIFIED BY password[, password]...
Specifies passwords for the users to whom the DBA privilege is to be granted.
The following table indicates the relationship between the IDENTIFIED BY clause of the GRANT DBA statement and the user privilege.

Table 3-45 Relationship between the IDENTIFIED BY clause of the GRANT DBA statement and the user privilege

User privilegeIDENTIFIED BY clause of GRANT DBA statement
SpecifiedNot specified
Already has CONNECT privilegeHas passwordGrants DBA privilege and changes to the specified password.Grants DBA privilege.
No passwordGrants DBA privilege and changes to the specified password.KFPA11571 error
No CONNECT privilegeGrants CONNECT privilege, DBA privilege, and the specified password.KFPA11571 error
SCHEMA TO
Specifies that the schema definition privilege is to be granted to one or more users.
authorization-identifier[, authorization-identifier]...
Specifies the authorization identifiers of the users to whom the schema definition privilege is to be granted.
CONNECT TO
Specifies that the CONNECT privilege is to be granted to one or more users.
authorization-identifier[, authorization-identifier]...
Specifies the authorization identifiers of the users to whom the CONNECT privilege is to be granted.
IDENTIFIED BY password[, password]...
Specifies passwords for the users to whom the CONNECT privilege is to be granted.
The following table indicates the relationship between the IDENTIFIED BY clause and of the GRANT CONNECT statement the user privilege.

Table 3-46 Relationship between the IDENTIFIED BY clause of the GRANT CONNECT statement and the user privilege

User privilegePassword character restriction definitionIDENTIFIED BY clause of GRANT CONNECT statement
SpecifiedNot Specified
Already has CONNECT privilegeHas passwordHas DBA privilege--Changes to the specified password.KFPA11571-E error
No DBA privilegeNoChanges to the specified password.Changes to No password.
YesChanges to the specified password.KFPA19634-E error
No passwordNo DBA privilegeNoChanges to the specified password.Changes to No password.
YesChanges to the specified password.KFPA19634-E error
No CONNECT privilegeNoGrants CONNECT privilege and password.Grants CONNECT privilege only with No password.
YesGrants CONNECT privilege and password.KFPA19634-E error

Legend:

--: Not applicable.

RDAREA RDAREA-name[, RDAREA-name]
Specifies the names of the RDAREAs for which the usage privilege is to be granted.
authorization-identifier[, authorization-identifier]...
Specifies the authorization identifiers of the users to whom the RDAREA usage privilege is to be granted for the specified RDAREAs.
PUBLIC
Specifies that the specified RDAREAs are to be public user RDAREAs.

Common rules

  1. The usage privilege cannot be granted to a replica RDAREA through the use of GRANT RDAREA.
  2. For details about a GRANT execution condition when the inner replica facility is in use, see the manual HiRDB Version 9 Staticizer Option.
  3. If the password character limit enhancement facility is used, this command checks the password character limit during the execution of the GRANT DBA or GRANT CONNECT statement.

Notes

  1. A maximum of 16 private user RDAREAs and 1,600 authorization identifiers can be specified.
  2. The schema definition privilege cannot be granted to users who do not have the DBA or CONNECT privilege.
  3. The RDAREA usage privilege for public user RDAREAs cannot be granted to individual users, nor can RDAREAs granted to individual users be defined as public user RDAREAs (PUBLIC cannot be specified for such an RDAREA).
  4. The DBA privilege includes the CONNECT privilege.
  5. When the DBA privilege is granted to a user, a password must also be specified for that user. Even when the DBA privilege is granted, a user who does not have a password cannot use the DBA privilege.
  6. A user can change a password assigned to that user by specifying the GRANT CONNECT option. In this case, the individual who issues the GRANT statement can change his or her password solely on the basis of his or her CONNECT privilege.
  7. The GRANT statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  8. When specifying more than one authorization identifier in the GRANT DBA and GRANT CONNECT statements and granting privileges to multiple users simultaneously, you cannot partially omit the password specified in the IDENTIFIED BY clause; either specify passwords in a number equal to the number of specified authorization identifiers or omit the entire IDENTIFIED BY clause. However, if no IDENTIFIED BY clauses are specified in the GRANT statements, a specification without a password that attempts to register a DBA will result in an error (see Table 3-45 and Table 3-46), and all GRANT statements will be disabled. In this case, execute the command by specifying the error-generating user with the IDENTIFIED BY clause in another GRANT statement.
  9. If multiple authorization identifiers are specified in the GRANT DBA and GRANT CONNECT statements while using the password character limit facility, resulting in privileges being granted to multiple users at that same time, if even one person enters a specification that violates the password character limit, an error will result (see Table 3-45 and Table 3-46), and all GRANT statements will be disabled.
  10. The GRANT DBA statement cannot be used to grant DBA privileges to or change passwords for a user who is in a password-invalid account lock state. In this case, the GRANT CONNECT statement should be used to clear the password-invalid account lock state, and then the GRANT DBA statement should be executed.
  11. The DBA privilege cannot be granted to auditors.
  12. If the RDAREA usage privilege for an RDAREA is granted solely to an auditor, other users cannot be granted the RDAREA usage privilege for that RDAREA.

Examples

  1. Grant the DBA privilege to the user whose authorization identifier is USER1 and assign the password PSWD:

    GRANT DBA TO USER1 IDENTIFIED BY PSWD

  2. Grant the schema definition privilege to the user whose authorization identifier is USER2:

    GRANT SCHEMA TO USER2

  3. Grant the CONNECT privilege to the user whose authorization identifier is USER3 and assign the password PSWD:

    GRANT CONNECT TO USER 3
        IDENTIFIED BY PSWD

  4. Change the password for the user (authorization-identifier: USER3) to ABCD.

     GRANT CONNECT TO USER3
         IDENTIFIED BY ABCD

  5. Grant the RDAREA usage privilege (for RDAREAs RDA1 and RDA2) to users whose authorization identifiers are USER4, USER5, and USER6:

    GRANT RDAREA RDA1,RDA2
     TO USER4,USER5,USER6

  6. Define RDAREA RDA3 as a public user RDAREA:

    GRANT RDAREA RDA3 TO PUBLIC

(2) GRANT access privilege (grant access privileges)

Privileges

Owner of the table
This user can grant to other users his or her access privilege with respect to base tables that the user owns, and view tables owned by user and defined from his or her base table, or view table. However, if the user receives the SELECT privilege with respect to a table owned by another user and defines a view table, subsequently he or she cannot grant this access privilege to other users.

Format

GRANT {access-privilege[, access-privilege] |ALL [PRIVILEGES]}
 ON [authorization-identifier.]table-identifier
 TO {authorization-identifier[, authorization-identifier]...|PUBLIC}

access-privilege::={SELECT|INSERT|DELETE|UPDATE}

Operands

access-privilege
Specifies an access privilege that is to be granted for a specified table. Identical access privileges cannot be specified.
ALL [PRIVILEGES]
Specifies that all applicable access privileges are to be granted for the specified table.
If the specified table is a view table, the ALL option grants all access privileges possessed by the owner of the view table.
SELECT
Specifies that the SELECT privilege is to be granted.
INSERT
Specifies that the INSERT privilege is to be granted.
DELETE
Specifies that the DELETE privilege is to be granted.
UPDATE
Specifies that the UPDATE privilege is to be granted.
authorization-identifier
Specifies the authorization identifier of the owner of the table for which the specified access privileges are to be granted.
To grant access privileges to public views, specify the word PUBLIC in authorization-identifier, in uppercase characters enclosed in double quotation marks (").
table-identifier
Specifies the name of the table for which the specified access privileges are to be granted.
authorization-identifier
Specifies the authorization identifier of a user to whom the specified access privileges for the specified table are to be granted. A maximum of 1,600 user authorization identifiers can be specified. Duplicate authorization identifiers are not allowed.
PUBLIC
Specifies that the specified access privileges to the specified table are to be granted to all users.

Common rule

  1. For details about the GRANT execution conditions under the inner replica facility, see the manual HiRDB Version 9 Staticizer Option.

Notes

  1. The GRANT statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. INSERT, UPDATE, or DELETE privileges on audit trails or view tables based on an audit trail table cannot be granted to users other than an auditor.

Examples

  1. Grant the SELECT access privilege for the stock (STOCK) table to the user whose authorization identifier is USER1:

    GRANT SELECT
    ON STOCK TO USER1

  2. Grant all access privileges for the stock table (STOCK) to all users:

    GRANT ALL
    ON STOCK TO PUBLIC