Oracle Profile Limits Oracle Users

Oracle Profile Limits Oracle Users
Oracle profile files are a means of restricting the resources used by users of database tutorials. such as: Control session or SQL can use the CPU, control the user's password management strategy.
After the database is created, the system has a default profile named Default, and if you do not make a special designation, the default profile that the user uses when creating the user.

View the view Dba_profiles to find out what profile is in the database.
Sql> SELECT distinct profile from Dba_profiles;
Profile
--------------------
Monitoring_profile
DEFAULT
See all Profile
Sql> select * from Dba_profiles;
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 explanation
1, the database resources to make restrictions
{{{sessions_per_user the number of concurrent sessions allowed per user name
| Cpu_per_session A session can be used in total CPU time, in units of 1% seconds
| Cpu_per_call CPU time allowed by SQL call (Parse, execute, and get)
| Connect_time Limit session connection time, in minutes
| Idle_time allow idle session time, in minutes
| Logical_reads_per_session restrict the reading of a session to a block of data, in blocks
| Logical_reads_per_call limit SQL calls to data block reads, units are blocks
| Composite_limit "Combination Play"
} {integer | Unlimited | DEFAULT}
| PRIVATE_SGA limit the allocation of private space in shared pool in the SGA {size_clause | Unlimited | DEFAULT}
}
2, the password to make restrictions
{{{} The number of times the failed_login_attempts account can be incorrectly tried before it is locked
| Password_life_time password can be used in days, units are days, default value of 180 days
| Password_reuse_time Password Reusable time interval (combined with Password_reuse_max)
| Maximum number of Password_reuse_max password changes (combined with password_reuse_time)
| The number of days the user is locked out after the password_lock_time has exceeded the number of error attempts, default 1 days
| Password_grace_time How many days can I use the original password when the password expires?
} {Expr | Unlimited | DEFAULT}
| password_verify_function {FUNCTION | NULL | DEFAULT}
}

Modify Profile:alter Profile [resource filename] limit [resource name] unlimited;
such as: Alter profile default limit failed_login_attempts 100;

Delete Profile:drop profile [resource filename] [CASCADE];
If you create a profile that has been authorized to a user, use the Cascade cascade to reclaim the appropriate limit, and then withdraw the restriction information, the user will be restricted by the system default profiles.

Set the effective time for profile parameters
1, the user has all the profile of the password restrictions on the immediate effect, unrestricted. From this we can see how much Oracle attaches to the user's password.

2, the user has all the profile of the resource restrictions related to the setting of the Resource_limit parameter, when True, when it is False (the default value) set any value is invalid.
Sql> Show Parameter Resource_limit
NAME TYPE VALUE
-------------------- ----------- -------
Resource_limit Boolean FALSE

Other:
1, set the SQL column display width: Col column name for size;
such as Col username for A20;
2, the rights control strategy must be fully tested before use, to ensure a thorough understanding of each limit rule, and also to each step of the restriction rules recorded in a specific database maintenance manual.