block changing specific user password

[dbase77]

Hi all,

Is there any way I can use in sql script where I can block changing specific user? For example I dont want to change system and sys user password. I have a script where it requests username and password to change. But I dont want this person to change system and sys user password. Anybody ever done this before?

Example of script:

accept user prompt 'Enter Oracle username: (must be in UPPERCASE) '
accept pwd hide prompt 'Enter New Password: (at least 6 characters including one
number) '
alter user &user identified by &pwd
/

Thank you in advance.

..........
dbase77

 

[hoinz]

why not just this:
In your script, after reading the input, compare if it happens to be sys or system, and if so, print 'sorry' and exit?

 

[SantaMufasa]

But dbase77, what is there that forces a password changer to use your script? Why can they not simply issue an "ALTER USER SYS IDENTIFIED BY <new-password>;"?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[dbase77]

Hi,

Operator doesnt have access to sqlplus program either from UNIX prompt or windows machine. They logon using ssh and it will prompt some sort of menu where they can choose any task they want. So there is no way they can change it using "alter user" command. I run this sql script from unix shell using "sqlplus -s system/pass @/home/jail/pwchange.sql".

I might try hoinz idea to check within shell script. If there is a way to check within sql, I would like to know the way in doing that.

............
dbase77

 

[SantaMufasa]

dbase77,

Here is a script, then, that should do what you want. Proofs of concept follow the script, which I named, "pw.sql":

*****************************************************************************************************
set verify off
accept user prompt 'Enter Oracle username: '
accept pwd hide prompt 'Enter New Password: (at least 6 characters including at least one numeral): '
declare
    sql_stm    varchar2(200) := 'alter user &user identified by &pwd';
begin
    if upper('&user') in ('SYS','SYSTEM') then
        raise_application_error(-20000,'Error: Cannot modify password for "SYS" or "SYSTEM" user.');
    elsif length('&pwd') < 6 then
        raise_application_error(-20001,'Error: new password must be at least 6 characters.');
    elsif length('&pwd') = length(translate('&pwd','^0123456789','^')) then
        raise_application_error(-20002,'Error: new password must include at least 1 numeral.');
    else
        execute immediate sql_stm;
    end if;
end;
/

SQL> save pw

SQL> @pw
Enter Oracle username: sys
Enter New Password: (at least 6 characters including at least one numeral): *****
declare
*
ERROR at line 1:
ORA-20000: Error: Cannot modify password for "SYS" or "SYSTEM" user.
ORA-06512: at line 5


SQL> @pw
Enter Oracle username: system
Enter New Password: (at least 6 characters including at least one numeral): ******
declare
*
ERROR at line 1:
ORA-20000: Error: Cannot modify password for "SYS" or "SYSTEM" user.
ORA-06512: at line 5


SQL> @pw
Enter Oracle username: dhunt
Enter New Password: (at least 6 characters including at least one numeral): ****
declare
*
ERROR at line 1:
ORA-20001: Error: new password must be at least 6 characters.
ORA-06512: at line 7


SQL> @pw
Enter Oracle username: dhunt
Enter New Password: (at least 6 characters including at least one numeral): *********
declare
*
ERROR at line 1:
ORA-20002: Error: new password must include at least 1 numeral.
ORA-06512: at line 9


SQL> @pw
Enter Oracle username: dhunt
Enter New Password: (at least 6 characters including at least one numeral): *******

PL/SQL procedure successfully completed.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Turkbear]

Hi,
If the user already knows the sys or system password ( which is needed in order to change it) then the users can get in and do anything.
If you change it ( and you should, on a regular basis), no user that does not know the new password can change it..

Th SYS and SYSTEM accounts should be very limited in their authorized users...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

And dbase, the script should also include a prompt for the old password, and test it for validity, before allowing a password change, as well.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Turkbear]

Hi,
In additon, severly restrict granting the DBA ( or SYSDBA/SYSOPER) roles to anyone...Only users with sufficient rights can change a password for any account except their own..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dbase77]

Mufasa,

That is what I'm looking for, check it within sql script. Thank you very much. I can check it from unix shell script before calling changing password sql script and pass the username variable. But I prefer to do it in sql script.

Turkbear, I'm setting user unix shell as menu shell script. So there is no way normal user can view the password as it is hard coded into the menu shell script. Only root user allow to view the menu shell script. On top of that user not allow to go to unix prompt.

Thank you all.

...........
dbase77

 

[Turkbear]

Hi,
That is OK, but bear in mind the your Unix script is not the only way to get to your database..Once the database's existance is known, a persistant person can probaly get to the instance, and if they have a user account with too many rights, they can change things..

Just grant users the minimum rights they need to do their work..

Better safe than sorry..





To Paraphrase:"The Help you get is proportional to the Help you give.."