OraFAQ Forum: SQL & PL/SQL » procedure to give ALTER USER to another

Home » SQL & PL/SQL » SQL & PL/SQL » procedure to give ALTER USER to another (Oracle RDBMS 11G and above)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

procedure to give ALTER USER to another [message #641929]

Thu, 27 August 2015 13:37

wolffjw
Messages: 3
Registered: August 2015
Location: Florida

Junior Member

I'm a DBA and I have some cases wherein a certain user needs ability to alter another user's password since he is the application administrator.

Naturally, I do not want him to be able to alter Oracle administrative accounts, or any other administrative accounts for that matter. Only actual "user" accounts.

Can someone please help me with this? I know basically that a procedure owned by someone with alter user privilege needs to be written with: execute immediate alter user '&user' identified by '&pass', and then execute grant on it to the person who I want to be able to execute it.

But I don't know much about writing procedures and how to do the checking that it is not an Oracle account, or an application admin account. I'm assuming to use a where clause where it only allows changing a user's password that has USERS as the default tablespace, or perhaps DEFAULT as the profile since they would only apply to actual end users and not system accounts (in our case because of the way we manage our databases for security reasons).

Can someone help please with the code for the procedure?

[Updated on: Thu, 27 August 2015 13:39]

Report message to a moderator

Re: procedure to give ALTER USER to another [message #641930 is a reply to message #641929]

Thu, 27 August 2015 13:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

is application 3-tier

Report message to a moderator

Re: procedure to give ALTER USER to another [message #641932 is a reply to message #641930]

Thu, 27 August 2015 13:53

wolffjw
Messages: 3
Registered: August 2015
Location: Florida

Junior Member

Yes, and No. Some users interface to the application, which interfaces to the database, and other users interface directly to the database.

Report message to a moderator

Re: procedure to give ALTER USER to another [message #641934 is a reply to message #641932]

Thu, 27 August 2015 13:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

wolffjw wrote on Thu, 27 August 2015 11:53

Yes, and No. Some users interface to the application, which interfaces to the database, and other users interface directly to the database.

for 3-tier, problem and solution must be part of application code.
For simplicity & clarity I would write a procedure, CHANGE_MY_PASSWORD, one for every schema to be managed;
such that this procedure an only change the password for its own schema/user; then issue GRANT EXECUTE as needed.

Report message to a moderator

Re: procedure to give ALTER USER to another [message #641936 is a reply to message #641929]

Thu, 27 August 2015 14:03

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I know basically that a procedure owned by someone with alter user privilege needs to be written with: execute immediate alter user '&user' identified by '&pass', and then execute grant on it to the person who I want to be able to execute it.

This is correct.

Quote:

But I don't know much about writing procedures and how to do the checking that it is not an Oracle account, or an application admin account.

You will an example of code at the bottom of this topic.
In this code the procedure "change_psw" can be modified to add some checks about the accounts which can be modified.

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Report message to a moderator

Re: procedure to give ALTER USER to another [message #641941 is a reply to message #641936]

Thu, 27 August 2015 14:55

wolffjw
Messages: 3
Registered: August 2015
Location: Florida

Junior Member

Thanks Michel. That's kind of similar to what I want to do, but I'm going to have to review it better to understand how it works.

Obviously, I cannot give out alter user privilege to anyone except our own internal DBADMIN account, which I plan to own the procedure. Then, once the procedure is created, I will to grant it to a user that does not have alter user privilege. Is that how the procedure works?

I see it does a lot of the type checking I need it to do, such as checking that users are not one of the oracle accounts, and obviously, I can modify that query as needed.

Report message to a moderator

Re: procedure to give ALTER USER to another [message #641942 is a reply to message #641941]

Thu, 27 August 2015 15:03

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Is that how the procedure works?

Yes.
"DBADMIN" is what I call target user in the script.
"Current user" is the user who executes the script, creates the package in target user schema and grants it the needed privileges.

Report message to a moderator

Previous Topic:	Finding matching values
Next Topic:	Need data beween date range

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 29 11:27:10 CDT 2025