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.
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.
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.
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.
- 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. - Gradual Database Password Rollover for Applications
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.
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 aINVOKER
rights PVF is provided as input toCREATE
orALTER
PROFILE
, thenORA-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
orALTER
PROFILE
, thenORA-28220
error is thrown. -
A PVF cannot be altered or dropped by a non-ADMIN user. That is, any user with the
CREATE
orDROP
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 andDROP ANY PROCEDURE
system privilege are audited for PVF security. See thePROCEDURES
list in Listings of System and Object Privileges for more information.
See Managing the Complexity of Passwords for more information.
Parent topic: Manage User Profiles with Autonomous Database
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.
Parent topic: Manage User Profiles with Autonomous Database