Oracle ALTER PROFILE

Summary: in this tutorial, you will learn how to use the Oracle ALTER PROFILE statement to modify the resource limit and password management of a profile.

Introduction to Oracle ALTER PROFILE statement

The ALTER PROFILE statement allows you to add, change, or delete a resource limit or password management parameter in a user profile.

The following illustrates the syntax of the ALTER PROFILE statement:

ALTER PROFILE profile_name
LIMIT { resource_parameters | password_parameters};
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of an existing user profile that you want to change after the ALTER PROFILE keywords.
• Then, specify the resource or password parameters after the LIMIT clause. The resource and password parameters are the same as described in the CREATE PROFILE tutorial.

The ALTER PROFILE statement cannot remove a limit from the DEFAULT profile.

The changes that you made to a profile will only affect users, who were previously assigned the profile,  in their subsequent sessions, not in their current ones.

To execute the ALTER PROFILE statement, your account needs to have ALTER PROFILE system privilege.

Oracle ALTER PROFILE statement example

First, create a new profile called supper with unlimited consecutive login attempts:

CREATE PROFILE super LIMIT
    FAILED_LOGIN_ATTEMPTS UNLIMITED;Code language: SQL (Structured Query Language) (sql)

Next, create a user named joe:

CREATE USER joe IDENTIFIED BY abcd1234;

GRANT connect, super TO joe;Code language: SQL (Structured Query Language) (sql)

Then, use the user joe to login to the Oracle Database:

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

After that, change the super profile so that any user who is assigned this profile becomes locked for 1 day after 3 consecutive unsuccessful login attempts:

ALTER PROFILE super LIMIT
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LOCK_TIME 1;Code language: SQL (Structured Query Language) (sql)

Also, define a new limit of 3 concurrent sessions for the super profile:

ALTER PROFILE super LIMIT 
    SESSIONS_PER_USER 5; Code language: SQL (Structured Query Language) (sql)

Note that these changes only take effect on the user joe in the next login.

In this tutorial, you have learned how to use the Oracle ALTER PROFILE statement to modify the resource limit and password management of a user profile.