|
Last week I discussed a reporting project and a
password / permissions problem, Problems with Permissions and Passwords. The solution from
last week, ALTER USER...IDENTIFIED BY VALUES... works,
but is not the one we used.
The solution we used takes advantage of
Since this is a new week, let me repeat the
problem from last week. 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. And last week's tip
discussed that solution.
What we actually did was write a stored
procedure that granted the necessary permissions, created the
procedure in the PROJ_MGT schema, executed that procedure, and
then dropped it.
First, let's see what happens when the user
PROJ_RPT tries to access the PROJECTS table in the PROJ_MGT
schema.
CONNECT proj_rpt/proj_rpt
Connected.
SELECT * FROM proj_mgt.projects;
SELECT * FROM proj_mgt.projects
*
ERROR at line 1:
ORA-00942: table or view does not exis
To correct this permissions problem, we connect as a DBA and
create a procedure in the PROJ_MGT schema that grants the
appropriate permissions to PROJ_RPT. The reason this works
is that the DBA has permissions to create and execute a
procedure in any schema, but by default, a procedure is created
with definers rights, meaning the procedure executes as
if it was being executed by the schema in which it is stored. In
this case, that schema is PROJ_MGT, which obviously can grant
permissions on tables in its own schema.
CONNECT system/password
Connected.
--
--
Create the procedure in the proj_mgt schema
CREATE PROCEDURE proj_mgt.p_temp AS
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON
projects TO proj_rpt';
END;
/
Procedure created.
--
Execute the procedure
EXECUTE proj_mgt.p_temp
PL/SQL procedure successfully completed.
--
Remove the procedure
DROP PROCEDURE proj_mgt.p_temp;
Procedure dropped.
Now that the permissions have been granted, let's reconnect as proj_rpt and try to access the table.
CONNECT proj_rpt/proj_rpt
Connected.
SELECT * FROM proj_mgt.projects;
no rows selected
Success! We were able to connect and access
the table with no problem.
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.
|
