OracleNotes

Blog

  • Granting privileges on all or multiple objects to user/role

    Granting privileges on all or multiple objects to user/role

    Question:
    I need to grant SELECT, UPDATE privilege on all tables owned by schema XXVMX to users MSUBBU, SMARTIN. Is there a command in Oracle to grant a privilege on all objects in schema to user?

    Answer:
    No such privilege in Oracle. You will have to write a script to grant the privilege on individual objects to the users.

    If you have to repeat the same to more users, it may be better to create a role and grant the privileges to the role. Then assign the role to the users that need the privilege. Thus when new tables are created under XXVMX, the privilege need to be added to the role once only, do not have to do grant to all the individual users.

    Create Role:

    CREATE ROLE XXVMX_UPDATE;

    Grant Role to Users:

    GRANT XXVMX_UPDATE to MSUBBU, SMARTIN;

    SQL Script:

    set pages 0
    set lines 300 trims on feedback on echo off


    spool grants.sql


    SELECT ‘grant select, update on ‘ || owner ||’.’||table_name|| ‘ to XXVMX_UPDATE;’
    FROM dba_tables
    WHERE owner = ‘XXVMX’;


    spool off


    set pages 99 lines 80
    set feedback on echo on

    Execute the script file created to grant the privileges.

    @grants.sql

    You may replace the “dba_tables” in the query with “dba_objects” and change the WHERE clause appropriately to filter different sets of objects…

  • Script to Refresh Materialized Views

    The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast refresh-able materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month…

    REM Uncomment below line if EBS database
REM alter session set current_schema=apps;

set serveroutput on size 9999

Declare
cursor mv2ref is select owner ||'.'||  mview_name mview
       from dba_mviews
       where last_refresh_date between sysdate -30 and sysdate -1
       and fast_refreshable = 'DML';
dummy pls_integer;

begin

REM Uncomment below line if EBS database
REM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;

for rmv2ref in mv2ref loop
begin
    dbms_mview.refresh(rmv2ref.mview, 'F');
    dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));

exception
when others then
    dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));
    dbms_output.put_line('    Error: '|| sqlcode ||':'||sqlerrm);

end;

end loop;

end;
/

    Enjoy!