Understanding Database Authorization Methods - Oracle

Once a user is authenticated with the database, the next step is to determine what types of objects, privileges, and resources the user is permitted to access or use. In this section, we review how profiles can control not only how passwords are managed but also how profiles can put limits on various types of system resources.

In addition, we review the two types of privileges in an Oracle database: system privileges and object privileges. You can assign both of these privileges directly to users, or indirectly through roles, another mechanism that can make a DBA’s job easier when assigning privileges to users.

Profile Management

There never seems to be enough CPU power or disk space or I/O bandwidth to run a user’s query. Because all these resources are inherently limited, Oracle provides a mechanism to control how much of these resources a user can use. An Oracle profile is a named set of resource limits providing this mechanism.

In addition, you can use profiles as an authorization mechanism to control how user passwords are created, reused, and validated. For example, you may wish to enforce a minimum password length, along with a requirement that at least one uppercase and lowercase letter appear in the password. In this section, we talk about how profiles manage passwords and resources.

For your installation of Oracle Database XE, you may have only one user or a handful of users; most likely, you will not need to use profiles at all. If you are using Oracle Database XE as a development tool, or you are the only user, profiles will not be that useful until you start using Oracle Database XE as a production database, or more likely, when you migrate the application to another version of Oracle Database.

The CREATE PROFILE Command

TheCREATE PROFILEcommand controls many different restrictions and user resources. First, you can create a profile to limit the connect time for a user to 120 minutes:

create profile lim_connect limit
     connect_time 120;

Similarly, you can limit the number of consecutive times a login can fail before the account is locked:

create profile lim_fail_login limit
     failed_login_attempts 8;

Or you can combine both types of limits in a single profile:

create profile lim_connectime_faillog limit
     connect_time 120
     failed_login_attempts 8;

How Oracle responds to a resource limit being exceeded depends on the type of limit. When one of the connect time or idle time limits is reached (such asCPU_PER_SESSION), the transaction in progress is rolled back and the session is disconnected. For most other resource limits (such asPRIVATE_SGA), the current statement is rolled back, an error is returned to the user, and the user has the option to commit or roll back the rest of the transaction. If an operation exceeds a limit for a single call (such asLOGICAL_READS_PER_CALL), the operation is aborted, the current statement is rolled back, and an error is returned to the user. The rest of the transaction remains intact. The user can then roll back, commit, or attempt to complete the transaction without exceeding statement limits.

Oracle provides theDEFAULTprofile, which is applied to any new user if no other profile is specified. This query against the data dictionary viewDBA_PROFILESreveals the limits for theDEFAULT profile:

SQL> select * from dba_profiles where profile = 'DEFAULT';

16 rows selected.

The only real restriction in theDEFAULTprofile is the number of consecutive unsuccessful login attempts before the account is locked, which is ten. In addition, no password verification function is enabled.

Please check back for the next part of the series.