Create a read only user in Oracle

27 Mar

If you want to create a read only user for a schema, you can use the following query:
CREATE USER ro_user
IDENTIFIED BY ro_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION TO ro_user;

CREATE ROLE ro_role;

BEGIN
FOR x IN (SELECT *
FROM dba_tables
WHERE owner = ‘CHEMA_XX’)
LOOP
EXECUTE IMMEDIATE
‘GRANT SELECT ON SCHEMA_XX.’ || x.table_name || ‘ TO ro_role’;
END LOOP;
END;

GRANT ro_role TO ro_user;

 

Read more

http://stackoverflow.com/questions/7502438/oracle-how-to-create-a-readonly-user

Advertisements

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

%d bloggers like this: