Manage User Profiles with Autonomous AI Database on Dedicated Exadata Infrastructure

You can create and alter user profiles in Autonomous AI 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 AI Database has restrictions on the profile clause. See Limitations on the Use of 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 Database User Privileges.

See CREATE PROFILE in Oracle Database 19c SQL Language Reference or Oracle Database 26ai SQL Language Reference for information on using CREATE 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 AI 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 AI 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 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 AI Database for more information.

Manage Password Complexity on Autonomous AI 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 AI 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 in Oracle Database 19c Security Guide or Oracle Database 26ai Security Guide 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 in Oracle Database 19c SQL Language Reference or Oracle Database 26ai SQL Language Reference for more information.

See Managing the Complexity of Passwords in Oracle Database 19c Security Guide or Oracle Database 26ai Security Guide 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.

Related Content

Manage Autonomous AI Database Users on Dedicated Exadata Infrastructure