Oracle Profiles Version 21c |
---|
General Information | |||||||
Library Note |
|
||||||
Purpose | Profiles are associated with individual users and define two different categories of behaviour. The first is Kernel Resources which allow a definition of resource availability to be defined across a category consisting of one or more user schemas. The second category is Password Resources and allows for a specific password behaviour to be assigned to a group of one or more user schemas. | ||||||
Dependencies |
|
||||||
Source | The default profile is created by {$ORACLE_HOME}/rdbms/admin/denv.bsq Modifications that improve governance, compliance, and security can be found, commented out, in {$ORACLE_HOME}/rdbms/admin/utlpwdmg.sql |
||||||
System Privileges |
|
||||||
RESOURCE_LIMIT=TRUE is required for resource limiting portions of the profile. Password limiting functionality is not affected by this parameter. | resource_limit = TRUE |
||||||
conn sys@pdbdev |
|||||||
Kernel Resources | |||||||
COMPOSITE_LIMIT |
Maximum weighted sum of: CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. If this limit is exceeded, Oracle aborts the session and returns an error.
|
||||||
ALTER PROFILE developer LIMIT composite_limit 5000000; |
|||||||
CONNECT_TIME |
Allowable connect time per session in minutes
|
||||||
ALTER PROFILE developer LIMIT connect_time 600; |
|||||||
CPU_PER_CALL | Maximum CPU time per call (100ths of a second)cpu_per_call <value | UNLIMITED | DEFAULT> |
||||||
ALTER PROFILE developer LIMIT cpu_per_call 3000; |
|||||||
CPU_PER_SESSION | Maximum CPU time per session (100ths of a second)cpu_per_session <value | UNLIMITED | DEFAULT> |
||||||
ALTER PROFILE developer LIMIT cpu_per_session UNLIMITED; |
|||||||
IDLE_TIME | Allowed idle time before user is disconnected (minutes)idle_time <value | UNLIMITED | DEFAULT> |
||||||
ALTER PROFILE developer LIMIT idle_time 20; |
|||||||
LOGICAL_READS_PER_CALL | Maximum number of database blocks read per calllogical_reads_per_call <value | UNLIMITED | DEFAULT> |
||||||
ALTER PROFILE developer LIMIT logical_reads_per_call 1000; |
|||||||
LOGICAL_READS_PER_SESSION |
Maximum number of database blocks read per session
|
||||||
ALTER PROFILE developer LIMIT logical_reads_per_session UNLIMITED; |
|||||||
PRIVATE_SGA | Maximum integer bytes of private space in the SGA (useful for systems using multi-threaded server MTS)private_sga <value | UNLIMITED | DEFAULT> |
||||||
ALTER PROFILE developer LIMIT private_sga 15K; |
|||||||
SESSIONS_PER_USER | Number of concurrent multiple sessions allowed per usersessions_per_user <value | UNLIMITED | DEFAULT> |
||||||
ALTER PROFILE developer LIMIT sessions_per_user 1; |
|||||||
Password Resources | |||||||
FAILED_LOGIN_ATTEMPTS | The number of failed attempts to log in to the user account before the account is locked
|
||||||
ALTER PROFILE developer LIMIT failed_login_attempts 3; |
|||||||
INACTIVE_ACCOUNT_TIME | Automatically lock a database user account that has not logged in to the database in a specified number of days |
||||||
ALTER PROFILE developer LIMIT inactive_account_time 35; |
|||||||
PASSWORD_GRACE_TIME | The number of days during which a login is allowed but a warning is issued |
||||||
ALTER PROFILE developer LIMIT password_grace_time 10; |
|||||||
PASSWORD_LIFE_TIME | The number of days the same password can be used for authentication |
||||||
ALTER PROFILE developer LIMIT password_life_time 60; |
|||||||
PASSWORD_LOCK_TIME | The number of days an account will be locked after the specified number of consecutive failed login attempts defined |
||||||
ALTER PROFILE developer LIMIT password_lock_time 30; |
|||||||
PASSWORD_REUSE_MAX | Number of times a password can be reused |
||||||
ALTER PROFILE developer LIMIT password_reuse_max 99; |
|||||||
PASSWORD_REUSE_TIME | Days between password reuses |
||||||
ALTER PROFILE developer LIMIT password_reuse_time 9999; |
|||||||
PASSWORD_ROLLOVER_TIME (new 21c) ![]() |
Days during which both old and new passwords are both valid. This works with passwords only: not certificates. |
||||||
ALTER PROFILE default LIMIT password_rollover_time
0; |
|||||||
Password Verification | |||||||
Sample script for creating a password verify function | {ORACLE_HOME}/rdbms/admin/utlpwdmg.sql it is important to note that the 12c password verify function has a different name and enhanced functionality |
||||||
PASSWORD_VERIFY_FUNCTION Verifies a passwords for length, content, and complexity |
password_verify_function <function_name | NULL | DEFAULT> |
||||||
ALTER PROFILE developer LIMIT |
|||||||
Changing passwords with a password verify function | The function requires the old and new passwords so password changes can not be done with ALTER USER. Password changes should be performed with the SQL*Plus PASSWORD command or through a stored procedure that requires the correct inputs. | ||||||
Create Profiles | |||||||
View existing profile The values highlighted in yellow have been reduced from Oracle 19c corresponding to a slight improvement in default security (based on OCI cloud with version 20.3). In 19c FAILED_LOGIN_ATTEMPTS was 3, PASSWORD_LIFE_TIME was 180, PASSWORD_RESUSE_MAX and PASSWORD_REUSE_TIME were unlimited. PASSWORD_ROLLOVER_TIME is new in 21c. |
col profile format a20 |
||||||
Create Center for Internet Security (CIS) profile | CREATE PROFILE <profile_name> LIMIT |
||||||
-- log into cdb$root |
|||||||
Alter Profile | |||||||
Alter profile syntax | ALTER PROFILE <profile_name> LIMIT <profile_item_name> <value>; |
||||||
ALTER PROFILE developer LIMIT FAILED_LOGIN_ATTEMPTS 3; |
|||||||
Modify Oracle default profile for Center for Internet Security (CIS) | ALTER PROFILE DEFAULT LIMIT |
||||||
Modify Oracle default profile for DOD Security Technical Implementation Guidelines (STIG) The STIG profile is created as a local object with container = current. Exception is made in PDB code similar to the DEFAULT profile to make sure the STIG profile is created in every container during DB creation time. |
ALTER PROFILE DEFAULT LIMIT |
||||||
Assign Profile | |||||||
Assign During User Creation | CREATE USER <user_name> |
||||||
CREATE USER uwclass |
|||||||
Assign Profile After User Creation | ALTER USER <user_name> |
||||||
ALTER USER uwclass PROFILE developer; |
|||||||
Drop Profile | |||||||
Drop Profile without Users | DROP PROFILE <profile_name>; |
||||||
DROP PROFILE developer; |
|||||||
Drop Profile with associated Users | DROP PROFILE <profile_name> CASCADE; |
||||||
DROP PROFILE developer CASCADE; |
|||||||
Password Verify Functions | |||||||
Note | As of version 12.2 comes with 3 different password verify functions implemented in $ORACLE_HOME/rdbms/admin/catpvf.sql. While two other Password Verision functions exist in 12c+ the only function worth using is the name named ora12c_stig_verify_function because the STIG function is no more onerous than what a bank or credit company would expect for an online account. |
||||||
ora_complexity_check | If not null, each of the following parameters specifies the minimum number of characters of the corresponding type.
|
||||||
ora_string_distance | Calculates the Levenshtein distance between two strings 's' and 't'. The Levenshtein distance between two words is the minimum number of single-character edits (insertion, deletion, substitution) required to change one word into the other. |
||||||
ora12c_stig_verify_function | This function is provided to give stronger password complexity function that would take into consideration recommendations from Department of Defense Database Security Technical Implementation Guide (STIG) v1 r2 released on 22-Jan-2016. | ||||||
ora12c_strong_verify_function | Provided from 12c onwards for stringent password check requirements | ||||||
ora12c_verify_function | Makes the minimum complexity checks like the minimum length of the password, password not same as the username, etc. The user may enhance this function according to the need. This function must be created in SYS schema. connect sys/<password> as sysdba before running the script | ||||||
verify_function | Sets the default password resource parameters. This script needs to be run to enable the password features. However the default resource parameters can be changed based on the need. A default password complexity function is also provided.
This function makes the minimum complexity checks like the minimum length of the password, password not same as the username, etc. The user may enhance this function according to the need. This function must be created in SYS schema. connect sys/<password> as sysdba before running the script |
||||||
verify_function_11g | Makes the minimum complexity checks like the minimum length of the password, password not same as the username, etc. The user may enhance this function according to the need. | ||||||
Password verification related view | SQL> desc ku$_pwdvfc_view |
||||||
Password Verify Functions | |||||||
Note | If what you are looking for is "real" security then there are three places where you need to intersect your activities with your profile. The three solutions are listed below. | ||||||
First: Tighten the password verification function to eliminate common words by integrating your Microsoft Word dictionary as shown here. This sample is based upon the ORA12C_STIG_VERIFY function. | -- Step 1: download words.txt from https://github.com/dwyl/english-words
|
||||||
Second: Eliminate use of the DEFAULT profile. Profiles should be custom crafted based upon the work that a user or mechid needs to perform. For the highest security start with the MORGAN profile at right. | ALTER PROFILE DEFAULT LIMIT |
||||||
Third: Limit resources so that a query cannot return more data than is legitimately required to perform a specific job. The number of times the job requirement is literally, "must be able to, in a single query, read every row in every table" is vanishingly small. | conn sys@pdbdev as sysdba |
Related Topics |
Database Security |
Built-in Packages |
Built-in Functions |
Consumer Groups |
Fine Grained Access Control |
Product User Profiles |
Roles |
Users |
What's New In 21c |
What's New In 23c |
This site is maintained by Dan Morgan. Last Updated: | This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved | |||||||||
|
||||||||||