Home » SQL & PL/SQL » Client Tools » Oracle User Management
Oracle User Management [message #526532] Tue, 11 October 2011 08:46 Go to next message
tonyry
Messages: 4
Registered: October 2011
Junior Member
I'm looking for a basic password reset/unlock tool for use at a helpdesk. Environment is mixed 10 and 11G. Current procedure is log into specific database using SQL Plus and manually type out reset/unlock commands.

Any assistance locating an application, vbscript, etc. to do this is greatly appreciated!

Re: Oracle User Management [message #526553 is a reply to message #526532] Tue, 11 October 2011 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

since we have NO idea what your application is or how it is implemented, no answer is possible now.
Re: Oracle User Management [message #526566 is a reply to message #526553] Tue, 11 October 2011 10:14 Go to previous messageGo to next message
tonyry
Messages: 4
Registered: October 2011
Junior Member
Sorry, my bad. Our procedure is we log into SQL Plus with our credentials, then enter:

@c:\oracle\reset.sql

The contents of that .sql file is:

set serveroutput on
set feedback off
exec dbd_pwd_manager.change_password ('&UserToReset','&NewPassword');
set feedback on
PROMPT

There are around 200 oracle databases that we unlock or reset user passwords on. Each db has an individual password, but can be set to the same. I don't have version information beyond the databases either being 10 or 11G. My Oracle knowledge is very limited, I apologize if I'm missing something still. Just a lowly helpdesk person trying to make our process easier. Other departments have gui based tools, but our department is to cheap to pay licensing costs for 30 people.
Re: Oracle User Management [message #526568 is a reply to message #526566] Tue, 11 October 2011 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65835
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you want EXACTLY.

Regards
Michel
Re: Oracle User Management [message #526573 is a reply to message #526568] Tue, 11 October 2011 10:57 Go to previous messageGo to next message
tonyry
Messages: 4
Registered: October 2011
Junior Member
A tool that will:
Import a list of names from the ldap.ora/tnsnames.ora or manually type the name
Allow me to select from a list or search for a user account
Allow me to unlock that user account and/or reset the password.
Re: Oracle User Management [message #526575 is a reply to message #526573] Tue, 11 October 2011 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65835
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want it in client/server or 3-tier environment?
Does the user that will use it has the ALTER USER privilege or DBA role?

Regards
Michel
Re: Oracle User Management [message #526576 is a reply to message #526575] Tue, 11 October 2011 11:50 Go to previous messageGo to next message
tonyry
Messages: 4
Registered: October 2011
Junior Member
A client side tool that would go out and connect to specific db as needed
Do not have ALTER USER privilege to unlock or reset, so it must be DBA role?
Re: Oracle User Management [message #526578 is a reply to message #526576] Tue, 11 October 2011 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 65835
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have ALTER USER or DBA then you can unlock or change the password by yourself.
If you have not then DBA has to provide you a package to do the actions on behalf you.

Regards
Michel
Re: Oracle User Management [message #526633 is a reply to message #526578] Wed, 12 October 2011 02:26 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Are you trying to reset an individuals password on many instances in one go?
Re: Oracle User Management [message #526846 is a reply to message #526576] Thu, 13 October 2011 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65835
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a small Excel worksheet that will give you a start for an application.
The worksheet contains 2 sheets. The first one let you give your credentials (user, password and "base" which is the tnsnames alias for your target instance) and connect clicking on the "Connect" button.
Once you are connected, the second sheet is activated with the list of the Oracle accounts, their status, lock and expire dates.
You can select one or several accounts double-clicking in the "Select" column then you can lock, unlock or change the password of these accounts clicking on the "Lock", "Unlock" or "Change Psw" button.
There also is a "Refresh" button that allows you to refresh the information in the sheet.

For this version, you need the DBA privileges (actually SELECT on DBA_USERS and ALTER USER privileges are sufficient). If have not these ones, ask your DBA to create a view similar to the SELECT query inside the worksheet and a package with LOCK, UNLOCK and CHANGE_PSW functions and grant you the EXECUTE privilege on this package to allow you to make these actions.

This worksheet works with OO4O (Oracle Object for OLE), you have to install it from the Oracle client CD.

Regards
Michel



[Edit: program slighty modified to fix a display bug]

[Updated on: Fri, 14 October 2011 08:31]

Report message to a moderator

Re: Oracle User Management [message #527016 is a reply to message #526846] Fri, 14 October 2011 08:23 Go to previous message
Michel Cadot
Messages: 65835
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's another solution; about the same one but with no need of powerfull privileges.

Ask your DBA to execute the SQL script and grant you SELECT privilege on the created view and EXECUTE privilege on the created package.
Then you can use the Excel worksheet (same interface than the previous one).

Regards
Michel



[Edit: put privilege name in upper case]


[Updated on: Fri, 14 October 2011 14:34]

Report message to a moderator

Previous Topic: need help with setting up a connection
Next Topic: Not able to launch sql prompt after entering user name and pwd
Goto Forum:
  


Current Time: Tue Oct 16 05:28:47 CDT 2018