REM
REM Copyright (c) 2002, 2007, Oracle. All rights reserved.  
REM
REM Name
REM   awmxsrol.sql
REM
REM Description
REM   OLAP_XS_ADMIN Role grants
REM
REM Notes
REM
REM MODIFIED (MM/DD/YY)
REM   dbardwel 04/25/08 - Add additional privileges for olap_xs_admin role for 11.2
REM   -- 11.2 -- above this line --
REM   dbardwel 08/07/07 - Add select on dba_xds_instance_sets to olap_xs_admin
REM   --- 11.1.0.7 -- Patch changes above this line ---
REM   dbardwel 06/15/07 - Add select on dba_roles to olap_xs_admin
REM   dbardwel 05/17.07 - Add olap_xs_admin role to DBA role
REM   dbardwel 01/17/07 - Rename awm_xs_admin -> olap_xs_admin
REM   dbardwel 12/01/06 - For AWM Release 11G R1
REM


Rem add olap_xs_admin role if needed
declare
  cursor apsrole is select role from dba_roles where role = 'OLAP_XS_ADMIN';
  apsrolename varchar2(30);
begin
  if not apsrole%isopen then
    open apsrole;
    fetch apsrole into apsrolename;
    if apsrole%notfound then
      execute immediate 'create role olap_xs_admin not identified';
    end if;
    close apsrole;
  end if;
end;
/

Rem These grants are needed since dbms_xdb package is authid current user
Rem Without these grants the dbms_xdb calls will not work

grant execute on SYS.DBMS_XS_SECCLASS_INT to olap_xs_admin;
grant execute on SYS.DBMS_XS_PRIVID_LIST to olap_xs_admin;
grant execute on XDB.DBMS_XDB to olap_xs_admin;

grant select on XDB.XS$SECURITYCLASS to olap_xs_admin;
grant insert on XDB.XS$SECURITYCLASS to olap_xs_admin;
grant update on XDB.XS$SECURITYCLASS to olap_xs_admin;
grant delete on XDB.XS$SECURITYCLASS to olap_xs_admin;
	
grant select on XDB.XDB$ACL to olap_xs_admin;
grant insert on XDB.XDB$ACL to olap_xs_admin;
grant update on XDB.XDB$ACL to olap_xs_admin;
grant delete on XDB.XDB$ACL to olap_xs_admin;

grant select on XDB.XS$PRINCIPALS to olap_xs_admin;
grant insert on XDB.XS$PRINCIPALS to olap_xs_admin;
grant update on XDB.XS$PRINCIPALS to olap_xs_admin;
grant delete on XDB.XS$PRINCIPALS to olap_xs_admin;

grant select on XDB.XS$DATA_SECURITY to olap_xs_admin;
grant insert on XDB.XS$DATA_SECURITY to olap_xs_admin;
grant update on XDB.XS$DATA_SECURITY to olap_xs_admin;
grant delete on XDB.XS$DATA_SECURITY to olap_xs_admin;

grant execute on dbms_xds to olap_xs_admin;
grant select on dba_roles to olap_xs_admin;
grant select on dba_xds_instance_sets to olap_xs_admin;

grant execute on SYS.DBMS_XS_ROLESET_EVENTS_INT to olap_xs_admin;
grant execute on SYS.DBMS_XS_SECCLASS_EVENTS to olap_xs_admin;
grant execute on SYS.DBMS_XS_DATA_SECURITY_EVENTS to olap_xs_admin;


grant olap_xs_admin to DBA;

Rem Need to get a return code for creating an xml db folder.
Rem Unfortunately dbms_xdb.createfolder() returns a boolean true/false which I
Rem can not directly handle via jdbc. This is a wrapper function to return a varchar2

create or replace function awm_createxdsfolder(resPath varchar2) return varchar2 is
  v_ret boolean;
  v_retcode varchar2(10);
begin
  v_ret := dbms_xdb.createfolder(resPath);

  if v_ret = true then
    v_retcode := 'GOOD';
  else
    v_retcode := 'BAD';
  end if;

  return v_retcode;

end awm_createxdsfolder;
/

grant execute on sys.awm_createxdsfolder to olap_xs_admin;