Well, actually -- you don't want an answer to his question as that would be very specific to that very specific product cold fusion (about which I know squat).

Here is a support note (also avaialable on metalink by searching for

ora-28001 jdbc

surprisingly!) that addresses YOUR question:


Article-ID: <Note:124970.1>
Circulation: PUBLISHED (EXTERNAL)
Folder: languages.Java.JDBC
Topic: Sample Code Articles
Title: Example: How to Change an Expired Password in JDBC?
Document-Type: SCRIPT
Impact: LOW
Skill-Level: NOVICE
Server-Version: 08.01.07
Updated-Date: 04-SEP-2002 07:12:09
References:
Attachments: NONE
Content-Type: TEXT/PLAIN
Products: 972/OCI;
Platforms: GENERIC;

Overview
--------

Starting with Oracle8i, the database provides a number of password management
capabilities including the ability to expire passwords with or without some
grace period during which the user has an option to change the password.

If the password has expired with no grace period, the attempt to connect to
the database returns an ORA-28001 error. In this case, the user must change
the password before continuing as the current connection is not valid.

If there is a grace period, the database returns an ORA-28002 warning and the
user has the option to change the password if desired, but can continue to use
the existing connection if not (ORA-28002 is a warning only).

The capability to programmatically change the password has been available in
OCI8 since 8.1.5, but was only recently exposed via JDBC, starting in 8.1.7.
Currently, this capability is only exposed in the 8.1.7 JDBC OCI driver. The
JDBC Thin driver does not currently support any password management features.


Program Notes
-------------

This sample has been tested using the following configuration:

Sun JDK 1.2 for Solaris
8.1.7 JDBC OCI driver for Solaris
8.1.7 Enterprise Edition Server for Solaris

The 8.1.7 JDBC OCI driver is mandatory, but the use of JDK 1.2 should not be
required. Also, this should work against any 8.1.X version of the database.

To run this sample, do the following:

a.) Set up your environment for Oracle (i.e. ORACLE_HOME, etc.)

b.) Set up your CLASSPATH to include the Oracle JDBC drivers

setenv CLASSPATH .:$ORACLE_HOME/jdbc/lib/classes12.zip (C shell)

OR

export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes12.zip (Korn/Bourne shell)

NOTE: If you are using JDK 1.1, substitute the 8.1.7 classes111.zip file.

c.) Compile the java source code to create a Class file

javac Employee.java

d.) Run the program

java Employee -- defaults to local connection as scott/tiger

OR

java Employee url user pass -- to specify different connect parameters

url contains the complete JDBC connect url including jdbc:oracle:oci8:@


Caution
-------

The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.


Program
-------

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -

/*
* This sample is a modified version of the Employee.java
* sample that ships with 8.1.X. It demonstrates how to use
* the new Password Management/Password Aging features supported
* in JDBC OCI starting with version 8.1.7.
*
* NOTE: Password management isn't available in the Thin driver!
*
* If invoked with no args, it uses the scott/tiger user and
* the default connection. If you wish to use a TNS service
* name, different user and/or password, you can invoke as:
*
* java Employee url user password
*/

import java.io.*;
import java.sql.*;
import java.util.*;

// The Sample Java application

