-- 
-- Create User ssdba have its own tablespace ideally
-- Recommended to have 5GB free space
-- connect ssdba/ssdba..
-- Author :Surender Sara
-- Copyright Sera Consulting Inc. 2003, Orabyte 2006
-- RUN AS SYS user
-- MAKE SURE TO CHANGE THE PASSWORD FOR SSDBA, 
-- THIS WILL BE A DBA USER, 
--

spool ssdba_cr_user.lst
undefine i_tbs
undefine i_tmp

accept i_tbs    char prompt 'SSDBA tables : Default tablespace ? -Please Enter= '
accept i_tmp    char prompt 'SSDBA tables : Temp tablespace ? -Please Enter= '

drop user ssdba cascade;

create user ssdba identified by ssdba
default tablespace &i_tbs
temporary tablespace &i_tmp
quota unlimited on users
;

Prompt ' Issue Grants '

grant connect, resource to ssdba;
revoke unlimited tablespace from ssdba ;
Prompt ' Grant system privs which APPS Also Has' 
grant create materialized view to ssdba;

grant dba to ssdba;
grant ALTER ANY CLUSTER to ssdba;
grant ALTER ANY INDEX to ssdba;
grant ALTER ANY OUTLINE to ssdba;
grant ALTER ANY ROLE to ssdba;
grant ALTER ANY SEQUENCE to ssdba;
grant ALTER ANY SNAPSHOT to ssdba;
grant ALTER ANY TABLE to ssdba;
grant ALTER ANY TRIGGER to ssdba;
grant ADMINISTER DATABASE TRIGGER  to ssdba;
grant ALTER SESSION to ssdba;
grant ALTER SYSTEM to ssdba;
grant ANALYZE ANY to ssdba;
grant COMMENT ANY TABLE to ssdba;
grant CREATE ANY CLUSTER to ssdba;
grant CREATE ANY CONTEXT to ssdba;
grant CREATE ANY INDEX to ssdba;
grant CREATE ANY OUTLINE to ssdba;
grant CREATE ANY SEQUENCE to ssdba;
grant CREATE ANY SNAPSHOT to ssdba;
grant CREATE ANY SYNONYM to ssdba;
grant CREATE ANY TABLE to ssdba;
grant CREATE ANY TRIGGER to ssdba;
grant CREATE ANY VIEW to ssdba;
grant CREATE DATABASE LINK to ssdba;
grant CREATE PROCEDURE to ssdba;
grant CREATE PUBLIC SYNONYM to ssdba;
grant CREATE ROLE to ssdba;
grant CREATE SESSION to ssdba;
grant CREATE SYNONYM to ssdba;
grant CREATE TRIGGER to ssdba;
grant CREATE TYPE to ssdba;
grant CREATE VIEW to ssdba;
grant DELETE ANY TABLE to ssdba;
grant DROP ANY CLUSTER to ssdba;
grant DROP ANY CONTEXT to ssdba;
grant DROP ANY INDEX to ssdba;
grant DROP ANY OUTLINE to ssdba;
grant DROP ANY ROLE to ssdba;
grant DROP ANY SEQUENCE to ssdba;
grant DROP ANY SNAPSHOT to ssdba;
grant DROP ANY SYNONYM to ssdba;
grant DROP ANY TABLE to ssdba;
grant DROP ANY TRIGGER to ssdba;
grant DROP ANY VIEW to ssdba;
grant DROP PUBLIC SYNONYM to ssdba;
grant GLOBAL QUERY REWRITE to ssdba;
grant GRANT ANY ROLE to ssdba;
grant INSERT ANY TABLE to ssdba;
grant LOCK ANY TABLE to ssdba;
grant SELECT ANY SEQUENCE to ssdba;
grant SELECT ANY TABLE to ssdba;
grant UNLIMITED TABLESPACE to ssdba;
grant UPDATE ANY TABLE to ssdba;

grant execute on dbms_system to ssdba ;
grant select on dba_extents  to ssdba;
grant select on dba_segments to ssdba;
grant select on dba_data_files to ssdba;

-- RUN as SYS connection 


create view sys.x_$kcbfwait  as select * from x$kcbfwait;
grant select on x_$kcbfwait to ssdba ;

create view sys.x_$bh  as select * from x$bh;
grant select on x_$bh to ssdba ;

create view sys.x_$kccfe  as select * from sys.x$kccfe;
grant select on x_$kccfe to ssdba ;

create view sys.x_$kglcursor as select * from sys.x$kglcursor;
grant select on sys.x_$kglcursor to ssdba;

create view sys.x_$kglob as select * from x$kglob;
grant select on sys.x_$kglob to ssdba;

create view sys.x_$kglrd as select * from x$kglrd;
grant select on sys.x_$kglrd to ssdba;


create view sys.x_$ksmsp as select * from x$ksmsp;
grant select on sys.x_$ksmsp  to ssdba;

create view SYS.X_$KCBWDS as select * from x$kcbwds;
grant select on sys.x_$kcbwds to ssdba;

