Allows you to change the role name or add a comment to a role.
1 – Environment
You can use the ALTER ROLE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mALTER[m[1m [1;4mROLE[m[1m qqqq> <role-name> qqwqqqqqqqqqqqqqqqqqqqqqqwqqqqqq>[m [1m mqq> alter-role-opts qqj [m (B)0[m[1malter-role-opts = [m [1m [m [1mqqwqq> [1;4mIDENTIFIED[m[1m [1;4mEXTERNALLY[m[1m qqqqqqqqqqqqqwqqqq> [m [1m tqq> [1;4mNOT[m[1m [1;4mIDENTIFIED[m[1m qqqqqqqqqqqqqqqqqqqqu [m [1m tqq> [1;4mRENAME[m[1m [1;4mTO[m[1m <new-role-name> qqqqqqqqqu [m [1m mqq> [1;4mCOMMENT[m[1m IS qqwqq> 'string' qqwqqqqqj [m [1m mqq<qqq / qqqqqqj [m
3 – Arguments
3.1 – COMMENT_IS
Adds a comment about the role. SQL displays the text of the
comment when it executes a SHOW ROLES statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
3.2 – IDENTIFIED_EXTERNALLY
Specifies whether SQL should inherit roles from the operating
system. If you specify one of these clauses, you must specify
the same clause as was specified when the role was created.
You cannot use the ALTER ROLE statement to change roles from
IDENTIFIED EXTERNALLY to NOT IDENTIFIED or from NOT IDENTIFIED to
IDENTIFIED EXTERNALLY.
The IDENTIFIED EXERNALLY clause indicates that SQL inherits the
roles defined by the facilities of the operating system, such as
OpenVMS rights identifiers.
The NOT IDENTIFIED clause indicates that SQL does not inherit any
roles defined by the facilities of the operating system; instead,
the role is private to the database.
3.3 – NOT_IDENTIFIED
Specifies whether SQL should inherit roles from the operating
system. If you specify one of these clauses, you must specify
the same clause as was specified when the role was created.
You cannot use the ALTER ROLE statement to change roles from
IDENTIFIED EXTERNALLY to NOT IDENTIFIED or from NOT IDENTIFIED to
IDENTIFIED EXTERNALLY.
The IDENTIFIED EXERNALLY clause indicates that SQL inherits the
roles defined by the facilities of the operating system, such as
OpenVMS rights identifiers.
The NOT IDENTIFIED clause indicates that SQL does not inherit any
roles defined by the facilities of the operating system; instead,
the role is private to the database. This is the default.
3.4 – RENAME_TO
Changes an existing role name to a new role name without
changing the privileges granted to the role. You might change
the name of a role that corresponds to a department name when the
department is renamed. For example, if the personnel department
is renamed human resources, you might change the role used by
that department from PERSONNEL to HUMAN_RESOURCES. The new role
name must not already exist in the database. The old role name is
removed from the database when the transaction is committed. The
old role name can be re-created and reused, if desired. If the
new role name is identified externally, then it must exist as an
operating system group or rights identifier.
See the RENAME statement for further discussion.
3.5 – role-name
The name of an existing role (such as one created with the CREATE
ROLE statement).
4 – Example
Example 1: Renaming a Role
SQL> -- Change the name of the role from WRITER to DOCUMENTATION.
SQL> -- Any privileges granted to the role WRITER are transferred to the role
SQL> -- DOCUMENTATION. The role WRITER is deleted from the database.
SQL> ALTER ROLE WRITER
cont> RENAME TO DOCUMENTATION;
SQL> SHOW ROLES;
Roles in database with filename mf_personnel.rdb
DOCUMENTATION