What is Oracle?
Oracle is one of the most powerful database management system based on Relational Database Model, provided by Oracle Corporation along with fully integrated database application development and administration tools. It uses Structured Query Language (SQL) for database access and its own proprietary procedural language PL/SQL for application development along with Java programming support.
User Login Without Password
Sometimes, the DBA needs to log into a user's account to make a change, such as to grant a privilege on a user's table to another user, but, may not know what the user's password is, or, may need to make changes to a set of users from a script, but, doesn't want to include their passwords in the script itself. Oracle provides an undocumented "identified by values" clause in the "alter user" command that can be used for this purpose. Since the dba_users table contains the encoded password for each user, this value can be used to generate an "alter user" command to reset the user's password back to its original value. Then, from user system or another DBA user, you can alter the user's password to a known value (such as "whatever"), log into the user's account using "connect userid/whatever", make the changes that are needed, connect back to the system account, and run the generated "alter user" command to put the original password back.
The following SQL generates a password change script (setpw.sql) to set all users to a known password ("whatever"), and, another script (resetpw.sql) to set all users back to their original passwords. This would be used as part of another script to generate the password change scripts, run setpw.sql, log in and make the changes that are needed, and immediately run resetpw.sql to put the passwords back. Note that the users won't be able to log in during that time, since their passwords are not valid while you are running the script.
select 'alter user ' || username || ' identified by whatever;' from dba_users;
select 'alter user ' || username || ' identified by values ''' || password || ''';'
Getting Table Name and Probable Table Owner
Using the Defines Using Select tip, here is a snippet of SQL code that can be used to prompt for and get the table name from the user, as well as the probable default table owner (from the first dba_tables entry for that table name, or, if not found, using the current user's ID) which can be overridden by the user. The resulting entries are put into the tablename and tableowner variables as upper case values.
set showmode off echo off
set termout on
accept tablename char prompt 'Enter table name: '
set termout off
column tablename new_value tablename
select upper('&tablename') tablename from dual;
define tableowner = 'DUMMY'
column tableowner new_value tableowner
select owner tableowner from dba_tables
where table_name = upper('&tablename') and rownum = 1;
select username tableowner from user_users
where '&tableowner' = 'DUMMY' and rownum = 1;
set termout on
accept tableowner2 char prompt 'Enter table owner, if not &tableowner: '
set termout off
tableowner from dual;