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:

  1. It uses “execute immediate”.
  2. 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.
  3. 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;
Advertisements

3 Comments on “Read Only User”

  1. Hey wise and attractive DBA, do you mind using double quotes in your execute immediate statements do avoid errors and/or sql injection?

    Like this:
    stmt := 'GRANT SELECT ON "' || ORIG_USER || '"."' || obj.object_name || '" TO "' || RO_USER|| '"';

  2. Chris says:

    Read only in test systems is OK, right up until someone writes a really good query that grinds the whole thing to a halt.

    another favorite is “can we have a sysdba account in development”…

    My usuall method for this is to create a role for read only rights and spool the grants out to a file, then add the users to a resource maanger group to limit their CPU usage, still can’t stop them using up all the temp space though.

  3. Freek says:

    Chris,

    You could create a separate temp tablespace and assign this tablespace to their account.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s