Recently I did a small reporting project for a client where I ran into a permissions problem. The client was running a project management application in an Oracle8i database, and wanted to maintain a historical record of changes of estimated completion dates and statuses. This information would then be used to improve the accuracy of future estimates. However, we ran into a problem with permissions and passwords.

Four schemas / users are involved in this project

• PROJ_MGT - Existing schema containing the project management tables

• PROJ_RPT - New schema containing the project reporting tables

• PROJ_USER - Existing user through which the users access the existing project management tables and will access the project reporting tables.

• SYSTEM - Oracle DBA

The problems began early when I found that no one knew the password to PROJ_MGT schema. The new PROJ_RPT user needed SELECT permission against a small set of tables in the PROJ_MGT schema, but without the PROJ_MGT password, there was no way to grant those permissions.

Everyone's first reaction to this problem is just to connect as a user with DBA privileges, because a DBA can do anything. But there are some limits to a DBA's privileges and, in Oracle8i (although not Oracle9i), this is one of the limits. If you connect as SYSTEM and try to grant the permission, the following error is returned.

GRANT SELECT ON proj_mgt.projects TO proj_rpt;
GRANT SELECT ON proj_mgt.projects TO proj_rpt
*
ERROR at line 1:
ORA-01031: insufficient privileges

The next suggestion was to change the password for PROJ_MGT to a new password, so we then would know what the password is and can use the PROJ_MGT user to grant the necessary permissions. And a DBA can easily do that. But what if the PROJ_MGT is embedded in a script or the packaged application? And while the DBA can easily change the password, can the password be changed back to the original one?

And the answer to that question is yes, a password can easily be changed to a new password and then back to the old one by the DBA using the following ALTER USER command

ALTER USER username IDENTIFIED BY VALUES 'encryptedpassword';

where username is the user for whom the password is to be changed and encryptedpassword is the password encryption from the dba_users data dictionary view.

So the sequence of events is the following:

-- First, for this example, we actually know the password for PROJ_MGT is actually PROJ_MGT
-- but let's pretend we don't
--
-- As a DBA, get the password encryption for the user from the dba_users data dictionary view
SELECT username, password
FROM   dba_users
WHERE  username = 'PROJ_MGT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
PROJ_MGT                       21EB180E11B22A2F

-- Set the password to something we know
ALTER USER proj_mgt IDENTIFIED BY temp_pass;

User altered.

-- Connect as proj_mgt to grant the permissions
CONNECT proj_mgt/temp_pass
Connected.
--
-- Grant the necessary privileges
GRANT SELECT ON proj_mgt.projects TO proj_rpt;

Grant succeeded.

-- Reconnect as the DBA
CONNECT system/password
Connected.
--
-- Change the password back to what it was
ALTER USER proj_mgt IDENTIFIED BY VALUES '21EB180E11B22A2F';

User altered.

-- And to prove the password has been set back to the original one
CONNECT proj_mgt/proj_mgt
Connected.

The above solution works, with a very small risk that someone or something would try to connect as PROJ_MGT while the password had been changed. However, it is not the solution I used, which will be next week's tip.

Note: This tip was tested on Oracle8i.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.