4

we've got an Oracle 11g Schema that our application uses to select, insert and update, but we've had a request from one of our customers to provide read-only access to the same base tables and views owned by the application.

Other than synonym'ing all of the application owned tables into a new account (or making the synonyms public), how would I go about doing this?

Any help or pointers to the approach or Oracle feature I should be looking at would be most appreciated, thank you!

3 Answers 3

8

You'll need a separate account to grant the read-only access to. I would suggest adding a role that you grant read-only access to as well-- you can then re-use that role if more users need this access in the future.

CREATE ROLE my_read_only_role;

BEGIN
  FOR x IN (SELECT table_name FROM dba_tables WHERE owner=<<schema name>>)
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO my_read_only_role';
  END LOOP;
  FOR y IN (SELECT view_name FROM dba_views WHERE owner=<<schema name>>)
  LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || y.view_name || ' TO my_read_only_role';
  END LOOP;
END;
/

GRANT my_read_only_role TO new_customer_account;

Once that is done, the new account will need to prefix the table names with the schema name to select the data. Alternatively, you could create public synonyms for each object (you can add another EXECUTE IMMEDIATE to each loop in the code above). Or you could have the user run the command

ALTER SESSION SET current_schema = <<schema name>>

on login. You could also create a login trigger in the new account that would do this automatically. That will cause <<schema name>> to be implicitly added as the schema prefix. It does not affect the privileges of the session-- the user still has the read-only privileges, the default schema name has just been changed.

1
  • Thanks Justin - the 'current_schema' is just the ticket I was looking for, as I want the tables and views to look as though they belong to the read-only user. Cheers!
    – Andrew
    Aug 10, 2009 at 10:11
2

I created the new_customer_account without quotas as sysdba:

create user new_customer_account
  IDENTIFIED BY new_password
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
GRANT CONNECT TO new_customer_account;
GRANT CREATE SESSION TO new_customer_account;
ALTER USER new_customer_account QUOTA 0 ON users;

The application_user granted the select-right on the relevant views (and/or tables):

BEGIN
 FOR i IN (SELECT *
             FROM all_views
            WHERE owner = ''
              AND relevant_where_clause) LOOP
  EXECUTE IMMEDIATE 'GRANT SELECT ON application_user.'
          || i.view_name || ' TO new_customer_account';
 END LOOP;
END;

After that, the new_customer_account creates new Synonyms:

BEGIN
 FOR i IN (SELECT *
             FROM all_views
            WHERE owner = 'application_user'
              AND relevant_where_clause) LOOP
  EXECUTE IMMEDIATE 'CREATE SYNONYM ' || i.view_name
          || ' FOR application_user.' || i.view_name;
 END LOOP;
END;
1

I'm pretty sure that you have to create a new account, and only grant select to that user.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .