See also - https://stackoverflow.com/questions/7502438/oracle-how-to-create-a-readonly-user
[oracle@collabn1 ~]$ cat read_only.sh
export ORACLE_SID=emrep
sqlplus -silent /nolog<<EOF
connect / as sysdba
set pagesize 0 linesize 32767 trimspool on echo off feedback on termout off colsep "" verify off
set serveroutput on size unlimited
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';
DECLARE
sql_text varchar2(200) := '';
exist_count number := 0;
BEGIN
for usr in (select username from all_users where username = 'RUSSELL') loop
select count (*) into exist_count from dba_roles where role = usr.username || '_READONLY';
if exist_count = 0 then
sql_text := 'create role ' || usr.username || '_READONLY NOT IDENTIFIED';
execute immediate sql_text;
dbms_output.put_line(sql_text);
for obj in (select object_name from all_objects where owner = usr.username and object_type in ('TABLE','VIEW')) loop
sql_text := 'grant select on ' || usr.username || '.' || obj.object_name || ' to ' || usr.username || '_READONLY';
BEGIN
execute immediate sql_text;
EXCEPTION
WHEN OTHERS then null;
END;
end loop;
end if;
end loop;
END;
/
exit
EOF
SQL> l
1* select * from dba_tab_privs where grantee = 'RUSSELL_READONLY'
1* select * from dba_tab_privs where grantee = 'RUSSELL_READONLY'
SQL> /
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE COM TYPE INHERITED
------------------------------ --------------- -------------- ------------------------------ --------------- --- --- --- ----- ----------
RUSSELL_READONLY RUSSELL TEST RUSSELL SELECT NO NO NO TABLE NO
------------------------------ --------------- -------------- ------------------------------ --------------- --- --- --- ----- ----------
RUSSELL_READONLY RUSSELL TEST RUSSELL SELECT NO NO NO TABLE NO
SQL> l
1* select * from dba_role_privs where grantee = 'IAN'
1* select * from dba_role_privs where grantee = 'IAN'
SQL> /
GRANTEE GRANTED_ROLE ADM DEL DEF COM INHERITED
------------------------------ -------------------- --- --- --- --- ----------
IAN RUSSELL_READONLY NO NO YES NO NO
------------------------------ -------------------- --- --- --- --- ----------
IAN RUSSELL_READONLY NO NO YES NO NO
1* select a.grantee, a.granted_role, b.owner, b.privilege from dba_role_privs a, dba_tab_privs b where a.granted_role = b.grantee and a.granted_role = 'RUSSELL_READONLY'
SQL> /
GRANTEE GRANTED_ROLE OWNER PRIVILEGE
------------------------------ -------------------- --------------- ---------------
IAN RUSSELL_READONLY RUSSELL SELECT
SYS RUSSELL_READONLY RUSSELL SELECT
------------------------------ -------------------- --------------- ---------------
IAN RUSSELL_READONLY RUSSELL SELECT
SYS RUSSELL_READONLY RUSSELL SELECT
#!/bin/bash
create_ro()
{
#use the -s to suppress output, to spool to file use <<EOF >> output.txt
sqlplus -s system/………….@...........scan:1521/.....SVC <<EOF
set serveroutput on;
set verify off;
set echo off;
set pagesize 0;
set head off;
DECLARE
v VARCHAR2(150);
w VARCHAR2(150);
CURSOR c is select 'grant select on $FROMUSR.'||table_name|| ' to $TOUSR;' from dba_tables where owner='$FROMUSR';
CURSOR d is select 'grant select on $FROMUSR.'||view_name|| ' to $TOUSR;' from dba_views where owner='$FROMUSR';
BEGIN
OPEN c;
LOOP
FETCH c into v;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(v);
END LOOP;
CLOSE c;
OPEN d;
LOOP
FETCH d into w;
EXIT WHEN d%NOTFOUND;
dbms_output.put_line(w);
END LOOP;
CLOSE d;
END;
/
EOF
}
# Main processing
[ $# -eq 0 ] && { echo "Usage: supply the 2 users to clone from / to"; exit 1; }
FROMUSR=$1; export FROMUSR
TOUSR=$2; export TOUSR
echo " "
echo "Run output against " $2
echo "----------------------------------"
create_ro
exit
