Pages

Saturday, 15 March 2014

Steps to Create a Readonly User in Oracle with Select grant on all tables

I have explained the steps to be followed to create readonly user.

Step 1:
Create a Role with basic permission which can be used as a template for additional users if needed. The permissions granted to a role can be granted to a user directly in case a role based access is not required.


create role read;
grant create session to read;
--Optional
grant connect to read;
Grant create synonym to read;

Grant the create session permission so that the user with this profile is able to login into the database, once a user is granted this role the create session need not be explicitly added as it is carried over from the role.
The grant to create synonym has been added so that one can create synonyms for the tables from other schema instead of accessing the tables with scheme identifier.

Step 2:
Grant Select on all the tables in a Schema to a the read role. This can be done using a PL/SQL block mentioned below. Lets assume that we have to grant select on all tables belonging to the schema/user GTUSER, the below mentioned query can be executed.


BEGIN
 FOR x IN (SELECT * FROM dba_tables WHERE owner='GTUSER')
 LOOP
  EXECUTE IMMEDIATE 'GRANT SELECT ON GTUSER.' || x.table_name || ' TO read';
 END LOOP;
END;
/

The above query will
Note: The above PL/SQL can be executed either by a user with dba privilege or the owner of the schema (provided the user has permission to execute), in which case one can use SELECT * FROM USER_TABLES which will list only the tables belonging to the current logged in user.

Step 3: Optional
The below mentioned PL/SQL can be executed to create synonyms for all the tables and synonyms by logging in as dba used. Note that this will create a public synonym that is accessible to all users and might have some undesired effect. Skip this step and use Step 4 in case private synonyms have to be created for readonly user.


BEGIN
  FOR x IN (SELECT * FROM dba_tables WHERE owner='GTUSER')
  LOOP
    EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || x.table_name ||' FOR ECECUSER.' || x.table_name || ' ';
  END LOOP;

  FOR x IN (SELECT * FROM dba_synonyms WHERE owner='GTUSER')
  LOOP
    EXECUTE IMMEDIATE 'CREATE OR REPLACE SYNONYM ' || x.synonym_name ||' FOR ECECUSER.' || x.synonym_name || ' ';
  END LOOP;
END;
/


Step 4.1:
Spool the create synonym (table and synonym from the target schema) query  into a file. The file would contain the create synonym script for all the tables and synonym which can be executed on the readonly user.
Login to GTUSER (user having the table for which synonym has to be created) or to a user having dba privelege and execute the queries mentioned.


set pagesize 1000;
set linesize 100;
set heading off;
set feedback off;
spool tablesynonym.sql
select 'CREATE OR REPLACE SYNONYM ' || table_name || ' FOR GTUSER.'||table_name ||';' from user_tables;
spool off;
spool synonym.sql
select 'CREATE OR REPLACE SYNONYM ' || synonym_name || ' FOR GTUSER.'||synonym_name||';' from user_synonyms;
spool off;


--Or select from dba_tables depending on the user access available
spool tablesynonym.sql
select 'CREATE OR REPLACE SYNONYM ' || table_name || ' FOR GTUSER.'||table_name ||';' from dba_tables where OWNER = 'GTUSER';
spool off;
spool synonym.sql
select 'CREATE OR REPLACE SYNONYM ' || synonym_name || ' FOR GTUSER.'||synonym_name ||';'from dba_synonyms where OWNER = 'GTUSER';
spool off;

Note:
1) Set pagesize can be set to value that is more than the number of tables in the schema
2) Set linesize can be set 100 or more to ensure that the output for each table is put into a single line.
3) Set heading to off so that the query name is not created.
4) Use either db_tables when you have dba priveledge else login into the user (say GTUSER) and use user_tables as all users usually can query this to get the tables belonging to them.

Step 4.2:
Login into readonly user and execute the spooled file>


@tablesynonym.sql
@synonym.sql

With the above we have covered the following:
1) Creating a read profile
2) Creating a readonly user
3) Granting select access on tables of a particular schema to read role.
4) Creating synoyms for all tables and synonyms of the main schema in readonly user profile.


No comments:

Post a Comment