 |
|
Oracle Database Tips by Donald Burleson |
Altering Users
To create a user, you must have the ALTER
USER privilege. You can alter users with the Oracle Enterprise
Manager GUI or at the command line in SQL*Plus or Server Manager (SVRMGRL).
The command-line syntax for altering a user is:
See Code Depot
For example:
See Code Depot
Once a user has been created, the only thing
that you cannot alter for that user is the user name. The password,
default tablespace, temporary tablespace, and the quota on a
tablespace, profile, default role, status, and password expiration
can all be altered by someone with the ALTER USER system
privilege.
Each user can alter the Oracle password you
initially assigned to that user upon creation, provided that user is
not identified externally (via the operating system). In addition to
the end user, users with the ALTER USER system privilege can issue
the ALTER USER command to change the user's password. The use of
operating system authentication can also be changed by a user with
the ALTER USER system privilege. Any changes to the password will
take effect the next time that user logs in to Oracle.
When you change the default tablespace for a
user, all future objects created by that user will be created in the
new default tablespace you designated (unless otherwise specified by
the user at the time the object was created). Remember, the user
must have a quota in the tablespace to create new objects in that
tablespace. If a user reaches the maximum number of bytes assigned
(quota), only a user with the ALTER USER system privileges will be
able to increase the quota limit for the user.
The undocumented keyword VALUES allows you to
specify the encrypted value of a user's password. This can be handy
if the DBA needs to temporarily become a user. You simply capture
the encrypted value of the user's password from the SYS.USER$ table,
alter the user to a password you know, do what you need to do, then
reset the password using this command:
ALTER USER
username IDENTIFIED BY VALUES 'encrypted_password';
Note: As of 12c, the create user xxx identified by values clause will throw a ORA-02153 error.
www.dba-oracle.com/oracle_scripts.htm |
and include the URL for the page.
