Manage User Profiles with Autonomous Database

You can create and alter user profiles in Autonomous Database. After you create or alter a profile, you can specify the profile clause with CREATE USER or ALTER USER. You can also import existing user profiles from another environment with Oracle Data Pump Import.

Note:

Autonomous Database has restrictions on the profile clause. See Restrictions for SQL Commands for information on CREATE PROFILE and ALTER PROFILE restrictions.

To add, modify, or remove a password parameter in a profile, including the DEFAULT profile you must have the ALTER PROFILE system privilege.

  1. To add or alter a profile, as the ADMIN user run either CREATE PROFILE or ALTER PROFILE. For example:
    CREATE PROFILE new_profile
      LIMIT PASSWORD_REUSE_MAX 10
      PASSWORD_LOCK_TIME 5;

    Note:

    If you are not the ADMIN user, then you must have CREATE PROFILE privilege to run CREATE PROFILE. If you run ALTER PROFILE, then you must have ALTER PROFILE privilege.
  2. Use the new or altered profile with a CREATE USER or ALTER USER command. For example:
    CREATE USER new_user IDENTIFIED BY password PROFILE new_profile;
    GRANT CREATE SESSION TO new_user;

This creates new_user with profile new_profile and with connect privileges. The new_user can now connect to the database and run queries. To grant additional privileges to users, see Manage User Privileges on Autonomous Database - Connecting with a Client Tool.

See CREATE PROFILE for information on using CREATE PROFILE or ALTER PROFILE.

You can import existing profiles created in other environments using Oracle Data Pump Import (impdp). Any existing profile association with database users is preserved after importing into Autonomous Database. When a newly created user, created from an Oracle Data Pump import, attempts to login for the first time, if the user's password violates the password complexity requirements then the account is expired and the user is required to change their password before the user can successfully login to Autonomous Database. These password restrictions are the same as the restrictions for any user on Autonomous Database.

Note:

Profile assignments for the ADMIN user cannot be modified.

When you create or alter a profile, you can specify a Password Verification Function (PVF) to manage password complexity. See Manage Password Complexity on Autonomous Database for more information.

Manage Password Complexity on Autonomous Database

You can create a Password Verify Function (PVF) and associate the PVF with a profile to manage the complexity of passwords.

Note:

If you specify a weaker or NULL Password Verify Function (PVF) for a new profile, then the default PVF CLOUD_VERIFY_FUNCTION applies. If the verify function you specify is more strict than the default CLOUD_VERIFY_FUNCTION, then the new verify function is used.

For example, to specify a PVF for a profile, use the following command:

CREATE PROFILE example_profile LIMIT PASSWORD_VERIFY_FUNCTION ADMIN.EXAMPLE_PVF

If the profile is created or altered by any user other than the ADMIN user, then you must grant the EXECUTE privilege on the PVF. If you create a PVF and the password check fails, the database reports the ORA-28219 error.

You can specify an Oracle supplied PVF, from one of the following:

  • CLOUD_VERIFY_FUNCTION (this is the default password verification function for Autonomous Database):

    This function checks for the following requirements when users create or modify passwords:

    • The password must be between 12 and 30 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.

    • The password cannot contain the username.

    • The password cannot be one of the last four passwords used for the same username.

    • The password cannot contain the double quote (") character.

    • The password must not be the same password that is set less than 24 hours ago.

  • ORA12C_STIG_VERIFY_FUNCTION

    This function checks for the following requirements when users create or modify passwords:

    • The password has at least 15 characters.

    • The password has at least 1 lower case character and at least 1 upper case character.

    • The password has at least 1 digit.

    • The password has at least 1 special character.

    • The password differs from the previous password by at least 8 characters.

    See ora12c_stig_verify_function Password Requirements for more information.

Note the following restrictions for a Password Verification Function (PVF) that you create and assign to a profile:

  • A PVF that you create must be created as a DEFINER RIGHTS PL/SQL function. If a INVOKER rights PVF is provided as input to CREATE or ALTER PROFILE, then ORA-28220 error is thrown.

  • Any PVF that you create must be created in the ADMIN user schema. If a non-ADMIN user owned PVF is provided as input to CREATE or ALTER PROFILE, then ORA-28220 error is thrown.

  • A PVF cannot be altered or dropped by a non-ADMIN user. That is, any user with the CREATE or DROP ANY PROCEDURE privilege is not allowed to alter or drop a PVF.

  • If the PVF associated with a profile is dropped, then any attempt to change the password for a user who uses the PVF in their profile throws the error ORA-7443. Users can still login when the PVF associated with their profile is dropped. However, if a user's password is expired and the PVF is dropped, then the user cannot login.

    To recover from the ORA-7443 error, the ADMIN user must recreate the dropped PVF and assign it to the profile, or assign an existing PVF to the profile. This allows a user change their password and login.

  • The CREATE ANY PROCEDURE system privilege and DROP ANY PROCEDURE system privilege are audited for PVF security. See the PROCEDURES list in Listings of System and Object Privileges for more information.

See Managing the Complexity of Passwords for more information.