class Employee
{
public static void main (String args []) throws SQLException
{
// Load the Oracle JDBC driver

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// Default connection (but could be modified to specify service name)

// NOTE: Must use oci8 here ... not supported by the thin driver

String url = "jdbc:oracle:oci8:@";
String usr = "scott";
String pwd = "tiger";

// Use singleton array so we can pass the connection to
// another function as an OUT parameter

Connection conn[] = new Connection[1];

// Use the command line args if provided

if ( args.length >= 1 ) url = args[0];
if ( args.length >= 2 ) usr = args[1];
if ( args.length == 3 ) pwd = args[2];

// Create a properties object and initialize it

Properties props = new Properties();
props.put("user",usr);
props.put("password",pwd);

// Attempt the initial connection to the database

try {

System.out.println("Attempting initial connection to " + url +
" as " + usr + "/" + pwd);

conn[0] = DriverManager.getConnection (url, props);

// If the password has expired, but there is a grace
// period, you will receive a warning, but no error.

SQLWarning sqlw = conn[0].getWarnings();

if ( sqlw != null && sqlw.getErrorCode() == 28002 )
{
// For interacting with user to get new password

String answer = null;
DataInputStream dis = new DataInputStream(System.in);

System.out.println("Your password will expire soon!");
System.out.println("Would you like to change it now (y or n)?");

try {
answer = dis.readLine();
}
catch ( IOException ioe ) {
ioe.printStackTrace();
}

// If user does not want to change the password, just
// continue. The original connection is ok to use.

if ( answer.equals("y") || answer.equals("Y") )
{
// Otherwise, close the original connection
// and open a new one, changing the password.

// Technically, it is not necessary to close
// the other connection, but this will waste
// resources by keeping the session alive if
// you have no intention of using it.

conn[0].close();

retryConnection(conn, url, props);
}
}
}
catch ( SQLException sqle ) {

// If the password has expired and there is no grace
// period, then you must change the password or exit.

if ( sqle.getErrorCode() == 28001 ) {

// For interacting with user to get new password

String answer = null;
DataInputStream dis = new DataInputStream(System.in);

System.out.println("Your password has expired!");
System.out.println("Would you like to change it now (y or n)?");

try {
answer = dis.readLine();
}
catch ( IOException ioe ) {
ioe.printStackTrace();
}

// If user wants to change password, do it.
// Otherwise exit since connection is unusable.

if ( answer.equals("y") || answer.equals("Y") )
{
retryConnection(conn, url, props);
}
else
{
System.out.println("Exiting ... ");
System.exit(0);
}
}
}

// Create a Statement
Statement stmt = conn[0].createStatement ();

// Select the ENAME column from the EMP table
ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

// Iterate through the result and print the employee names
while (rset.next ())
System.out.println (rset.getString (1));

// Close the RseultSet
rset.close();

// Close the Statement
stmt.close();

// Close the connection
conn[0].close();
}

private static void retryConnection(Connection conn[],
String url,
Properties props) throws SQLException {

Connection newconn = null;
String newpass = null, newpass2 = null;
DataInputStream dis = new DataInputStream(System.in);

try {
System.out.println("New Password: ");
newpass = dis.readLine();

System.out.println("Confirm Password: ");
newpass2 = dis.readLine();
}
catch ( IOException ioe ) {
}

if ( ! newpass.equals(newpass2) ) {
System.out.println("Passwords don't match!");
System.out.println("Exiting ... ");
System.exit(1);
}

props.put("OCINewPassword",newpass);

System.out.println("Attempting to change password ... ");

newconn = DriverManager.getConnection(url, props);

System.out.println("Connected");

conn[0] = newconn;
}
}

- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

Sample Output
-------------

If the password is current and no ORA-28001 or ORA-28002 error occurs.

Attempting initial connection to jdbc:oracle:oci8:@ as scott/tiger
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller

If the password is expired and either a ORA-28001 or ORA-28002 error occurs.

Attempting initial connection to jdbc:oracle:oci8:@ as scott/tiger
Your password has expired!
Would you like to change it now (y or n)?
y
New Password:
tiger2
Confirm Password:
tiger2
Attempting to change password ...
Connected
smith
allen
ward
jones
martin
blake
clark
scott
king
turner
adams
james
ford
miller

The one notable difference is that, for ORA-28002, you should see the message:

Your password will expire soon!


Additional Search Words
-----------------------

expiration

hmm, very useful feedback. thanks?

alter user system IDENTIFIED BY manager;

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/secure.htm#2232 <code>

There are grace periods and there are "expired passwords"

your application could (on its first screen) query the database to see if the user is in the grace period.

If so, tell them and ask them to "change their password"

If their password is expired -- that is a "help desk reset" isn't it? But if you wanted to, I suppose if you had a mapping of username to password -- you could set up a screen that was a "reset password for username:" screen that anyone could access, it would reset the password for the user (generating a random one) and email it to them (since we cannot authenticate them -- they are "expired", they cannot log in)

I don't think there is a good way to keep a password in a table.

1) but yes, a mapping of username to pasword would imply that.

2) yes

3) i'm not a fan of it.

4) because you are not going to give that password to the user, you are going to EMAIL the account that is the users email account.

Say I goto "E*Trade" (stock trading place).
Say I said to "E*Trade" -- I am 'tom', please reset my password to 'foobar'.

That would be bad. So, I can goto E*Trade and say "i am 'tom', i need a new password". E*Trade will email my email account a new password. That way, if the guy that went to etrade wasn't really ME -- they didn't get my password.

what is the point of expiring the password then?

there is a grace period (you can fix it then)
once it expires, it is expired -- must be "reset"

that is my "thought process" here -- if it is expired, it is expired -- gone, not to be used.

No, because the passwords in Oracle are not case sensitive.

documenation says.....

The Thin driver does not support OS authentication in making the connection, and therefore does not support special logins.


the thin driver does not support certain features with regards networking and this is one of them.

