Home » RDBMS Server » Security » Privileges (10g,Solaries)
Privileges [message #438777] Tue, 12 January 2010 03:32 Go to next message
madhavamanohar
Messages: 58
Registered: February 2009
Member
Hi All,

With out DBA privilege is it possible to change the password for the user?

If yes please let me know the procedure.

One application user(owner) need to change the password fro all other users,but I don't want to give DBA privilege fro that user.

Thanks
Manohar.

Re: Privileges [message #438778 is a reply to message #438777] Tue, 12 January 2010 03:41 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
The user should have "ALTER USER" SYSTEM PRIVILEGE directly.
SQL> conn raghav/raghav
Connected.
SQL> alter user scott identified by tejajun20;
alter user scott identified by tejajun20
                               *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn system/tejajun20
Connected.
SQL> grant alter user to raghav;

Grant succeeded.

SQL> conn raghav/raghav
Connected.
SQL> alter user scott identified by tejajun20;
alter user scott identified by tejajun20
*
ERROR at line 1:
ORA-28007: the password cannot be reused


SQL> alter user scott identified by teja;

User altered.

SQL> sho user
USER is "RAGHAV"
SQL>


sriram Smile

[Updated on: Tue, 12 January 2010 03:42]

Report message to a moderator

Re: Privileges [message #438779 is a reply to message #438778] Tue, 12 January 2010 03:46 Go to previous messageGo to next message
madhavamanohar
Messages: 58
Registered: February 2009
Member
Ohh Got it ..Thank you soo much.
Re: Privileges [message #438788 is a reply to message #438777] Tue, 12 January 2010 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
One application user(owner) need to change the password fro all other users,but I don't want to give DBA privilege fro that user.

Do not give ALTER USER privilege to your user as he can change ALL passwords including SYS, SYSTEM and all DBAs one, and so allow to connect as a DBA afterwards.
Create a procedure in a DBA schema that executes the ALTER USER statement but previously checks the account it tries to change.
The grant the EXECUTE privilege on this procedure to your user.

Regards
Michel

[Updated on: Tue, 12 January 2010 04:27]

Report message to a moderator

Re: Privileges [message #438793 is a reply to message #438788] Tue, 12 January 2010 05:06 Go to previous messageGo to next message
madhavamanohar
Messages: 58
Registered: February 2009
Member
Hi Michel,

Thanks for you reply..

How to create a procedure to for routine password change for every 60 days and can you please help me to resolve the following issue. please expalin the procedure to resolve the issue.


"Is it possible to create a routine (stored procedure) that change a users password in the Oracle database?
The routine shall be possible to call from the application TSIC IM where a page can be designed that posts the user id, old password and the new password to the routine. The routine must send back a reply if the change was successful or not."

Thanks

Re: Privileges [message #438797 is a reply to message #438793] Tue, 12 January 2010 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
"Is it possible to create a routine (stored procedure) that change a users password in the Oracle database?

Yes, it is possible and quite simple.
It could be something like:
create or replace procedure change_password (
  p_username in varchar2,
  p_password in varchar2
)
is 
begin
  -- Check parameters
  if   p_username is null or p_password is null 
    or length(p_username) > 30 or length(p_password) < 8 -- minimum password length
    or upper(p_username) in ('SYS','SYSTEM') -- and so on, or check if username is in a list
  then 
    raise_application_error (-20000, 'Wrong parameter');
  end if;
  execute immediate 
    'alter user '||dbms_assert.schema_name(p_username)||
    ' identified by "'||p_password||'"';
end;
/

Must be created as a DBA user that has ALTER USER privilege directly granted to him, and then grant the EXECUTE privilege on this procedure to your user.
Add all controls you need depending on your environnement and policy.

Regards
Michel

[Updated on: Tue, 12 January 2010 05:30]

Report message to a moderator

Re: Privileges [message #438801 is a reply to message #438797] Tue, 12 January 2010 05:48 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> conn raghav/raghav
Connected.
SQL> create or replace procedure change_password (
  2    p_username in varchar2,
  3    p_password in varchar2
  4  )
  5  is
  6  begin
  7    -- Check parameters
  8    if   p_username is null or p_password is null
  9      or length(p_username) > 30 or length(p_password) < 8 -- minimum password length
 10      or upper(p_username) in ('SYS','SYSTEM') -- and so on, or check if username is in a list
 11    then
 12      raise_application_error (-20000, 'Wrong parameter');
 13    end if;
 14    execute immediate
 15      'alter user '||dbms_assert.schema_name(p_username)||
 16      ' identified by "'||p_password||'"';
 17  end;
 18  /

Procedure created.


SQL> conn system/bhanu
Connected.
SQL> create user ashish identified by bhanu account unlock;

User created.

SQL> grant create session to ashish;

Grant succeeded.

SQL> grant execute on change_password to ashish;
grant execute on change_password to ashish
                 *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist


SQL> grant execute on raghav.change_password to ashish;

Grant succeeded.

SQL> sho user
USER is "SYSTEM"
SQL> conn ashish/bhnau
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn ashish/bhanu
Connected.
SQL> set serveroutput on
SQL> exec change_password('SCOTT','ASHISH');
BEGIN change_password('SCOTT','ASHISH'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CHANGE_PASSWORD' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> exec raghav.change_password('SCOTT','ASHISH');
BEGIN raghav.change_password('SCOTT','ASHISH'); END;

*
ERROR at line 1:
ORA-20000: Wrong parameter
ORA-06512: at "RAGHAV.CHANGE_PASSWORD", line 12
ORA-06512: at line 1


SQL> exec raghav.change_password('SCOTT','oracleoca');

PL/SQL procedure successfully completed.

SQL> conn scott/oracleoca
Connected.
SQL> alter user system identified by oracleoca;

User altered.

SQL> conn system/oracleoca
Connected.
SQL>


So here My newly created user ahish has only limited no of privileges...As i granted him execute that procedure...so that he can change the password of another user which is having DBA privilege...so that again he can do the dba things which is also some security problem isn`t it?

Let me know If I am wrong..


sriram Smile
Re: Privileges [message #438803 is a reply to message #438801] Tue, 12 January 2010 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so that he can change the password of another user which is having DBA privilege...so that again he can do the dba things which is also some security problem isn`t it?

What about my remark:

Quote:
-- and so on, or check if username is in a list

In the test you can exclude all users having DBA role, or anything you want to enforce the security. My procedure was just a prototype that anyone can enhance to fit his needs.

Please when you post something remove all that is NOT in your demonstration; the first error for instance should NOT be posted as it has nothing with your post, just show that you make errors and we don't care about that. Will you also post all your typos?

Regards
Michel

[Updated on: Tue, 12 January 2010 05:56]

Report message to a moderator

Re: Privileges [message #438824 is a reply to message #438797] Tue, 12 January 2010 07:31 Go to previous messageGo to next message
madhavamanohar
Messages: 58
Registered: February 2009
Member
Thank you soo much Michel.
Re: Privileges [message #441871 is a reply to message #438824] Wed, 03 February 2010 09:56 Go to previous message
smunir362
Messages: 294
Registered: September 2007
Senior Member
Please also see.
http://www.orafaq.com/forum/?t=msg&th=150992&112796/
Previous Topic: Stop Export To DBA User
Next Topic: Lock out user between specific times
Goto Forum:
  


Current Time: Sun Sep 25 07:44:09 CDT 2016

Total time taken to generate the page: 0.04351 seconds