Create a read only user – Oracle

To create a user in Oracle that just have read permissions in the existing schemas owned by other users, follow these steps

. A read only user is perfect in case you don't want that user to overwrite or delete data by any chance.

First Create a user, let's say "prod":

SQL> create user prod identified by anypassword default tablespace

tableSpaceName quota unlimited on tableSpaceName;

User created

//You must specify a default tablespace in order to let them login.

Then Grant Session privilege so that they can login and perform any task:

SQL> GRANT CREATE SESSION TO prod;

Grant succeeded.

There are various priveleges and roles given to a user, but here we will only

discuss about giving select privilege in order to make this user a read only user:

Let's say there is a Schema schema1 with one table in it table1, then do:

SQL> grant select on schema1.table1 to prod;

Grant succeeded.

Now if there is a Schema schema2 with more than one table in it, above

query wouldn't work:

SQL> grant select on schema2.* to prod;

grant select on schema.* to prod

*

ERROR at line 1:

ORA-00903: invalid table name

Note: I tried * because i wanted user prod select privelege to all the tables

inside schema2

Here is the script to give select privilege to all the tables:

BEGIN

FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='schema2 owner') LOOP

EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to prod';

END LOOP;

END;

/

Create a read only user Oracle

By: vishal

POST COMMENT

Foreing Exchange Marijuana Detox Procedure bristol logistics in reverse Dongfeng Cummins Heavy Perfect Zest Greed Is Not Good A Plague Improve Your Growth With The Best Growing Lamps Find Out The Best Way To Help The Plants With Hydroponic Grow System Prefinished Vs. Unfinished Wood Floors The Purpose Of A Shaver Light Uplighters Are A Bright Idea The Smart Way To Cut Back On Your Expenses Avail Nuvi 765t Best Price Now