Oracle 8 permits freezing passwords!

O.k., this is probably not big news to DBAs, but I've been looking for a way to freeze Oracle account passwords for a loooong time. I have never been given an answer, but I just figured it out!

First some background. We have users who like to create Java applets that use JDBC to connect to Oracle. For the applet to use the database, of course, it has to know how to authenticate. That means giving it a user ID and password. Once a user has that, he can perform arbitrary commands.

We also want to have a "public" account so that we can easily maintain a persistant connection to it for CGI apps. It can be used by anyone to look at public data.

The problem with both of these situations is that anyone who gets an Oracle connection can alter that user's password. That would mean that subsequent connections would fail (because they use a hardcoded password).

So...we have a new Oracle 8 server and I'm playing with it. I noticed the ability of the PASSWORD_VERIFY FUNCTION. That does it!

Here are my code snippets:

	create or replace FUNCTION password_lock (
		username varchar2,
		password varchar2,
		old_password varchar2
	)
	RETURN
		BOOLEAN 
	IS
	BEGIN
		RETURN(FALSE);
	END; 
	/
	
	create profile frozen_password
		limit
		PASSWORD_VERIFY_FUNCTION password_lock;
	
	alter user kyler profile frozen_password;
	

That's it! Now user "kyler" can not change his password.

This is going to help us a lot. I thought it might be handy for someone else. (Plus I'll be able to find it in Deja someday when I forget how to do it...)

--kyler Received on Fri Apr 28 2000 - 00:00:00 CDT