понедельник, 12 мая 2014 г.

Create oracle readonly user for all schemas (including system)

sqlplus "/as sysdba"

create role OIM_REPORTER;

-- Assigning Privileges to role
set pagesize 0
spool grant_ro.sql
select 'grant select on '||owner||'.'||table_name||' to OIM_REPORTER;' from all_tables;
select 'grant select on '||owner||'.'||view_name||' to OIM_REPORTER;' from all_views;
spool off
-- Execute it
@grant_ro.sql

CREATE USER ro_user  PROFILE "DEFAULT"
    IDENTIFIED BY "somepassword" DEFAULT TABLESPACE "USERS"
    TEMPORARY TABLESPACE "TEMP"
    ACCOUNT UNLOCK;
-- Permit the user connect to database
GRANT CREATE SESSION, ALTER SESSION TO ro_user;
-- Assigning role to user
GRANT OIM_REPORTER TO ro_user;

Note:
If you create some new tables you have to grant the permission to role.

Комментариев нет:

Отправить комментарий