ALTER USER statement is used to change the properties of an existing user. Some of the most important settings that this statement can change are the user’s password, assigned tablespace, space quota, profile, role, and locking/unlocking accounts. Keep in mind that there will be restrictions on the use of this statement due to database privileges.
Note: If the database user wants to change his own, the use of this statement is allowed without any additional privileges.
Resetting Oracle password using the ALTER USER… Identified By
The complete statement to reset a database user password is:
ALTER USER username IDENTIFIED BY newpassword replace oldpassword
If it is the first time that the database user is setting its password, the REPLACE clause can be omitted. This clause can also be omitted, if the database user has the ALTER USER privilege, else it must always use the REPLACE clause.
The Identified clause is also used to define global or external users. The statements are the following:
ALTER TABLE username IDENTIFIED globally AS ‘directory_dn’ ALTER TABLE username IDENTIFIED EXTERNALLY AS ‘certificate_dn’
Global and external users are already defined when the database user is created. The ‘Certificate_DN’ in the IDENTIFIED EXTERNALLY clause refers to the name located in the User’s PKI certificate. The ‘Directory_DN’ in the IDENTIFIED GLOBALLY clause refers to the user name in the enterprise directory service.
ALTER USER account unlock
This statement can be used to unlock database users (accounts) and the following steps set a basic guide for it:
- Log into the database with credentials which possess “sys-admin” privileges.
- Execute the following statement:
ALTER USER username account unlock;
For Example, to unblock a user named Operator, the statement syntax is the following:
ALTER USER Operator account unlock;
- If the database user remembers his/her password, this will allow the database login once the account is unlocked:
Using the same user from the last example,
Note: The locking clause also exists and can be imposed on the user, with the ACCOUNT LOCK privileges.
To force the password for a database user to expire, the statement is:
ALTER USER username password expire;
For Example, to force the user named Operator to expire, the statement syntax is the following:
ALTER USER Operator password expire;
After the statement is executed, the user must change the password immediately before logging in to the database.
Modifying user characteristics with the ALTER USER Statement
As mentioned before, other user characteristics can be changed using this statement. Some statements that come with this statement are:
ALTER USER username define TABLESPACE TablespaceName;
For example, to assign the user named Operator to the DBA_DAT Tablespace, the statement syntax is the following:
ALTER USER Operator define TABLESPACE DBA_DAT;
The DEFINE TABLESPACE clause specifies the tablespace assigned to the user. This tablespace will override the default tablespace defined to the user in the database.
ALTER USER username define TEMPORARY TABLESPACE tablespacename;
The DEFINE TEMPORARY TABLESPACE clause specifies a temporary tablespace assigned to the user.
Note: To assign a temporary tablespace it must be defined as one and using a standard block size.
For example, to assign the user named Operator to the TMP_DAT Temporary Tablespace, the statement syntax is the following:
ALTER USER Operator define TEMPORARY TABLESPACE TMP_DAT;
Note that the Temporary tablespaces are only for temporary objects that will last during the active session, allowing big transactions to use this temporary space for it process, differing from the permanent tablespace, which holds tables, functions, stored procedures, etc.
ALTER USER username PROFILE profilename;
The PROFILE clause allows the database user to access all of the resources that are defined from the profileName.
For example, to assign the user named Operator the profile Operators, the statement syntax is the following:
ALTER USER Operator PROFILE Operators;
ALTER USER username DEFAULT ROLE ALL EXCEPT rolename;
The DEFAULT ROLE clause assigns the database user all the roles given by default while logging in. To define a default user role, assign the role with the GRANT clause. The DEFAULT ROLE ALL EXCEPT clause defines that some roles will not be assigned as a default role for the user, for its next login.
For example, to assign the user named Operator all the roles except the DBA_ROLE, the statement syntax is the following:
ALTER USER Operator DEFAULT ROLE ALL EXCEPT DBA_ROLE;
Still need some help? Connect with a live SQLExpert here for some 1 on 1 help. Your first session is always free!