GRANT Format 1 (Grant privileges)

Function

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

The access privilege is granted to roles under the Sun Java System Directory Server linkage facility.

(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.
Table 3-42 shows the relationship between the IDENTIFIED BY clause and the user privilege.

Table 3-42 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.
Table 3-43 shows the relationship between the IDENTIFIED BY clause and the user privilege.

Table 3-43 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[Figure]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:

[Figure]: 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 Staticizer Option Version 7.
  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 the entire IDENTIFIED BY clause is omitted in the GRANT CONNECT statement, a specification that registers a DBA without a password causes an error (see Tables 3-42 and 3-43), nullifying the entire GRANT statement. In this case, execute the command by specifying the error-generating user with the IDENTIFIED BY clause in another GRANT statement.
  9. If, in the use of the password character limit facility, more than one authorization identifier is specified in both GRANT DBA and GRANT CONNECT statements, and, at the same time, privileges are granted to multiple users, and if even a single user makes a specification that violates the password character limit, an error occurs (see Tables 3-42 and 3-43), and the entire GRANT statement is nullified.
  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.

Notes on using the directory server linkage facility

Under the directory server linkage facility, users' CONNECT privileges are managed by the directory server, not by HiRDB. HiRDB manages privilege information other than the CONNECT privilege. For details about the directory server linkage facility, see the HiRDB Version 8 System Operation Guide.

  1. The CONNECT privilege cannot be granted to a user by using GRANT; it is granted by registering the user by specifying password attributes on the directory server.
  2. The DBA privilege cannot be granted to users who are not registered with the directory server; similarly, it cannot be granted to roles.
  3. When granting the DBA privilege, in the IDENTIFIED BY clause specify the password that is registered with the directory server. The password should not be omitted. Table 3-44 shows the relationship between the IDENTIFIED BY clause of GRANT DBA and directory server user privileges.

    Table 3-44 Relationship between the IDENTIFIED BY clause of GRANT DBA and directory server user privileges

    Directory server user privilegesIDENTIFIED BY clause of GRANT DBA
    SpecifiedNot specified
    User already registered with directory server*The DBA privilege is granted, and the specified password is registered with HiRDBThe DBA privilege is granted and the user privilege is registered with HiRDB without a password
    User not registered with directory server (no CONNECT privilege)KFPA11559-E errorKFPA11559-E error

    * Users without password attributes are registered as users without the CONNECT privilege.


  4. Granting the DBA privilege does not affect any information that is stored on the directory server.
  5. The schema definition privilege is not granted to users who are not registered with the directory server, nor is it granted to roles.
  6. If the schema definition privilege is granted to a user who does not have any privileges, the schema definition privilege is granted, and it is registered with HiRDB without a password.
  7. The RDAREA usage privilege can also be granted to users who are not registered with the directory server, but not to roles.

Notes on changing the directory server linkage facility to the unavailable status

  1. If the directory server linkage facility is made unavailable, the user's CONNECT privilege is controlled by HiRDB, not by the directory server.
  2. If, after the directory server linkage facility is rendered unavailable, there is a user with No-password DBA privileges, that user cannot exercise the No-password DBA privileges. If such a user attempts to use the No-password DBA privileges, a KFPA11552-E error may occur. To prevent registration of users with No-password DBA privileges, when the directory server linkage facility is used, holders of DBA privileges should be assigned passwords that are registered in the directory server.

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 and foreign tables that the user owns, and view tables owned by user and defined from his or her base table, view table, or foreign 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.
Under the directory server linkage facility, the access privilege can be granted to users, or roles who are registered with the directory server.

Format

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

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.
Under the directory server linkage facility, the access privilege is granted to all roles.
GROUP role-name
Specifies the role name to which the access privilege is granted. The role name is one of the information items managed by the Sun Java System Directory Server.
The following rules apply to role names:
  1. A maximum of 1,600 or role names can be specified.
  2. Each role name specified must be unique.
  3. Role names can be specified only when the directory server linkage facility is in use.

Common rule

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

Notes

  1. The GRANT statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. When the Directory Server linkage facility is used, you can also grant privileges to users and roles that are not registered in Directory Server. However, to access a table, the user must be registered in Directory Server. Also, a privilege granted to a role cannot be used to create a view table.
  3. In the case of granting a privilege to a user, if the privilege has already been granted to a role that has the same name as the user, do not grant the privilege to that user. In the case of granting a privilege to a role, if the privilege has already been granted to a user ID that has the same name as the role, do not grant the privilege to that role.
  4. 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

  3. Grant the privilege (UPDATE privilege) to update the inventory table (STOCK) to a role (role-name: ADMIN):

    GRANT UPDATE
       ON STOCK TO GROUP ADMIN