create view SYS.X_$KCBWBPD as select * from SYS.X$KCBWBPD;
grant select on SYS.X_$KCBWBPD to ssdba;

create view SYS.X_$KGLPN as select * from SYS.X$KGLPN;
grant select on SYS.X_$KGLPN to ssdba;

create view  sys.x_$kslld as select * from sys.x$kslld ;
grant select on  sys.x_$kslld to ssdba;

create view  sys.x_$ksllt as select * from sys.x$ksllt ;
grant select on sys.x_$ksllt to ssdba;

create view  sys.x_$kslwsc  as select * from sys.x$kslwsc  ;
grant select on   sys.x_$kslwsc to ssdba;

create view  sys.x_$ksllw  as select * from sys.x$ksllw;
grant select on  sys.x_$ksllw to ssdba;

create view  SYS.X_$KCCLE as select * from  SYS.X$KCCLE ;
grant select on   SYS.X_$KCCLE to ssdba;
                         
create view sys.x_$ksusgsta as select * from sys.x$ksusgsta ;
grant select on sys.x_$ksusgsta to ssdba;
        
create view	  sys.x_$ksusd as select * from sys.x$ksusd;
grant select on sys.x_$ksusd to ssdba;

create view	  sys.x_$ksuse as select * from sys.x$ksuse;
grant select on sys.x_$ksuse to ssdba;

create view	  sys.x_$ksbdp  as select * from sys.x$ksbdp;
grant select on sys.x_$ksbdp to ssdba;

create view	  sys.x_$ksusesta  as select * from sys.x$ksusesta;
grant select on sys.x_$ksusesta to ssdba;

create view	 sys.x_$kglst as select * from sys.x$kglst;
grant select on sys.x_$kglst to ssdba;

create view	 sys.x_$kvii as select * from sys.x$kvii;
grant select on sys.x_$kvii to ssdba;

create view	 sys.x_$kccle as select * from x$kccle;
grant select on sys.x_$kccle to ssdba;
 
create view sys.x_$kslei as select * from sys.x$kslei ; 
grant select on sys.x_$kslei to ssdba;

create view sys.x_$ksled as select * from sys.x$ksled ;
grant select on sys.x_$ksled to ssdba;


create view sys.x_$ksqst as select * from sys.x$ksqst ;
grant select on sys.x_$ksqst to ssdba;



create view sys.x_$ksusgsta  as select * from x$ksusgsta;
grant select on sys.x_$ksusgsta  to ssdba;

create view sys.X_$KGLDP as select * from sys.X$KGLDP ;
grant select on sys.X_$KGLDP to ssdba;

create view  sys.X_$KKSBV as select * from sys.X$KKSBV ;
grant select on sys.X_$KKSBV to ssdba;

create view sys.X_$KGLOB as select * from sys.X$KGLOB ;
grant select on sys.X_$KGLOB to ssdba;

create view sys.X_$KGLOB as select * from sys.X$KKSBV ;
grant select on sys.X_$KKSBV to ssdba;

create view sys.X_$KGLDP as select * from sys.X$KGLOB ;
grant select on sys.X_$KGLDP to ssdba;

create view sys.X_$KGLOB as select * from sys.X$KGLOB ;
grant select on sys.X_$KGLOB to ssdba;

create view sys.X_$KGLOB as select * from sys.X$KGLOB ;
grant select on sys.X_$KGLOB to ssdba;

create view SYS.X_$KGLXS as select * from SYS.X$KGLXS ;
grant select on SYS.X_$KGLXS to ssdba;

create view SYS.x_$ktsso as select * from SYS.x$ktsso;
grant select on SYS.x_$ktsso to ssdba;

create view SYS.x_$qesmmsga as select * from SYS.x$qesmmsga;
grant select on SYS.x_$qesmmsga to ssdba;

create view sys.x_$ksppi as select * from sys.x$ksppi;
grant select on sys.x_$ksppi to ssdba;


create view sys.x_$ksppsv as select * from sys.x$ksppsv;
grant select on sys.x_$ksppsv to ssdba;
 

grant select on v_$sql to ssdba        			;
grant select on v_$mystat to ssdba     			;
grant select on v_$sqlarea to ssdba    			;
grant select on v_$sqltext_with_newlines to ssdba	;
grant select on v_$latchname to ssdba  			;
grant select on dba_objects to ssdba   			;
grant select on v_$lock to ssdba       			;
grant select on v_$lock to ssdba       			;
grant select on v_$datafile to ssdba   			;
grant select on v_$sort_usage to ssdba 			;
grant select on v_$tempfile to ssdba   			;


grant select on uet$ to ssdba;
grant select on file$ to ssdba;
grant select on v_$session to ssdba;
grant select on v_$session_event to ssdba;
grant select on v_$session_wait to ssdba;
grant select on v_$sesstat to ssdba;
grant select on v_$statname to ssdba;
grant select on v_$sql to ssdba;

grant select on v_$parameter to ssdba;
grant select on v_$tempfile to ssdba;



spool off