Users and Database Authorization for Oracle Database XE

In this fourth part of a ten-part series on securing Oracle Database XE, we’ll delve more deeply into handling users and look at the various methods of database authorization. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Altering Users

Changing the characteristics of a user is accomplished by using the ALTER USER command. The syntax for ALTER USER is nearly identical to that of CREATE USER , except that ALTER USER allows you to assign roles as well as grant rights to a middle-tier application to perform functions on behalf of the user.

In this example, we change user KELLYC to use a different default permanent tablespace:

SQL> alter user kellyc
  2  default tablespace php_apps quota 500m on php_apps;

User altered.

SQL>

Note that the user KELLYC still can create objects in the USERS tablespace, but she must explicitly specify USERS in any CREATE TABLE and CREATE INDEX commands.

Dropping Users

Dropping users is very straightforward and is accomplished with the DROP USER command. The only parameters are the username to be dropped and the CASCADE option; any objects owned by the user must be explicitly dropped or moved to another schema if the CASCADE option is not used. In the following example, the user QUEENB is dropped, and if there are any objects owned by QUEENB , they are automatically dropped as well:

SQL> drop user queenb cascade;
User dropped.

If any other schema objects, such as views or packages, rely on objects dropped when the user is dropped, the other schema objects are marked INVALID and must be recoded to use other objects and then recompiled. In addition, any object privileges that were granted by the first user to a second user via the WITH GRANT OPTION clause are automatically revoked from the second user if the first user is dropped.

Becoming Another User

To debug an application, a DBA sometimes needs to connect as another user to simulate the problem. Without knowing the actual plain-text password of the user, the DBA can retrieve the encrypted password from the database, change the password for the user, connect with the changed password, and then change back the password using an undocumented clause of the ALTER USER command. It is assumed that the DBA has access to the DBA_USERS table, along with the ALTER USER privilege. If the DBA has the DBA role, these two conditions are satisfied.

The first step is to retrieve the encrypted password for the user, which is stored in the table DBA_USERS :

SQL> select password from dba_users where username = ‘KELLYC';

PASSWORD
————————-
E18FBF6B825235F2

SQL>

Save this password using cut and paste in a GUI environment, or save it in a text file to retrieve later. The next step is to temporarily change the user’s password and then log in using the temporary password:

SQL> alter user kellyc identified by temppass;
User altered.
SQL> connect kellyc/temppass;
Connected.

At this point, you can debug the application from KELLYC ’s point of view. Once you are done debugging, change the password back using the undocumented by values clause of ALTER USER :

SQL> alter user kellyc identified by values ‘E18FBF6B825235F2′;
User altered.

User-Related Data Dictionary Views

A number of data dictionary views contain information related to users and characteristics of users. Table 31-2 lists the most common views and tables.

Table 31-2. User-Related Data Dictionary Views and Tables

Data Dictionary View

Description

DBA_USERS

Usernames, encrypted passwords, account status, and default tablespaces.

DBA_TS_QUOTAS

Disk space usage and limits by user and tablespace, for users who have quotas that are not UNLIMITED.

DBA_PROFILES

Profiles that can be assigned to users with resource limits assigned to the profiles.

USER_HISTORY$

Password history with usernames, encrypted passwords, and date stamps. Used to enforce password reuse rules.

{mospagebreak title=Understanding Database Authorization Methods}

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

The CREATE PROFILE command 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 as CPU_PER_SESSION ), the transaction in progress is rolled back and the session is disconnected. For most other resource limits (such as PRIVATE_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 as LOGICAL_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 the DEFAULT profile, which is applied to any new user if no other profile is specified. This query against the data dictionary view DBA_PROFILES reveals the limits for the DEFAULT profile:

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

PROFILE

RESOURCE_NAME

RESOURCE_TYPE

LIMIT

 

 

 

 

DEFAULT

COMPOSITE_LIMIT

KERNEL

UNLIMITED

DEFAULT

SESSIONS_PER_USER

KERNEL

UNLIMITED

DEFAULT

CPU_PER_SESSION

KERNEL

UNLIMITED

DEFAULT

CPU_PER_CALL

KERNEL

UNLIMITED

DEFAULT

LOGICAL_READS_PER_SESSION

KERNEL

UNLIMITED

DEFAULT

LOGICAL_READS_PER_CALL

KERNEL

UNLIMITED

DEFAULT

IDLE_TIME

KERNEL

UNLIMITED

DEFAULT

CONNECT_TIME

KERNEL

UNLIMITED

DEFAULT

PRIVATE_SGA

KERNEL

UNLIMITED

DEFAULT

FAILED_LOGIN_ATTEMPTS

PASSWORD

10

DEFAULT

PASSWORD_LIFE_TIME

PASSWORD

UNLIMITED

DEFAULT

PASSWORD_REUSE_TIME

PASSWORD

UNLIMITED

DEFAULT

PASSWORD_REUSE_MAX

PASSWORD

UNLIMITED

DEFAULT

PASSWORD_VERIFY_FUNCTION

PASSWORD

NULL

DEFAULT

PASSWORD_LOCK_TIME

PASSWORD

UNLIMITED

DEFAULT

PASSWORD_GRACE_TIME

PASSWORD

UNLIMITED

16 rows selected.

The only real restriction in the DEFAULT profile 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.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan