subject: Create a read only user – Oracle [print this page] 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';
