Restrict the use of database resources by creating a user profile file

Resource list that can be restricted

In the following example, create a test_profile environment file for the user testuser:

Create profile test_profile limit

Failed_login_attempts 3;

Create user testuser identified by abcd1234

Profile test_profile;

Grant create session to testuser;

If the connection to the testuser account fails three times in a row, the account is automatically locked by Oracle. Then, when you enter the correct password for the testuser account, you will receive an error message:

Error: ORA-28000: the account is locked

The environment file is created using the "Create profile" command, which can be modified using the "alter profile" command. The "alter profile" command in the following example is used to modify an existing environment file. In this example, the default environment file of the database is modified to allow the maximum idle time to 1 hour:

Alter profile default or profile file name limit idle_time 60;

-- Set the user's resource configuration file
1. Set the initialization parameter resource_limit = true
2./* create a resource configuration file */
Create profile prfile_name limit session_per_user 2 cpu_per_session 10000 idle_time 60 connect_time 480;

3. Alter user user_name profile profile_name; -- allocate the created profile file to the user

4. Run the command to modify the resource configuration file.
Alter profile default or profile file name limit idle_time 60;

5. Drop profile profile_name; -- Delete profile

/* After a profile is created and specified to a user, you must use cascade to delete it */
Drop profile profile_name cascade;

Alter system set resource_limit = true; --- enable voluntary restriction. The default value is false.