see dba_users, your dba can create "yet another view" on top of this to expose just the information deemed necessary for your application.

You can contact support and reference bug 3093904. It is not reproducing in 10g (that was the closure on that one) but does reproduce in 9ir2.

OH MY GOSH.

If your helpdesk updates sys.user$, you are dead in the water. You have to restart the database at the very least and be willing to have the reciever go somewhat dead when you call support.

You CANNOT UPDATE THAT TABLE, period.

This is not anything I'll even debate. That is the silliest idea I've heard. You cannot DO THIS.

If you want to un-expire passwords, well, hey -- then DON'T EXPIRE THEM IN THE FIRST PLACE???!!??! What are they thinking? I don't get it. I know you didn't do this - but these people have to be stopped, stopped dead in their tracks.

What is the point of implementing security here at all????????

This is just not smart.

parts of sys.user$ is cached in the dictionary cache in the SGA, unless you shutdown abort and startup afterwards, the cache in the SGA is out of sync with the table and very strange things can happen.

but bottom line, they CANNOT DO THAT, they cannot update sys.user$

It is ironic isn't it, it keeps coming up over and over and over again.

updating the data dictionary, among the dumbest things you can do.

lock the accounts.

you shouldn't be using them - unlock them when you want to upgrade/absolutely need them.

your dba's should definitely not be using them day to day.

I'll refer you to support for this one - assuming you are using the current jdbc drivers first (that'll be their first question in all likelyhood)

check "what"?

what would you like to do?

not using the standard "connect" syntax no.

you cannot change the way the connect command works

You would/could provide a script - connect.sql - and train them to use that to connect instead of the connect command

but then - you might as well train them that the password is case sensitive in sybase?

that approach is sound (I've done similar things myself in the past)

I don't think that you can - it is sort of a chicken and egg problem. In order to get any code to run, you have to be logged in, but you cannot log in as long as your password is expired.


Another related question, If I use a constant connection ( with a defined
username/password) to a DAD, that displays a login page, which serves to check
the user's status, how can I change connection to the user loggin on?
In other words, can I dynamically pass credentials as parameters to a DAD?


No, not really - but sort of. If you were willing to make the login sort of a two step - you could put up a screen to input a username - do your validation - then redirect them to your current URL with their username already in the URL. The challenge box should (this will be browser dependent) appear with their username in there - and only need a password now

http://pixelpusher.com/viewsource/userpass.shtml


you could pass the username and password in the URL - but that wouldn't be a good idea for obvious reasons.

I'll suggest you use the apex discussion forum on otn.oracle.com. That group owns the mod_plsql module and would be best positioned to answer this particular type of question.

Sam

can you guess what my answer will be?


Only you - you and only you - know which is the best option for your place of work. What are your rules, what are your security policies, what do you want to do?

it is totally up to you.

you can create a new profile and just "default" everything you want to fall back to the default profile on:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6010.htm#SQLRF54280

for download - yes. for purchase, I don't think so.

http://www.oracle.com/technetwork/documentation/index.html


8, 8i, 9i are all there...

11.2.0.4:

ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'USER', user ) from dual;

DBMS_METADATA.GET_DDL('USER',USER)
-------------------------------------------------------------------------------

   CREATE USER "OPS$TKYTE" IDENTIFIED BY VALUES 'S:0087E82C62DD5C4E8BD178EF1649
CB63826BE8820BCC33F5430DC935B9C1;CF0015EA0385D263'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TMP"

similar in 12.1...

what is meant by "having problems with it", do you have an example of creating a user and then having this problem?

should work

ops$tkyte%ORA11GR2> connect ops$tkyte/foobar;
Connected.
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> column x format a1000
ops$tkyte%ORA11GR2> select dbms_metadata.get_ddl( 'USER', user )x from dual;

X
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   CREATE USER "OPS$TKYTE" IDENTIFIED BY VALUES 'S:0087E82C62DD5C4E8BD178EF1649CB63826BE8820BCC33F5430DC935B9C1;CF0015EA0385D263'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TMP"


ops$tkyte%ORA11GR2> alter user "OPS$TKYTE" IDENTIFIED BY VALUES 'S:0087E82C62DD5C4E8BD178EF1649CB63826BE8820BCC33F5430DC935B9C1;CF0015EA0385D263';

User altered.

ops$tkyte%ORA11GR2> connect ops$tkyte/foobar;
Connected.
ops$tkyte%ORA11GR2>

you might have fumbled the unwrapping. it should be just one big string - no spaces

What exactly are you doing and what errors are you getting?

Can you login as another user (e.g. sys) to change the password?