set define '^'
set verify off
prompt dev_grants

Rem  Copyright (c) Oracle Corporation 2007. All Rights Reserved.
Rem
Rem    NAME
Rem      dev_grants.sql
Rem
Rem    DESCRIPTION
Rem      System and object grants for Application Express development installation
Rem
Rem    NOTES
Rem
Rem
Rem
Rem    SCRIPT ARGUMENTS
Rem      None
Rem
Rem    RUNTIME DEPLOYMENT: No
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      jkallman  07/09/2007 - Created
Rem      jstraub   07/10/2007 - Moved grants to dba_users, sys.users$, v$database, dba_source, dba_cons_columns,
Rem                             dba_constraints, dba_views, dba_errors, dba_col_comments to core_grants.sql
Rem      jkallman  01/30/2008 - Remove grant of v_$sesstat and v_$sql_plan - moved to core_grants.sql
Rem      sspadafo  07/20/2008 - Remove grant of execute any procedure (Bug 7225208, 7229026)
Rem      jkallman  08/07/2008 - Move grant on sys.dba_free_space from dev_grants.sql to core_grant.sql (Bug 7308558)
Rem      jkallman  08/27/2008 - Restore grant of execute any procedure (Bug 7340942)
Rem      sspadafo  11/03/2008 - Added grant select on sys.dba_arguments, sys.dba_procedures (Bug 7270054)
Rem      sspadafo  11/03/2008 - Remove grant of execute any procedure (Bug 7270054)
Rem      sspadafo  11/04/2008 - Changed grant of select on sys.dba_arguments to a block that do this conditionally based on db version and also creates a private synonym for sys.dba_arguments (Bug 7270054)
Rem      sspadafo  11/04/2008 - Added grant select on sys.dba_ind_expressions (Bug 7270054)
Rem      sspadafo  11/15/2008 - Fix quoting problem in "where owner = ^APPUN"
Rem      sspadafo  11/16/2008 - Change condition for sys.dba_arguments check to exclude XE because the view does not exist in XE (Bug 7270054)
Rem      jkallman  11/17/2008 - Move grant on sys.dba_triggers, sys.dba_dependencies from dev_grants.sql to core_grants.sql
Rem      sspadafo  12/02/2008 - Change logic for dealing with sys.dba_arguments view and synonym (Bug 7270054)

prompt ...grant APEX owner development privileges

grant alter system                   to ^APPUN;
grant select on sys.dba_tab_comments to ^APPUN;
grant select on sys.dba_synonyms to ^APPUN;
grant select on sys.dba_db_links to ^APPUN;
grant select on sys.dba_profiles to ^APPUN;
grant select on sys.dba_trigger_cols to ^APPUN;
grant select on sys.dba_indexes to ^APPUN;
grant select on sys.dba_col_privs to ^APPUN;
grant select on sys.dba_snapshots to ^APPUN;
grant select on sys.dba_types to ^APPUN;
grant select on sys.dba_rsrc_plan_directives to ^APPUN;
grant select on sys.seg$ to ^APPUN;
grant select on sys.argument$ to ^APPUN;
grant select on sys.obj$ to ^APPUN;
grant select on sys.v_$statname to ^APPUN;
grant select on sys.v_$sysstat to ^APPUN;
grant select on sys.v_$sql to ^APPUN;
grant select on sys.v_$mystat to ^APPUN;
grant select on sys.v_$session to ^APPUN;
grant select on sys.v_$locked_object to ^APPUN;
grant select on sys.v_$session_wait to ^APPUN;
grant select on sys.v_$sess_io to ^APPUN;
grant select on sys.v_$open_cursor to ^APPUN;
grant select on sys.v_$process to ^APPUN;
grant select on sys.dba_procedures to ^APPUN;
grant select on sys.dba_ind_expressions to ^APPUN;
--
-- Grant select on sys.dba_arguments to apex schema and create a private synonym for it if sys.dba_arguments exists (database version 10.2 or greater, excluding XE)
--
declare 
    l_found boolean;
    l_sys_view_exists boolean;
begin
	  l_sys_view_exists := false;
	  for c1 in (select 1 from dba_objects where object_name = 'DBA_ARGUMENTS' and object_type = 'VIEW' and owner = 'SYS') loop
	  		l_sys_view_exists := true;
	  end loop;
	  
	  if l_sys_view_exists then
        execute immediate 'grant select on sys.dba_arguments to ^APPUN';
        l_found := false;
        for c1 in (select 1 from dba_synonyms where owner = '^APPUN' and synonym_name = 'DBA_ARGUMENTS' and table_owner = 'SYS' and table_name = 'DBA_ARGUMENTS') loop
            l_found := true;
        end loop;
        if not l_found then
            execute immediate 'create synonym ^APPUN..dba_arguments for sys.dba_arguments';
        end if;
    else
        begin
            -- Drop private synonym to dba_arguments.  Silently fail if it does not exist.
            execute immediate 'drop synonym ^APPUN..dba_arguments ';
        exception when others then
            null;
        end;
    end if;
end;
/

--
-- Compile a local dba_arguments view if sys.dba_arguments does not exist (database version 10.1 or earlier or XE)
--
column :thescript new_val script NOPRINT
variable thescript varchar2(35)
declare
    l_sys_view_exists boolean;
begin
	  l_sys_view_exists := false;
	  for c1 in (select 1 from dba_objects where object_name = 'DBA_ARGUMENTS' and object_type = 'VIEW' and owner = 'SYS') loop
	  		l_sys_view_exists := true;
	  end loop;
	  
	  if l_sys_view_exists then
    	  :thescript := 'null1.sql';       
    else
        :thescript := 'create_dba_arguments_view.sql';
    end if;
end;
/
select :thescript from dual;
alter session set current_schema = ^APPUN;
@^PREFIX.core/^script
alter session set current_schema = SYS;

begin
    -- Grant select on sys.dba_recyclebin.  Silently fail if it does not exist
    execute immediate 'grant select on sys.dba_recyclebin to ^APPUN';
exception
    when others then
        null;
end;
/

begin
    -- Grant select on sys.dba_feature_usage_statistics.  Silently fail if it does not exist
    execute immediate 'grant select on sys.dba_feature_usage_statistics to ^APPUN';
exception
    when others then
        null;
end;
/

grant select on dba_rollback_segs to ^APPUN;


prompt ...done grant APEX owner development privileges