Oracle ALTER USER

Summary: in this tutorial, you will learn how to use the Oracle ALTER USER statement to modify the authentication or database resource of a database user.

The ALTER USER statement allows you to change the authentication or database resource characteristics of a database user.

Generally speaking, to execute the ALTER USER statement, your account needs to have the ALTER USER system privilege. However,  you can change your own password using the ALTER USER statement without having the ALTER USER system privilege.

Let’s create a user named dolphin and grant the CREATE SESSION system privilege to dolphin:

CREATE USER dolphin IDENTIFIED BY abcd1234;

GRANT CREATE SESSION TO dolphin;
Code language: SQL (Structured Query Language) (sql)

1) Using Oracle ALTER USER statement to change the password for a user

The following example uses the ALTER USER statement to change the password for the user dolphin:

ALTER USER dolphin IDENTIFIED BY xyz123;
Code language: SQL (Structured Query Language) (sql)

Log in to the Oracle Database using the dolphin user:

Enter user-name: dolphin@pdborcl
Enter password: <dolphin password>
Code language: SQL (Structured Query Language) (sql)

The user dolphin should be able to authenticate to the Oracle Database using the new password xyz123

2) Using Oracle ALTER USER statement to lock/unlock a user

This example uses the ALTER USER statement to lock the user dolphin:

ALTER USER dolphin ACCOUNT LOCK;
Code language: SQL (Structured Query Language) (sql)

If you use the user dolphin to log in to the Oracle Database, you should see a message indicating that the user is locked:

Enter user-name: dolphin@pdborcl
Enter password: <dolphin password>
ERROR:
ORA-28000: the account is locked
Code language: SQL (Structured Query Language) (sql)

To unlock the user dolphin, you use the following statement:

ALTER USER dolphin ACCOUNT UNLOCK;
Code language: SQL (Structured Query Language) (sql)

Now, the user dolphin should be able to log in to the Oracle Database.

3) Using Oracle ALTER USER statement to set user’s password expired

To set the password of the user dolphin expired, you use the following statement:

ALTER USER dolphin PASSWORD EXPIRE;
Code language: SQL (Structured Query Language) (sql)

When you use the user dolphin to log in to the database, Oracle issues a message indicating that the password has expired and requests for the password change as follows:

Enter user-name: dolphin@orclpdb
Enter password: <dolphin password>
ERROR:
ORA-28001: the password has expired


Changing password for dolphin
New password: <new password>
Retype new password: <new password>
Password changed
Code language: SQL (Structured Query Language) (sql)

4) Using Oracle ALTER USER statement to set the default profile for a user

This statement returns the profile of the user dolphin:

SELECT 
    username, 
    profile 
FROM
    dba_users 
WHERE 
    username ='DOLPHIN';
Code language: SQL (Structured Query Language) (sql)

When you create a new user without specifying a profile, Oracle will assign the DEFAULT profile to the user.

Let’s create a new user profile called ocean:

CREATE PROFILE ocean LIMIT
    SESSIONS_PER_USER          UNLIMITED 
    CPU_PER_SESSION            UNLIMITED 
    CPU_PER_CALL               3000 
    CONNECT_TIME               60;
Code language: SQL (Structured Query Language) (sql)

and assign it to the user dolphin:

ALTER USER dolphin
PROFILE ocean;
Code language: SQL (Structured Query Language) (sql)

Now, the default profile of the user dolphin is ocean.

5) Using Oracle ALTER USER statement to set default roles for a user

Currently, the user dolphin has no assigned roles as shown in the output of the following query when executing from the dolphin’s session:

SELECT * FROM session_roles;
Code language: SQL (Structured Query Language) (sql)

First, create a new role called rescue from the user OT‘s session:

CREATE ROLES rescue;

GRANT CREATE TABLE, CREATE VIEW TO rescue;
Code language: SQL (Structured Query Language) (sql)

Second, grant this role to dolphin:

GRANT rescue TO dolphin;
Code language: SQL (Structured Query Language) (sql)

Third, use the user dolphin to log in to the Oracle Database. The default role of the user dolphin is rescue now.

SELECT * FROM session_roles;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

ROLE
---------
RESCUE    
Code language: SQL (Structured Query Language) (sql)

Fourth, create another role called super and grant all privileges to this role:

CREATE ROLE super;

GRANT ALL PRIVILEGES TO super;
Code language: SQL (Structured Query Language) (sql)

Fifth, grant the role super to the user dolphin:

GRANT super TO dolphin;
Code language: SQL (Structured Query Language) (sql)

Sixth, set the default role of the user dolphin to super:

ALTER USER dolphin DEFAULT ROLE super;
Code language: SQL (Structured Query Language) (sql)

Seventh, disconnect the current session of the user dolphin and log in to the Oracle Database again. The default role of the user dolphin should be super as shown in the output of the following query:

SELECT * FROM session_roles;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

ROLE
---------
SUPER
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle ALTER USER to change the authentication or database resource of a database user.