Roles refresh

select 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO HMF05236;' from dba_tables where OWNER='CA_SASQA';
select 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO HMF05236;' from dba_objects where OWNER='PRICING_ADS' and object_type='VIEW';



For tables read write

select 'GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON '||OWNER||'.'||TABLE_NAME||' TO WGPCCWUSR_RW_ROLE;' from dba_tables where OWNER='WGPCCWUSR';

select 'GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON '||OWNER||'.'||TABLE_NAME||' TO WGPISSUSR_RW_ROLE;' from dba_tables where OWNER='WGPISSUSR';

select 'GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON '||OWNER||'.'||TABLE_NAME||' TO WGPOMSUSR_RW_ROLE;' from dba_tables where OWNER='WGPOMSUSR';

select 'GRANT ALTER, DELETE, INSERT, SELECT, UPDATE ON '||OWNER||'.'||TABLE_NAME||' TO UVODS_RW_ROLE;' from dba_tables where OWNER='UVODS';

For tables read only
select 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO WGPCCWUSR_RO_ROLE;' from dba_tables where OWNER='WGPCCWUSR';

select 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO WGPISSUSR_RO_ROLE;' from dba_tables where OWNER='WGPISSUSR';

select 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO WGPOMSUSR_RO_ROLE;' from dba_tables where OWNER='WGPOMSUSR';

select 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO UVODS_RO_ROLE;' from dba_tables where OWNER='UVODS';

For views read only
select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO WGPCCWUSR_RO_ROLE;' from all_views where OWNER='WGPCCWUSR';

select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO WGPISSUSR_RO_ROLE;' from all_views where OWNER='WGPISSUSR';

select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO WGPOMSUSR_RO_ROLE;' from all_views where OWNER='WGPOMSUSR';

select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO UVODS_RO_ROLE;' from all_views where OWNER='UVODS';

For views read write
select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO WGPCCWUSR_RO_ROLE;' from all_views where OWNER='WGPCCWUSR';

select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO WGPISSUSR_RO_ROLE;' from all_views where OWNER='WGPISSUSR';

select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO WGPOMSUSR_RO_ROLE;' from all_views where OWNER='WGPOMSUSR';

select 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO UVODS_RO_ROLE;' from all_views where OWNER='UVODS';






select * from all_views where owner='UVODS';




BEGIN
  SYS.DBMS_SCHEDULER.DROP_JOB
    (job_name  => 'DAILY_PRIV_REFRESH');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'DAILY_PRIV_REFRESH'
      ,start_date      => TO_TIMESTAMP_TZ('2013/12/19 11:17:06.141006 America/Los_Angeles','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'DECLARE
v_schema VARCHAR2(30);
v_privilege VARCHAR2(2);

CURSOR c_role IS
SELECT role
FROM dba_roles WHERE role LIKE ''%R__ROLE'';

BEGIN
FOR r_role IN c_role
LOOP
v_schema := SUBSTR(r_role.role, 0, INSTR(r_role.role, ''_'', 1, 1) - 1);
v_privilege := SUBSTR(r_role.role, INSTR(r_role.role, ''_'', 1, 1) + 1, 2);

HISNA_DBA.refresh_schema_privs( v_schema, r_role.role, v_privilege);
END LOOP;
END;'
      ,comments        => NULL
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'DAILY_PRIV_REFRESH'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'DAILY_PRIV_REFRESH');
END;
/