-- @(#) $Id: //bas/722_STACK/src/ccm/rsbr/sapconn_role.sql#1 $ SAP
-- Create/update sapconn role
-- This script can only be run on Oracle 10g or higher
-- Calling syntax (sapconn_role.sql in current directory):
-- sqlplus /nolog @sapconn_role
-- Log file sapconn_role.log will be created in current directory

set echo off;
set termout off;
spool sapconn_role.log

whenever sqlerror exit sql.sqlcode

connect / as sysdba;

whenever sqlerror continue

declare

Curs		INTEGER;
Statement	VARCHAR2(128);
RetWert		INTEGER;
V_User		VARCHAR2(30);
cursor	Curs_1	is select username from sys.dba_users where username like 'SAP%';

begin

Curs := dbms_sql.open_cursor;

begin
 Statement := 'create role sapconn';
 dbms_sql.parse(Curs, Statement, DBMS_SQL.NATIVE);
 RetWert := dbms_sql.execute(Curs);
exception
 when others then
 NULL;
end;

begin
 open curs_1;
 loop
  fetch curs_1 into V_User;
  exit when curs_1%notfound;
  begin
   Statement := 'grant sapconn to "' || V_User || '"';
   dbms_sql.parse(Curs, Statement, DBMS_SQL.NATIVE);
   RetWert := dbms_sql.execute(Curs);
  exception
   when others then
   NULL;
  end;
  begin
   Statement := 'grant create procedure to "' || V_User || '"';
   dbms_sql.parse(Curs, Statement, DBMS_SQL.NATIVE);
   RetWert := dbms_sql.execute(Curs);
  exception
   when others then
   NULL;
  end;
 end loop;
 close curs_1;
end;

dbms_sql.close_cursor(Curs);

end; -- procedure
/

grant create session to sapconn;
grant alter session to sapconn;
grant create table to sapconn;
grant create view to sapconn;
grant create materialized view to sapconn;
grant create cluster to sapconn;
grant create synonym to sapconn;
grant create sequence to sapconn;
grant create trigger to sapconn;
grant create type to sapconn;
grant create operator to sapconn;
grant create indextype to sapconn;
grant create procedure to sapconn;
grant select_catalog_role to sapconn;
grant analyze any to sapconn;
grant advisor to sapconn;
grant execute on dbms_lob to sapconn;
grant execute on dbms_monitor to sapconn;
grant execute on dbms_advisor to sapconn;
grant execute on dbms_workload_repository to sapconn;

exit;