Tuesday 22 October 2013

Procedure to give read only privileges in oracle

This is script for table :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name
FROM dba_tables
 where owner='ERECON14'
  and table_name in ('WF_USERTYPE', 'WF_WORKFLOWTESTING') ;
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON '||tables.table_name|| TO GPS;
execute immediate sql_txt;
END LOOP;
END;
/

OR


SQL> create user b identified by b;

User created.

SQL> grant select any table,create session to b;


FROM SOURCE SCHEMA


GRANT SELECT ON AUDITTRAIL TO GPS

GRANT SELECT ON  TO GPS ;

SQL>  select * from recon14.WF_WORKFLOWTESTING;




SELECT table_name
FROM dba_tables
 where owner='ERECON14'
  and table_name in ('WF_USERTYPE', 'WF_WORKFLOWTESTING')


select * from tab where tname = 'AUDITTRAIL';

No comments:

Post a Comment