Simple and interesting password change question 1

[ajhull]

I would like to grant the priviledge to a user to be able to change other user passwords (hence 'alter user' priviledge) ,however, I do not want them to be able to change the password for critical accounts like 'sys'/'system' etc.

Anyway to do this?

Adrian
Certified DBA/Developer

 

[karluk]

To do this, you will probably have to create a stored procedure which accepts as input the userid and password. You could insert an edit in the procedure to disallow changing passwords of certain ids. If the input passed edit, you would construct and execute a dynamic sql statement to alter the password.

The idea is to control password changes via granting execute to the procedure. Doing it this way allows you to write code to prevent changes you don't want to see.

 

[ajhull]

But since the procedure dynamically executes the sql 'alter user...' does that not mean that any user that is able to execute this procedure would also have to have the priviledge 'alter user'. If they do, then they can still change the 'sys' password.

Am I wrong?

Adrian
Certified DBA/Developer

 

[SantaMufasa]

AJ,

Here is a proof of concept for Karl's suggestion:

SQL> connect test/test
Connected.
set serveroutput on
create or replace procedure Change_pw (uname in varchar2, pw in varchar2) is
		insufficient_privs	exception;
		pragma exception_init(insufficient_privs,-1031);
begin
	if UPPER(uname) in ('SYS','SYSTEM') then
		raise insufficient_privs;
	else
		execute immediate ('alter user '||uname||' identified by '||pw);
		dbms_output.put_line('Changed password for user: '||uname);
	end if;
end;
/

Procedure created.

grant execute on change_pw to yada
/

Grant succeeded.

SQL> conn yada/yada
Connected.

SQL> select granted_role from user_role_privs;

GRANTED_ROLE
------------------------------
CONNECT
PLUSTRACE
RESOURCE

SQL> alter user test2 identified by test2;
alter user test2 identified by test2
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> exec test.change_pw('test2','yada')

PL/SQL procedure successfully completed.

SQL> exec test.change_pw('sys','yada')
BEGIN test.change_pw('sys','yada'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.CHANGE_PW", line 6
ORA-06512: at line 1

conn test2/yada
Connected.
SQL>

So notice, above, that the user, "test" created the "Change_pw" procedure. "Test" then granted execute on the procedure to user, "yada". After connecting to "yada" (and confirming that "yada" does not have DBA privileges), an attempt to change password for "test2" fails when attempted with regular SQL, but succeeds with the TEST.CHANGE_PW procedure. Notice then, that an attempt by YADA to change "SYS" password fails (due to the code in the procedure).

Let us know if this is what you wanted,

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:27 (11Dec03) GMT, 18:27 (10Dec03) Mountain Time)

 

[ajhull]

Mufasa and Karluk,

Many thanks. And thank you for the example - I learnt something there.

I will deploy into the test database.

Final question: I am guessing that the person that initially compiles this procedure would have to have the 'alter user' priviledge for it to compile correctly. Am I correct?

Cheers,



Adrian
Certified DBA/Developer

 

[carp]

No.
The error would occur at runtime (unless you write the procedure with invoker's rights).
Since you are generating dynamic sql, the procedure is just assembling a text string. Compilation is no problem. It's when you try to run EXECUTE IMMEDIATE with an ALTER USER command that your privileges will be checked.

 

[SantaMufasa]

Correct, the owner/compiler of the procedure needs the ability to ALTER another user's password.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:44 (11Dec03) GMT, 09:44 (11Dec03) Mountain Time)

 

[ajhull]

Once again. thanks.

Adrian
Certified DBA/Developer

 

[carp]

For clarification -

the owner does not need the ALTER USER privilege to get this procedure to compile. However, when the owner/user executes the procedure, s/he WILL need the privilege.

Again, if the procedure is modified to use invoker's rights instead of owner's rights, the owner may NEVER need the ALTER USER privilege. If the owner (e.g., scott) grants execution privs to yada (see above example) and yada has ALTER USER privilege but scott does not, then as long as yada runs the procedure but scott does not, everything should be fine.

 

[ajhull]

Perhaps I am confused now....

I thought...

1) scott(dba) compiles and has the 'alter user' priv
2) grants execute priv on procedure to yada
3) yada does not have 'alter user' priv
4) yada can still execute the procedure successfully

If this is not the case then why would I even bother writing a procedure that prevents yada from altering the 'sys' account password if she can just simple type 'alter user sys identified by XXXXX'?


Adrian
Certified DBA/Developer

 

[carp]

There are probably two points to be clarified here:

1. For your purposes, you would DEFINITELY need scott to have the 'ALTER USER' privilege, since you don't want yada to have that privilege. Therefore, you will want to use owner's rights instead of invoker's rights (as is done in the code already provided) AT EXECUTION TIME (not necessarily at compile time).

2. Scott does NOT have to have ALTER USER privilege to get the procedure to compile. He WILL need it to execute.

My original post was in response to your question about whether or not the ALTER USER privilege was needed to get the procedure to compile. The answer is "no".

So to summarize:
Does scott need to have the ALTER USER privilege:
To compile: No.
To execute: Yes.

Sorry to have muddied up the waters for you.