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 authorization-identifier[, authorization-identifier]...
[IDENTIFIED BY password[, password]...]
- 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 privilege | IDENTIFIED BY clause of GRANT DBA statement |
---|
Specified | Not specified |
---|
Already has CONNECT privilege | Has password | Grants DBA privilege and changes to the specified password. | Grants DBA privilege. |
No password | Grants DBA privilege and changes to the specified password. | KFPA11571 error |
No CONNECT privilege | Grants CONNECT privilege, DBA privilege, and the specified password. | KFPA11571 error |
- SCHEMA TO authorization-identifier[, authorization-identifier]...
- 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 authorization-identifier[, authorization-identifier]...
[IDENTIFIED BY password[, password]...]
- 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 privilege | Password character restriction definition | IDENTIFIED BY clause of GRANT CONNECT statement |
---|
Specified | Not Specified |
---|
Already has CONNECT privilege | Has password | Has DBA privilege | -- | Changes to the specified password. | KFPA11571-E error |
No DBA privilege | No | Changes to the specified password. | Changes to No password. |
Yes | Changes to the specified password. | KFPA19634-E error |
No password | No DBA privilege | No | Changes to the specified password. | Changes to No password. |
Yes | Changes to the specified password. | KFPA19634-E error |
No CONNECT privilege | No | Grants CONNECT privilege and password. | Grants CONNECT privilege only with No password. |
Yes | Grants CONNECT privilege and password. | KFPA19634-E error |
Legend:
--: Not applicable.
- RDAREA RDAREA-name [RDAREA-name]...
TO {authorization-identifier[, authorization-identifier]...|PUBLIC}
- 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
- The usage privilege cannot be granted to a replica RDAREA through the use of GRANT RDAREA.
- For details about a GRANT execution condition when the inner replica facility is in use, see the manual HiRDB Version 9 Staticizer Option.
- 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
- A maximum of 16 private user RDAREAs and 1,600 authorization identifiers can be specified.
- The schema definition privilege cannot be granted to users who do not have the DBA or CONNECT privilege.
- 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).
- The DBA privilege includes the CONNECT privilege.
- 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.
- 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.
- The GRANT statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- 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.
- 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.
- 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.
- The DBA privilege cannot be granted to auditors.
- 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
- Grant the DBA privilege to the user whose authorization identifier is USER1 and assign the password PSWD:
GRANT DBA TO USER1 IDENTIFIED BY PSWD
- Grant the schema definition privilege to the user whose authorization identifier is USER2:
GRANT SCHEMA TO USER2
- 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
- Change the password for the user (authorization-identifier: USER3) to ABCD.
GRANT CONNECT TO USER3
IDENTIFIED BY ABCD
- 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
- 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[, access-privilege] |ALL [PRIVILEGES]}
- 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.
- access-privilege::={SELECT|INSERT|DELETE|UPDATE}
- 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.
- ON [authorization-identifier.]table-identifier
- 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.
- TO {authorization-identifier[, authorization-identifier]...|PUBLIC}
- 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
- For details about the GRANT execution conditions under the inner replica facility, see the manual HiRDB Version 9 Staticizer Option.
Notes
- The GRANT statement cannot be specified from an X/Open-compliant UAP running under OLTP.
- 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
- Grant the SELECT access privilege for the stock (STOCK) table to the user whose authorization identifier is USER1:
GRANT SELECT
ON STOCK TO USER1
- Grant all access privileges for the stock table (STOCK) to all users:
GRANT ALL
ON STOCK TO PUBLIC