Home arrow Oracle arrow Page 2 - Users and Database Authorization for Oracle Database XE

Understanding Database Authorization Methods - Oracle

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).

TABLE OF CONTENTS:
  1. Users and Database Authorization for Oracle Database XE
  2. Understanding Database Authorization Methods
By: Apress Publishing
Rating: starstarstarstarstar / 1
December 30, 2010

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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';

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 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.



 
 
>>> More Oracle Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: