Oracle Cloud Infrastructure Documentation

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

    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, the login is handled as follows:

  • The password complexity restrictions are the same as the restrictions for any user on Autonomous Database.

  • If the user's password violates the password complexity requirements, the account is expired with a 30-day grace period. In this case the user is required to change their password before the grace period ends.

Note

Profile assignments for users with profile ORA_PROTECTED_PROFILE and ORA_ADMIN_PROFILE cannot be modified.

The following users share the ORA_PROTECTED_PROFILE profile and the profile assignment of these users cannot be changed:

  • ADBSNMP
  • ADB_APP_STORE
  • DCAT_ADMIN
  • GGADMIN
  • RMAN$CATALOG

The ADMIN user is assigned to ORA_ADMIN_PROFILE.

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.

Parent topic: Manage Users

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 user passwords.

Note

The minimum password length for a user specified PVF is 8 characters and must include at least one upper case letter, one lower case letter and one numeric character. The minimum password length for the DEFAULT profile is 12 characters (the DEFAULT profile uses the CLOUD_VERIFY_FUNCTION PVF). The password cannot contain the username.

Oracle recommends using a minimum password length of 12 characters. If you define a profile's PVF, and set the minimum password length to less than 12 characters, then tools such as Oracle Database Security Assessment Tool (DBSAT) and Qualys report this as a database security risk.

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:

  • If you specify a user profile, the minimum password length depends on how you define the associated PVF, as follows:

    • If a PVF is defined, then the minimum password length enforced is 8 characters with at least one upper case letter, one lower case letter and one numeric character. The password cannot contain the username.

    • If the PVF is defined as NULL, then the minimum password length enforced is 8 characters with at least one upper case letter, one lower case letter and one numeric character. The password cannot contain the username.

    • If the profile does not have a PVF defined, then the DEFAULT profile’s PVF (CLOUD_VERIFY_FUNCTION) is assigned and the minimum password length enforced is 12 characters.

  • If you specify a Password Verify Function (PVF) that is more strict than the default CLOUD_VERIFY_FUNCTION, then the new verify function is used.

  • 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.

Gradual Database Password Rollover for Applications

An application can change its database passwords without an administrator having to schedule downtime.

To accomplish this, you can associate a profile having a non-zero limit for the PASSWORD_ROLLOVER_TIME password profile parameter, with an application schema. This allows the database password of the application user to be altered while allowing the older password to remain valid for the time specified by the PASSWORD_ROLLOVER_TIME limit. During the rollover period of time, the application instance can use either the old password or the new password to connect to the database server. When the rollover time expires, only the new password is allowed.

See Managing Gradual Database Password Rollover for Applications for more information.