Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.
Course Of The Month
5 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Simple GUI for Oracle/SQLPlus Password Reset/Unlock
Posted on 2016-07-20
We have a lab system that uses Oracle as the backend database. Currently user account unlocks and password resets are done via 2nd line technicians, using the SQL*Plus commandline. We'd like to left-shift this process to service desk. However, we don't want them to have access to the commandline, as it could be dangerous if the wrong command is entered.
I'd like to know if anyone has already written a simple GUI that would allow user's accounts to be unlocked (ALTER USER “userid” ACCOUNT UNLOCK;) or passwords reset (ALTER USER “userid” IDENTIFIED BY password;).
If a GUI doesn't already exist, what would be required to create one? I have some experience with Visual Studio, so I'm thinking 3 Windows forms. The first/main form would be simple, 2 text boxes, username & password, and 2 buttons, unlock & reset (which would send the username/password along with 1 of the 2 commands above). There would be a menu at the top, login, connection settings, and exit. Exit is self explanitory. Login would open another form for the service desk analysts to login with their super user IDs. Connection settings would open a form to create/edit the oracle DB connection, or it would open the default Windows ODBC connection box, and allow you to create/edit a connection there.
I'd like to know if anyone has already written a simple GUI that would allow user's accounts to be unlocked (ALTER USER “userid” ACCOUNT UNLOCK;) or passwords reset (ALTER USER “userid” IDENTIFIED BY password;).
If a GUI doesn't already exist, what would be required to create one? I have some experience with Visual Studio, so I'm thinking 3 Windows forms. The first/main form would be simple, 2 text boxes, username & password, and 2 buttons, unlock & reset (which would send the username/password along with 1 of the 2 commands above). There would be a menu at the top, login, connection settings, and exit. Exit is self explanitory. Login would open another form for the service desk analysts to login with their super user IDs. Connection settings would open a form to create/edit the oracle DB connection, or it would open the default Windows ODBC connection box, and allow you to create/edit a connection there.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
3-
2
10 Comments
Active today
Even with a GUI all I need to do is uncompile it and I get the username and password.
Besides that is way to complicated.
Create a stored procedure in a privileged account (not SYS or SYSTEM) that accepts two parameters.
Grant execute on that procedure and create session to your helpdesk user.
Create a synonym on the procedure for the helpdesk user.
Done.
All that user can do is connect and execute your stored procedure.
Let them use sqlplus.
If you want to be really nice, create a sql script file that prompts them for the info and executes the procedure.
Besides that is way to complicated.
Create a stored procedure in a privileged account (not SYS or SYSTEM) that accepts two parameters.
Grant execute on that procedure and create session to your helpdesk user.
Create a synonym on the procedure for the helpdesk user.
Done.
All that user can do is connect and execute your stored procedure.
Let them use sqlplus.
If you want to be really nice, create a sql script file that prompts them for the info and executes the procedure.
Active today
Maybe two procedures to keep things minimal and less coding.
unlock_account(p_username in varchar2)
reset_password(p_username in varchar2, p_password in varchar2)
The nice thing about the procedure is you can add checks in there to make sure they don't reset SYS or SYSTEM passwords or any other accounts they shouldn't.
unlock_account(p_username in varchar2)
reset_password(p_username in varchar2, p_password in varchar2)
The nice thing about the procedure is you can add checks in there to make sure they don't reset SYS or SYSTEM passwords or any other accounts they shouldn't.
Active today
if you want a gui, you could look into APEX - that way there would be no need to install anything on the client. It would simply be a small web page hosted by the database itself.
building a simple one page app like this could be done fairly easily, would be secure and could fully leverage the pl/sql mentioned above.
building a simple one page app like this could be done fairly easily, would be secure and could fully leverage the pl/sql mentioned above.
Thanks for the reply. I didn't think about doing a stored procedure. I will forward this to the tech team, and see if they go for it! Sounds like the easiest option!
Regarding the GUI, I wasn't going to encode any username or password in the GUI itself. The login menu option would ask the service desk analyst for their username & password to login to oracle, so this would be user input and not stored. And the main form would be for the username & password of the end user being reset/unlocked, so again, the analyst would supply this during a raised service request.
Regarding the GUI, I wasn't going to encode any username or password in the GUI itself. The login menu option would ask the service desk analyst for their username & password to login to oracle, so this would be user input and not stored. And the main form would be for the username & password of the end user being reset/unlocked, so again, the analyst would supply this during a raised service request.
Active today
>>The login menu option would ask the service desk analyst for their username & password to login to oracle
If you give them a privileged username and password, what keeps them from using sqlplus or any other tool to connect to the database?
If you give them a privileged username and password, what keeps them from using sqlplus or any other tool to connect to the database?
@sdstuber, thanks, I've not heard of Apex before. I assume you mean Oracle Application Express (https://apex.oracle.com/en/)? I've have a read of the site & details. It looks interesting. We use ServiceNow as an ITSM tool, so maybe we could build something in APEX and use it with in the ServiceNow tool?
Active today
if you use apex (which runs inside the database) you can use application ids instead of database ids.
that way the technicians won't have an oracle account at all
that way the technicians won't have an oracle account at all
Active today
Yes, you can use apex (application express) with servicenow. We use servicenow where I work as well.
there's nothing special about the servicenow/apex relationship; but if you have a local db install (as opposed to their cloud installation) then it's fairly easy to build queries that read their tables to generate your own reports or other features.
there's nothing special about the servicenow/apex relationship; but if you have a local db install (as opposed to their cloud installation) then it's fairly easy to build queries that read their tables to generate your own reports or other features.
Featured Post
Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this post we will learn different types of Android Layout and some basics of an Android App.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month5 days, 14 hours left to enroll
707 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.