April 2004

The Specialist
Database News You Can Use
A monthly newsletter for Oracle usersApril 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Password Management

You’re invited to submit your Oracle-related questions to us at askdbspecialists04@dbspecialists.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only). Installing Oracle 10g on Linux

This month’s question comes from Greg in Pittsburgh, Pennsylvania: We are going through our annual audit and have been asked a most perplexing question. Our auditors want us to prove that a password has been changed every 30 days. We do not use resource groups or fancy profiles to manage the process yet know our users do change their passwords. Is there a way I can tell that a password has been changed using just the system catalog?

Ian Jones of the Database Specialists team responds: The user$ table in the SYS schema contains a column called “ptime” which indicates the date and time a user’s password was last changed. (Prior to Oracle 8i this column indicates the date but not the time.) For example, the query:

SELECT   name "Username", ptime "Password Changed"
FROM     SYS.user$
WHERE    type# = 1
AND      ptime < TRUNC (SYSDATE) - 30

will give you a list of users who have not changed their passwords in the last 30 days. (Note that type#=1 are users and type#=2 are roles.)

There is no easy way to reconstruct the data historically. If you are running Oracle 9i you could use Log Miner to do this. (DDL was not mined in Oracle 8i’s Log Miner.) For capturing the information going forward, you have a few options: One approach would be to enable database auditing of the ‘ALTER USER’ command. However, this would catch all invocations of the ALTER USER command including non-password related ones such as changing a user’s default tablespace. Another approach would be to regularly copy the rows from SYS.user$ into a history table which you could then audit.

As Greg alluded to, probably the best way to ensure passwords are actually managed in compliance with the company’s auditing policy would be to let Oracle manage passwords using its native functionality. Oracle 8i introduced a number of password management features, including the ability to expire passwords with or without a grace period. This is implemented through the use of a resource profile. (Note that you do not need to set the ‘resource_limit’ instance parameter to TRUE to enforce the password options within a resource profile.)

For example, the following SQL statements will create a resource profile and assign this profile to the SCOTT user:


CREATE PROFILE change_passwd_monthly
LIMIT PASSWORD_LIFE_TIME 30  -- Password must be changed within 30 days
      PASSWORD_GRACE_TIME 1  -- Users have 1 day after expiration 
                             -- before they will fail to log in

ALTER USER scott PROFILE change_passwd_monthly;

If you have requirements regarding the reuse of passwords (i.e. to stop users from simply changing their password back to its original setting and thus avoiding actually changing their passwords) you can set either of the following options in the resource profile:

PASSWORD_REUSE_TIME: The number of days that must pass before a password can be reused.
PASSWORD_REUSE_MAX: The number of times that a password must change before it can be reused.

If you choose to set one of these in a resource profile, you will probably want to set the other to “unlimited” in the same resource profile. Once either of these options is set, Oracle automatically maintains the necessary password history (i.e. that necessary to enforce the password reuse rules you adopt) in SYS.user_history$.

Please note that, depending upon your situation, the use of these resource profile features may require changes to the logon logic within your application.


If you are interested in taking a look at Oracle Database 10g and you have a spare Linux box available, you might want to read the white paper at http://www.puschitz.com/InstallingOracle10g.shtml. Author Werner Puschitz has put together a guide that will give you a pretty good head start on installing Oracle Database 10g on three different varieties of Red Hat Linux. Puschitz writes, “Oracle10g is the easiest Oracle on Linux installation so far.” Oracle Performance Management

Sounds like good news, yes? Oracle Database 10g is certified by Oracle Corporation for use on Red Hat Enterprise Linux Advanced Server versions 2.1 and 3. Puschitz also explains in his paper how to install Oracle on Red Hat 9. Although Oracle Database 10g is not certified for use on Red Hat 9, this may be a more cost-effective platform for exploring the latest release of the Oracle database.


If you’ve ever been interested in Oracle performance management (and who hasn’t?), you’ve likely heard of Craig Shallahamer. Craig is the President of OraPub, a research, training, and consulting company which provides performance predictability software and workshops all over the world on topics such as Proactive Performance Management and Advanced SQL Tuning for DBAs. Be sure to take a look at the OraPub website, which is a handy resource portal. You’ll find a compilation of white papers, industry news, tools, an events calendar, and more. In the Papers Archive section of http://www.orapub.com, you’ll find topics such as: RMOUG Training Days Recap

  • Response Time Analysis for Oracle Based Systems
  • All About Oracle’s Touch-Count Data Block Buffer Algorithm
  • Oracle Database Performance Tuning Methods
  • Forecasting Oracle System Performance Using Simulation
  • Grid Computing—The Next Big Thing


There’s some good news for those of you who weren’t able to attend the Rocky Mountain Oracle Users Group (RMOUG) Training Days event in February. The Database Specialists team presented three technical sessions at this two-day conference packed with over 100 educational presentations. If you visit the RMOUG website, you’ll find a recap of the Training Day presentations, along with summaries of sessions and downloads. Session tracks included:

  • Application Development
  • Data Warehousing
  • Database Administration
  • Technology Management
  • Web Application Development

Leave a Reply

Your email address will not be published. Required fields are marked *