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