About Oracle's profiles

First, about Oracle's profiles

The profiles file is a configuration collection of password and resource restrictions, including CPU time, I/O usage, idle time, connection time, number of concurrent sessions, password policy, and so on resource usage profile can control session level or statement invocation level. Oracle's own default is much more unrestricted, and the profile is specified when the user is created.

Ii. Detailed profile parameters

2. Sql> SELECT * from dba_profiles where profile=' DEFAULT ';
4. Profile Resource_name Resource_type LIMIT
6. ------------------------------ -------------------------------- ------------- ------------------------------------ ----
8. DEFAULT Composite_limit KERNEL UNLIMITED
10. DEFAULT Sessions_per_user KERNEL UNLIMITED
12. DEFAULT cpu_per_session KERNEL UNLIMITED
14. DEFAULT Cpu_per_call KERNEL UNLIMITED
16. DEFAULT logical_reads_per_session KERNEL UNLIMITED
18. DEFAULT Logical_reads_per_call KERNEL UNLIMITED
20. DEFAULT idle_time KERNEL UNLIMITED
22. DEFAULT connect_time KERNEL UNLIMITED
24. DEFAULT PRIVATE_SGA KERNEL UNLIMITED
26. DEFAULT failed_login_attempts PASSWORD
28. DEFAULT password_life_time PASSWORD
30. DEFAULT password_reuse_time PASSWORD UNLIMITED
32. DEFAULT Password_reuse_max PASSWORD UNLIMITED
34. DEFAULT password_verify_function PASSWORD NULL
36. DEFAULT password_lock_time PASSWORD 1
38. DEFAULT password_grace_time PASSWORD 7
40. rows Selected

Sql> SELECT * from Dba_profiles where profile= ' DEFAULT '; Profile resource_name Resource_type LIMIT-------------------------------------- -----------------------------------------------------------------------------DEFAULT composite_li        MIT KERNEL Unlimiteddefault Sessions_per_user KERNEL                        Unlimiteddefault cpu_per_session KERNEL Unlimiteddefault        Cpu_per_call KERNEL Unlimiteddefault logical_reads_per_session                        KERNEL Unlimiteddefault Logical_reads_per_call KERNEL unlimiteddefault                     Idle_time KERNEL Unlimiteddefault Connect_time  KERNEL Unlimiteddefault PRIVATE_SGA                    KERNEL Unlimiteddefault failed_login_attempts PASSWORD 1 0DEFAULT password_life_time PASSWORD 180DEFAULT password_      Reuse_time PASSWORD Unlimiteddefault Password_reuse_max PASSWORD                        Unlimiteddefault password_verify_function PASSWORD Nulldefault Password_lock_time PASSWORD 1DEFAULT password_grace_time passwor D 716 Rows selected

Resource_parameter section (Resource_type=kernel):
1.session_per_user: Specifies the number of concurrent sessions that limit the user.
2.cpu_per_session: Specifies the Cpu time limit for the session, in 1% seconds.
3.cpu_per_call: Specifies the Cpu time limit for one invocation (parse, execute, and fetch) in 1% seconds.
4.connect_time: Specifies the total connection time, in minutes, for the session.
5.idle_time: Specifies the total time, in minutes, that the session is allowed to be continuously inactive, and the session will be disconnected.
6.logical_reads_per_session: Specifies the number of blocks of data that a session allows to read, including all blocks of data read from memory and disk.
7.logical_read_per_call: Specifies the maximum number of data blocks that are allowed to be read by the Execute SQL (parse, execute, and fetch) call at one time.
8.PRIVATE_SGA: Specifies the maximum amount of space, in bytes, that a session can allow to allocate in the shared pool (SGA).
9.composite_limit: Specifies the total resource consumption for a session, expressed in service units units. Password_parameter section (Resource_type=password):
1.failed_login_attempts: Specifies the maximum number of times that an attempt to log in is allowed before the account is locked out.
2.password_life_time: Specifies the number of days that the same password is allowed to use.
3.password_reuse_time and Password_reuse_max: These two parameters must be associated with each other, password_reuse_time specifies the number of days before the password cannot be reused.
The Password_reuse_max specifies the number of times the password has changed before the current password is reused. Two parameters must be set to an integer.
4.password_lock_time: Specifies the lockout time, in days, for the number of failed login attempts to reach payback.
5.password_grace_time: Specifies the number of days to expire, and the database issues a warning to the number of days before the login expires. If the database password is not modified in the middle, the expiration is invalidated.
6.password_verify_function: This field allows complex PL/SQL password validation scripts to be passed as parameters to the Create profile statement. The Oracle database provides a default script that
However, you can create your own validation rules or use third-party software validation. For a function name, specifying the name of the password validation rule, which is specified as NULL, means that the password verification feature is not used.


Iii. ExamplesCreate a custom profile

2. CREATE Profile My_profile LIMIT
4. Sessions_per_user UNLIMITED
6. Cpu_per_session UNLIMITED
8. Cpu_per_call 6000
10. Connect_time 60
12. Logical_reads_per_session DEFAULT
14. Logical_reads_per_call 6000
16. Composite_limit 6000000
18. PRIVATE_SGA 66K
20. Failed_login_attempts 6
22. Password_life_time 60
24. Password_reuse_time 60
26. Password_reuse_max 5
28. Password_lock_time 1/24
30. Password_grace_time 10

CREATE profile My_profile limitsessions_per_user unlimitedcpu_per_session unlimitedcpu_per_call 6000CONNECT_TIME 60logical_reads_per_session defaultlogical_reads_per_call 6000composite_limit 6000000PRIVATE_SGA 66K   FAILED_ Login_attempts 6 Password_life_time 60password_reuse_time Password_reuse_max 5   

You can specify the profiles you want to use when you create a user

2. Sql> Create user cancer identified by cancer default tablespace test temporary tablespace Temp  Profile My_profile;
4. User created
8. Sql> Select Username,profile from dba_users where username=' cancer ';
10. USERNAME profile
12. ------------------------------ ------------------------------
14. Cancer My_profile

Sql> create user cancer identified by cancer default tablespace test temporary tablespace temp profile my_profile; User createdsql> Select Username,profile from dba_users where username= ' cancer '; USERNAME profile                       ------------------------------------------------------------cancer                         my_profile

or change its profiles for a user who has already created it

2. sql> alter user cancer profile default;
4. User Altered
8. Sql> Select Username,profile from dba_users where username=' cancer ';
10. USERNAME profile
12. ------------------------------ ------------------------------
14. Cancer DEFAULT

About Oracle's profiles