Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

[Previous entry: "Holidays, Patch re-releases and newsletters"] [Next entry: "Another Major UK Data Loss"]

Stopping a user from changing his own Oracle database password



I had a chat with a friend of mine on the phone last night and he asked me a question. I won't reveal his name in case he doesn't want me to but he knows who he is.

He asked me if its possible to stop a user from changing his own password. This is an issue as the system privilege ALTER USER allows password changes of any user BUT Oracle silently allows the use of the ALTER USER command on a users own account without the system privilege being granted. If it was as simple as a user can only change a password including his own IF he has the ALTER USER system privilege then that would make it simpler to restrict and block, i.e. dont grant the system privilege.

Interestingly the same issue applies to the ALTER SESSION command. You don't need ALTER SESSION to change NLS_LANG or date format, in fact its only needed to set events / trace. I always find this an inconsistency, that Oracle perhaps should not have allowed. i.e. the syntax actually has two meanings, one as a system privilege and one as a command to do the action in a restricted sense without having the system privilege, what I would have called a "hack" (not in the hacker / cracker sense) but in the traditional software development sense.

So how can we block a command that cannot be granted or revoked? - If we could assume that the users can only access the database directly with SQL*Plus then maybe its possible.... then maybe not. I talked about trying to block SQL*Plus five years ago in a newsletter but concluded its impossible. It still is impossible but this is a slight variation on the same issue. The problem is that users cannot be restricted to one tool.

So OK, what other options are there? - clearly a tool such as Sentrigo Hedgehog could be an option as we can detect the use of the syntax and actively block the command.

I wanted to see if there is a native way also. I came up with the idea of a DDL trigger that fires on the use of an ALTER USER command and then blocks its use. Clearly we can extend the same ideas to ALTER SESSION problems that are similar but there is then a less convenient syntax (i.e. we cannot detect that its on the USER dictionary object for an ALTER SESSION) by using the sql_text function to retrieve the SQL of the triggering SQL and then parse out the relevant ALTER SESSION command that you want to block. OK, here goes for a simple solution to my friends problem:



SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 13 20:01:08 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter user-name: system/xxxxxx

Connected to:
Personal Oracle Database 11g Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user alter_test cascade;

User dropped.

SQL> create user alter_test identified by alter_test;

User created.

SQL> grant create session to alter_test;

Grant succeeded.

SQL> connect alter_test/alter_test
Connected.

SQL> alter user alter_test identified by alter_test;

User altered.

SQL> connect system/xxxxxx
Connected.
SQL> create or replace trigger alter_trigger
2 before alter
3 on database
4 declare
5 begin
6 if (ora_dict_obj_type = 'USER') then
7 raise_application_error(-20010,'you cannot change your own password');
8 end if;
9 end;
10 /

Trigger created.

SQL> connect alter_test/alter_test
Connected.
SQL> alter user alter_test identified by x;
alter user alter_test identified by x
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20010: you cannot change your own password
ORA-06512: at line 4


SQL> connect system/oracle1
Connected.
SQL> drop user alter_test cascade;

User dropped.

SQL> drop trigger alter_trigger;

Trigger dropped.

SQL>


Let's just walking through this code; first we connect as SYSTEM and remove the test user. Then we create a user ALTER_TEST and grant only CREATE SESSION to it (he doesnt have ALTER USER system privilege). Then we test that he can change his own password back to the same value (we could have changed it to any other value).

Next we re-connect as SYSTEM and create a very simple trigger (in a real system you would want to make this more robust of course with error checking and recovery; you would also most likely not want it to detect the use of the system privilege for legitimate users who are allowed to change passwords and most likely not stop some key staff from changing their own passwords; You would also want it to function correctly with password management).

The trigger is simple. It fires on ALTER at the database level; that is its a system trigger at the database level and not the schema level. It simply checks that the dictionary object being altered, in this case a user, so we detect that the USER dictionary object is being altered. This means a logical object not the SYS.USER$ table, so don't put USER$ in there.

Finally we can connect as the test user again, ALTER_TEST and now try and change our own password. This time the RAISE_APPLICATION_ERROR fires and reports that the trigger fired and stopped us changing our own password. It worked!

There are probably other solutions if Ithink harder about it but this one will work.

There has been 8 Comments posted on this article


August 14th, 2008 at 12:45 am

Pete Finnigan says:

You can also (ab)use the password verify function for a profile, which allows it to be targetted at individuals or groups of users.

CREATE PROFILE NO_CHANGE_PWD LIMIT PASSWORD_VERIFY_FUNCTION verify_function_false;

CREATE OR REPLACE FUNCTION verify_function_false
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
BEGIN
if user != 'SYS' then
RETURN(FALSE);
else
return true;
end if;
END;
/

create user alter_test identified by alter_test profile no_change_pwd;

grant create session to alter_test;

conn alter_test/alter_test
alter user alter_test identified by fred replace alter_test;



August 14th, 2008 at 08:59 am

Pete Finnigan says:

Thanks Gary, good catch. I have actually used this before but forgot. I was racking my brains last night to find a second solution that was easy. i knew there was one but could not remember it.

thanks gary

cheers

Pete



August 14th, 2008 at 09:11 am

Pete Finnigan says:

Brilliant piece of lateral thinking, Gary.
You might want to use the SYS_CONTEXT function to augment the checks: "user" is not the safest identifier.
I use AUTHENTICATED_IDENTITY and HOST to establish if it's SYS from the correct node and the correct OS logon id, otherwise no-go.
For those interested, there is a gold mine of security checks to be had from
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm#g1513460



August 14th, 2008 at 12:29 pm

Pete Finnigan says:

Hi,
As I'm not very familiar with OCI programming I was wondering if the "alter_trigger" trigger would fire on OCIPasswordChange call. I would assume it is not issuing an ALTER USER statement internally hence any application using that OCI call will still allow its users to change their password. Right?



August 14th, 2008 at 01:55 pm

Pete Finnigan says:

Hi Bidu,

Thanks for your comment. It's a good point and one that I cannot answer without testing it. I am not in a position this afternoon to write a test OCI program with this function call in it to see if the trigger still captures it. If I have time this evening I may have a go, in the meantime if anyone else has any code ready to go that could be tested I would love to hear the results. My gut feeling would be that gary's solution should work and that mine probably works as there is still a DDL change on a USER object even with out the ALTER USER command.

cheers

Pete



August 14th, 2008 at 11:31 pm

Pete Finnigan says:

Pete,

there is a simple way to test OCIPasswordChange. Just use the password command in SQL*Plus.

I did it in Oracle 10.2.0.4 and your solution with the trigger is working.



August 20th, 2008 at 06:11 pm

Pete Finnigan says:

Hi Gary

I was playing with your password verification function and I found a bug (10.2.0.3/11.1.0.6) which allows users with a special privilege to bypass the password verification function.

Today I reported this issue already reported to secalert.

I was not able to bypass Pete's DDL trigger. That's why I would recommend the DDL-Trigger solution.

Regards

Alexander



August 22nd, 2008 at 12:55 pm

Pete Finnigan says:

Hi Alex,

Thanks for your tests. I had a suspicion that the password command uses the OCIPasswordChange function. I didn't want to say in case i was wrong and I didn't have the time to check as I was packing for summer holidays. We are back now so normal service is resumed..:-).

I did have an OCI program with the function in that I can also test, if I get a chance i will compile it and test it as well. An interesting way to check that sqlplus uses the function is to use the OCI Spy utility, there is a link on my tools page.

Thanks Alex

cheers

Pete