CODEWITHSUNDEEP
oracle-databaseprivileges
Uma
Uma

Reputation: 1

can we provide an oracle privilege to user which can alter only password of other user

I have created a procedure as shown below. This procedure is present in schema1 and trying to modify the password of another schema/user let's say schema2. To achieve this the user schema1 must have altered user privilege but I cannot provide the alter user privilege to schema1 due to some restrictions on the application level. I tried to query using ALTER SESSION in the procedure but it is not working. Can someone help me with the solution?

code:

Procedure p_demo(p_schema in varchar, p_pswd in varchar2)
is
begin
execute immediate 'alter session set current_schema ='||p_schema;
execute immediate 'alter user '||p_schema||' identified by '||p_pswd;
end;

Upvotes: 0

Views: 1566

Answers (3)

Uma
Uma

Reputation: 1

Cant we do as shown below: Tried doing this but it didn't work. What i am trying to do is basically give alter user privileges to one role and assign that role to my oracle procedure. Create role role_name; GRANT ALTER USER TO role_name grant all on role_name to procedure

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

There are 3 users involved in this example:

  • scott, who is trying to change someone else's password (that's your schema1)
  • mike, whose password should be changed (your schema2)
  • mydba, who is granted DBA role in my database (if you don't have such an user, SYS would do, but - you'd rather have your own "DBA" user, don't mess up with SYS if you don't have to)

Connected as scott, I can't modify mike's password:

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

I'm going to connect as mydba and create a stored procedure which looks like yours:

SQL> connect mydba/mypwd@c11gt
Connected.

SQL> create or replace procedure p_demo (p_schema in varchar2, p_pswd in varchar2) is
  2  begin
  3    execute immediate 'alter user ' || p_schema || ' identified by ' || p_pswd;
  4  end;
  5  /

Procedure created.

SQL> exec p_demo('mike', 'lion');

PL/SQL procedure successfully completed.

OK; it works. I'll grant execute privilege on it to scott:

SQL> grant execute on p_demo to scott;

Grant succeeded.

Back to scott; see what he can do now:

SQL> connect scott/tiger@c11gt
Connected.

SQL> exec mydba.p_demo('mike', 'friday');

PL/SQL procedure successfully completed.

Do mike's credentials work?

SQL> connect mike/friday@c11gt
Connected.

SQL>

Yes, everything's fine.

So: you don't have to grant alter user to schema1; let it use procedure owned by a privileged user who can change someone else's password.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231651

Changing the current_schema has no impact on permissions or what user is currently logged in. It merely changes how object name resolution works. If you query an object foo when current_schema is set to schema1, Oracle looks in the schema1 schema rather than in the current user's schema. It does nothing to give you access to the schema1.foo table.

I'm not quite sure that I understand the goal. If you are trying to ensure that only the schema2 user can change the schema2 user's password, you can define the procedure to use invoker's rights rather than definer's rights.

create or replace procedure change_my_password( p_username in varchar2, 
                                                p_password in varchar2 )
  authid current_user
is
begin
  execute immediate 'alter user '||p_username||' identified by '||p_password;
end;
/

If the goal is to have the procedure owned by schema1 and to give users other than the schema2 user permission to change schema2's password (i.e. to allow an application user or a helpdesk user to reset the user's password), schema1 would likely need to have the alter user permission. Otherwise, it's probably not doable.

If you're really desperate, you could potentially use the undocumented (and I emphasize undocumented here, subject to change at any time, may have weird side effects, may tend to make Oracle Support unhappy) dbms_sys_sql package. That's the package that APEX uses internally to run code as other users. I don't imagine that a sane DBA would consider giving an application user execute access on that package rather than the much (much, much) safer alter user permission but if you're trying to work around some corporate policy and you're not much concerned about actual security...

Upvotes: 3

Related Questions