Profiles- Users and Basic Security

When you create users, requirements call for strong passwords and for the passwords to adhere to a set of security rules. You may want to ensure that a certain user is not capable of consuming inordinate amounts of CPU resources. These two examples can be done in database profiles. An Oracle profile is a database object that serves two purposes:

• Enforces password security settings

• Limits system resources that a user consumes

Tip don’t confuse a database profile with a sQL profile.a database profile is an object assigned to a user that enforces password security and constrains database resource usage, whereas a sQL profile is associated with a sQL statement and contains corrections to statistics to help the optimizer generate a more efficient execution plan.

Limiting Database Resource Usage

As mentioned earlier, the password profile settings take effect as soon as you assign the profile to a user. Unlike password settings, kernel resource profile restrictions don’t take effect until you set the RESOURCE_LIMIT initialization parameter to TRUE, which is the default, for your database; for example,

To view the current setting of the RESOURCE_LIMIT parameter, issue this query:

When you create a user, if you don’t specify a profile, then the DEFAULT profile is assigned to the user. You can modify the DEFAULT profile with the ALTER PROFILE statement. The next example modifies the DEFAULT profile to limit CPU_PER_SESSION to 240,000 (in hundredths of seconds):

This limits any user with the DEFAULT profile to 2,400 seconds of CPU use. You can set various limits in a profile. The following are examples of database resource settings you can limit:

•     CONNECT_TIME

• CPU_PER_SESSION

•    IDLE_TIME

• LOGICAL_READS_PER_SESSION

•     SESSIONS_PER_USER

You can also create a custom profile and assign it to users via the CREATE PROFILE statement. You can then assign that profile to any existing database users. The following SQL statement creates a profile that limits resources, such as the amount of CPU and individual session can consume:

After you create a profile, you can assign it to a user. In the next example, the user hsolo is assigned USER_PROFILE_LIMIT: