Read only users


 Used this to create read only users - edit as appropriate.

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'

SQL> /
GRANTEE          OWNER        TABLE_NAME     GRANTOR        PRIVILEGE      GRA HIE COM TYPE  INHERITED
------------------------------ --------------- -------------- ------------------------------ --------------- --- --- --- ----- ----------
RUSSELL_READONLY        RUSSELL        TEST       RUSSELL        SELECT      NO  NO  NO  TABLE NO

SQL> l
  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

  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




#!/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