Use oracle profile to restrict ORACLE users

The PROFILE file of ORACLE is a method to restrict the resources used by database users. For example, you can control the CPU that can be used by sessions or SQL statements, and control your password management policies.
After the database is created, the system has a default profile named DEFAULT. If no special configuration is specified, the default profile used by the user when the user is created is DEFAULT.

View dba_profiles to find out which profiles are in the database.
SQL> select distinct profile from dba_profiles;
PROFILE
--------------------
MONITORING_PROFILE
DEFAULT
View all profiles
SQL> select * from dba_profiles order by PROFILE;
PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------------------------------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

Parameter description
1. Restrict database resources
{SESSIONS_PER_USER: number of concurrent sessions allowed by each user name
| CPU time that a CPU_PER_SESSION session can use in total, in the unit of 1% seconds
| CPU time allowed for one SQL call (parsing, execution, and retrieval) of CPU_PER_CALL
| CONNECT_TIME limits the session connection time, in minutes
| IDLE_TIME: the time allowed for idle sessions. The unit is minute.
| LOGICAL_READS_PER_SESSION restricts the session's reading of data blocks. The unit is block.
| LOGICAL_READS_PER_CALL restricts the reading of data blocks by SQL calls. The unit is block.
| COMPOSITE_LIMIT"
} {Integer | UNLIMITED | DEFAULT}
| PRIVATE_SGA limits the allocation of private space in the Shared Pool of the session in SGA {size_clause | UNLIMITED | DEFAULT}
}
2. Restrict passwords
{Number of failed attempts before the FAILED_LOGIN_ATTEMPTS account is locked
| Number of days in which the PASSWORD_LIFE_TIME password can be used. The unit is day. The default value is 180 days.
| Interval at which PASSWORD_REUSE_TIME passwords can be reused (combined with PASSWORD_REUSE_MAX)
| Maximum number of PASSWORD_REUSE_MAX password changes (combined with PASSWORD_REUSE_TIME)
| PASSWORD_LOCK_TIME indicates the number of days that the user is locked after the number of failed attempts is exceeded. The default value is 1 day.
| PASSWORD_GRACE_TIME how many days can I use the original password after the password expires?
} {Expr | UNLIMITED | DEFAULT}
| PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT}
}

Modify profile: alter profile [resource file name] limit [Resource Name] unlimited;
For example, alter profile default limit failed_login_attempts 100;

Delete PROFILE: drop profile [resource file name] [CASCADE];
If the created PROFILE has been authorized to a user, use CASCADE to unbind the corresponding restriction. After revoking the restriction information, use the default PROFILE to restrict the user.

Set the effective time of the PROFILE Parameter
1. Password restrictions in all profiles of a user take effect immediately without any restrictions. From this we can see that Oracle attaches great importance to user passwords.
2. Resource restrictions in all profiles of a user are related to the resource_limit parameter settings. This parameter takes effect when it is set to TRUE. If it is set to FALSE (default), any value is invalid.
SQL> show parameter resource_limit
NAME TYPE VALUE
--------------------------------------
Resource_limit boolean FALSE

Others:
1. Set the SQL column display width: col column name for size;
For example, col username for a20;
2. Permission control policies must be fully tested before use to ensure a thorough understanding of each restriction rule, in addition, the restrictions in each step are recorded in a specific database maintenance manual.

Author: "One mi sunshine©®"