Read-Only access to an existing Oracle
Schema
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='ASHOKAN')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO my_read_only_role';
END LOOP;
FOR x IN (SELECT
table_name FROM dba_views WHERE
owner='ASHOKAN')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.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 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.
ALTER
SESSION SET current_schema = 'ASHOKAN';
