Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Database Administration / Oracle ALTER USER

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;

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;

Log in to the Oracle Database using the dolphin user:

Enter user-name: dolphin@pdborcl Enter password: <dolphin password>

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;

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

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

ALTER USER dolphin ACCOUNT UNLOCK;

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;

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

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';

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;

and assign it to the user dolphin:

ALTER USER dolphin PROFILE ocean;

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;

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

CREATE ROLES rescue; GRANT CREATE TABLE, CREATE VIEW TO rescue;

Second, grant this role to dolphin:

GRANT rescue TO dolphin;

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;

Here is the output:

ROLE --------- RESCUE

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

CREATE ROLE super; GRANT ALL PRIVILEGES TO super;

Fifth, grant the role super to the user dolphin:

GRANT super TO dolphin;

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

ALTER USER dolphin DEFAULT ROLE super;

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;

The following shows the output:

ROLE --------- SUPER

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

  • Was this tutorial helpful?
  • YesNo
Previous Oracle REVOKE
Next Oracle DROP USER

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.