Modifies an entry for the specified user name. The modifications
take effect on the next database connection after the ALTER USER
statement is committed.
1 – Environment
You can use the ALTER USER 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;4mUSER[m[1m qqwqq> <username> qqwwqqqqqqqqqqqqqqqqqqqqqqqqwqqq>[m [1m mqq> PUBLIC qqqqqqjmwq> alter-user-opts qqwqj [m [1m [m [1m mqqqqqqqqqq<qqqqqqqqqqj [m (B)0[m[1malter-user-opts = [m [1m [m [1mqwq> [1;4mACCOUNT[m[1m qqqqqwqq> [1;4mLOCK[m[1m qqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m [1mx[m [1mmqq> [1;4mUNLOCK[m[1m qqj[m [1mx[m [1m tq> [1;4mIDENTIFIED[m[1m [1;4mEXTERNALLY[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m [1m tq> [1;4mRENAME[m[1m [1;4mTO[m[1m qqqqqq> <new-username> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m [1m tq> [1;4mCOMMENT[m[1m IS qqwqq> '<string>' qqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqu [m [1m x[m [1m mqqqqqqqq / <qqqqqqqqqqqqj x [m [1m [m [1mtq> [1;4mNO[m[1m [1;4mPROFILE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m [1mmq> [1;4mPROFILE[m[1m <profile_name> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
3 – Arguments
3.1 – ACCOUNT
Syntax options:
ACCOUNT LOCK | ACCOUNT UNLOCK
The ACCOUNT LOCK clause disables access to the database by the
user for whom the ALTER USER statement is being applied. The
ACCOUNT UNLOCK clause allows the user access to the database.
3.2 – COMMENT_IS
Adds a comment about the user. SQL displays the text of the
comment when it executes a SHOW USERS statement. Enclose the
comment in single quotation marks (') and separate multiple lines
in a comment with a slash mark (/).
3.3 – IDENTIFIED_EXTERNALLY
Indicates that the user will be authenticated through the
operating system.
3.4 – PROFILE
Syntax options:
PROFILE | NOPROFILE
Identifies a new profile for assignment to the user and replaces
any previously assigned profile. The specified profile name must
be the name of an existing profile.
NOPROFILE removes any assigned profile from the user. No error is
returned if a profile is not currently assigned.
3.5 – PUBLIC
The PUBLIC user in the database. This entry gives you control
over anonymous users who access the database.
3.6 – RENAME_TO
Changes the user name and, if a security profile exists, assigns
the security profile associated with the old user name to the new
user name. This might be used, for example, when a person's name
changes (as through marriage), and, therefore, his or her account
on the operating system is changed accordingly. The new-username
must not currently exist in the database.
When the ALTER USER command is issued, the existing user name is
removed from the database and replaced with the new-username. If
SECURITY CHECKING is INTERNAL, then subsequent SHOW PROTECTION
statements will display the new name for the user, and all GRANT
and REVOKE statements will require the new-username. The new-
username is not visible to other sessions until the transaction
containing the ALTER USER command is committed.
See the RENAME for further discussion.
3.7 – username
An existing user name in the database.
4 – Examples
Example 1: Renaming a User
SQL> create user KELLYN
cont> identified externally
cont> comment is 'User: Edward "Ned" Kelly';
SQL>
SQL> -- The alternate name must exists at the operating system level
SQL> alter user KELLYN rename to N_KELLY;
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-E-NOSUCHPRF, unknown profile user or role
SQL>
SQL> -- Use the new corporate user-id naming scheme
SQL> alter user KELLYN
cont> rename to NKELLY;
Example 2: Adding a profile to a user
This example creates a new profile that defines the DEFAULT
transaction and then assigns a profile to the user. The next time
the user attaches to the database, the START DEFAULT TRANSACTION
statement will use the defined profile instead of the standard
READ ONLY default.
SQL> create profile READ_COMMITTED
cont> default transaction read write isolation level read committed wait 30;
SQL> show profile READ_COMMITTED
READ_COMMITTED
Default transaction read write wait 30
Isolation level read committed
SQL> alter user JAIN profile READ_COMMITTED;
SQL> show user JAIN;
JAIN
Identified externally
Account is unlocked
Profile: READ_COMMITTED
No roles have been granted to this user