Read Only User
“Oh mighty DBA” said the developer, “You are wise and very attractive. Can I please have access to the TEST system of my application? I need to debug an issue that QA found and I cannot reproduce”.
(I’m paraphrasing a bit).
“No way. You will change few things while you are there and ruin the test cycle. Even if you won’t, QA will think you did and this will ruin the test cycle.”
“I see your point, wise and attractive DBA. In this case, can I have just read-only access to the system?”
(I hate this part in the conversation. When a request sounds innocent and trivial, but is in-fact complicated and full of trickery.)
“I’ll see what I can do. But you don’t get to execute any functions. Just select on table and views. And you’ll need to prefix the table names with the name of the original user.”
(Well, actually giving synonyms is easy enough. If he’ll insist, I’ll add them).
I wrote the function that does it. It is horrible in several ways:
- It uses “execute immediate”.
- No bind variables. I don’t think I can use them in this case, but it still means I’m doing bad things to the library cache.
- I add “with grant” privilege to all object privileges the original user had. I did not want to touch the original user, but as I explained before, I had no choice. I could have been more selective here, though.
I hope it will be useful, but I post it mostly as a rough reference. Think twice before you use it.
CREATE OR REPLACE
PROCEDURE GRANT_READ_ONLY(RO_USER in varchar2, ORIG_USER in varchar2,with_grant in BOOLEAN
default true) AUTHID current_user AS stmt varchar2(4000);
get_grant_objs varchar2(4000);
TYPE cur_typ IS REF CURSOR;
c_cursor cur_typ;
Type user_priv_typ is record (
table_name all_tab_privs.table_name%type,
privilege all_tab_privs.privilege%type,
grantor all_tab_privs.grantor%type);
user_priv_rec user_priv_typ;
BEGIN
if (with_grant) then
get_grant_objs := 'select table_name,privilege,grantor from dba_TAB_PRIVS dtp
join dba_objects do on dtp.grantor=do.owner and dtp.table_name=do.object_name
where grantee=''' || ORIG_USER || ''' order by TABLE_NAME';
open c_cursor for get_grant_objs;
LOOP
FETCH c_cursor into user_priv_rec;
EXIT WHEN c_cursor%NOTFOUND;
stmt := 'GRANT ' || user_priv_rec.privilege || ' ON ' || user_priv_rec.grantor || '.' ||
user_priv_rec.table_name || ' TO ' || ORIG_USER || ' WITH GRANT OPTION';
execute immediate stmt;
--dbms_output.put_line(stmt);
END LOOP;
CLOSE c_cursor;
end if;
for obj in
(
select object_name from all_objects
where owner=ORIG_USER
and object_type in ('SEQUENCE','TABLE','VIEW','MATERIALIZED VIEW'))
LOOP
stmt := 'GRANT SELECT ON ' || ORIG_USER || '.' || obj.object_name || ' TO ' || RO_USER;
execute immediate stmt;
--dbms_output.put_line(stmt);
END LOOP;
END;
RSS
Email
