---This file should contain all the privileges which need to
---neccearily be granted to owb_user_role.

--used to save the role name and encrypted password
CREATE TABLE OWB_ROLE_INFO(       
       repository_name VARCHAR2(30) unique, 
       public_view_role_name VARCHAR2(30),
       owb_user_role_name VARCHAR2(30),
       owb_user_role_enabling_pwd RAW(2000)
--       owb_user_role_enabling_pwd VARCHAR2(200)
);


--sept 27,06: need to create a tab to record user's conn info so if a user is connected, another session should not drop it... 

create table USER_LOGIN_INFO(
user_name varchar2(30) not null,  
inst_id number  not null, 
sid number not null, 
SERIAL# NUMBER not null, 
logon_time VARCHAR2(30) not null, 
osUser VARCHAR2(512) not null,
workspaceId number not null
);

--should grant select to real owb user (not through owb_user_role) on the above table


--create owb_user_role: this should be OWB$CLIENT
--jul 31: use alter role since it is created at the owbmeta creation time
 ALTER  ROLE OWBM$CLIENT IDENTIFIED by %pwd; 
--CREATE ROLE OWB$CLIENT IDENTIFIED by %pwd;

--this is the public view role,name convention:OWB_DESIGNCENTER_VIEW
--fix bug 5560480
ALTER ROLE OWBM_DESIGNCENTER_VIEW IDENTIFIED by %pwd;
--CREATE ROLE OWB_DESIGNCENTER_VIEW IDENTIFIED by %pwd;

--jul 11,06: create OWB_USER role here temporarily , since UK will create it there
--fix bug 5560480
--create role OWB_USER; 
--grant execute on wb_workspace_management  to OWB_USER;
--grant select on workspace_assignment  to OWB_USER;

--create a type of COLLECTION to pass privileges  as an array to
--pl/sql pkg procedures...
--CREATE TYPE ObjectUOIDArray AS TABLE OF VARCHAR2(255); 

--create type PRIVSLISTS AS VARRAY(200) OF VARCHAR2(200);
--.
--run
--sec_stringlist is generic string list 
create type SEC_STRINGLIST AS VARRAY(200) OF VARCHAR2(200);

--create type USERIDLIST AS VARRAY(200) OF VARCHAR2(200);
--.
--run

-- originally we want to create sec_objectinfo as a type to capture
--into which will return when do security check cascade, but find that
--it's there is a bug (an SQL internal error exception )when retrieve
--array from ARRAY object by non-repos owner user. hence have to
--change to just declare an array of plain type(string). 

--CREATE TYPE SEC_OBJECTINFO AS OBJECT (
 --  object_id NUMBER(9),
  -- object_uoid VARCHAR2(255),
  -- object_name VARCHAR2(255),
  -- object_classname VARCHAR2(255)
--);

--CREATE TYPE SEC_OBJECTINFOLIST IS VARRAY(1000) OF  SEC_OBJECTINFO;
CREATE TYPE SEC_OBJECTINFOLIST IS VARRAY(1000) OF  VARCHAR2(400);


--CREATE TYPE SECCHECK_OBJECT AS OBJECT (
 --  object_id NUMBER(9),
   -- -1: check cascade down; 0: no cascade, 1: cascade up
 --  cascadeType NUMBER(9)
--);

--CREATE TYPE SECCHECK_OBJECTLIST IS VARRAY(500) OF  SECCHECK_OBJECT;

create type SEC_INTEGERLIST AS VARRAY(500) OF NUMBER(9);

-- definition of cascade type: -1: only cascade down; 0: no cascade; 1: cascade up; 2: cascade up+cascade down
--create type SEC_CASCADETYPELIST AS VARRAY(500) OF NUMBER(9);

create type SEC_UOIDLIST AS VARRAY(500) OF VARCHAR2(255);



--for public view performance, introduce a lookup table
create table FCO_READ_LOOKUP(
	FCOID number(9), 
	privilegeOwnerID number(9));

create index IDX_FCO_READ_LOOKUP_PRIVID on
FCO_READ_LOOKUP(privilegeOwnerID) PARALLEL NOLOGGING COMPRESS
tablespace %tndex;	
create index IDX_FCO_READ_LOOKUP_FCOID on
FCO_READ_LOOKUP(FCOID) PARALLEL NOLOGGING COMPRESS
tablespace %tndex;	

--to speed up FCO lookup from SCO,introduce following indx:
--comment out for fix bug: 4561758
--create index IDX_SCOFCOID on CMPSCOClasses(r_11) PARALLEL NOLOGGING COMPRESS tablespace %tndex;
--create index IDX_SCOCfgFCOID on CMPSCOCfgClasses(r_11) PARALLEL NOLOGGING COMPRESS tablespace %tndex;
--create index IDX_SCOMapFCOID on CMPSCOMapClasses(r_11) PARALLEL NOLOGGING COMPRESS tablespace %tndex;
--create index IDX_SCOPrpFCOID on CMPSCOPrpClasses(r_11) PARALLEL NOLOGGING COMPRESS tablespace %tndex;



--create table to contain sys id and name mapping for public view of
--sys priv purpose
create table SYS_PRIV_ID_TO_NAME_TAB(
	sysPrivId number(9) unique, 	
	sysPrivName varchar2(200) unique);	

--create some type for public view of sys priv
CREATE or replace TYPE priv_t AS OBJECT
( name VARCHAR2(100));
/

create or replace type privList as table of priv_t;
/

create table OBJ_PRIV_ID_TO_NAME_TAB(
	objPrivId number(9) unique, 	
	objPrivName varchar2(200) unique);	


--seed the obsoleted sys priv for paris
create table obsolete_sys_privs(obsolete_sys_name varchar2(255));

insert into obsolete_sys_privs(obsolete_sys_name) values('CREATE_USER');
insert into obsolete_sys_privs(obsolete_sys_name) values('CREATE_ROLE');
insert into obsolete_sys_privs(obsolete_sys_name) values('CREATE_ACLCONTAINER');
insert into obsolete_sys_privs(obsolete_sys_name) values('SNAPSHOT_RESTORE');
insert into obsolete_sys_privs(obsolete_sys_name) values('RUNTIME_EXECUTE');
insert into obsolete_sys_privs(obsolete_sys_name) values('SOURCE_IMPORT');
insert into obsolete_sys_privs(obsolete_sys_name) values('BRIDGE_EXPORT');
insert into obsolete_sys_privs(obsolete_sys_name) values('BRIDGE_IMPORT');
insert into obsolete_sys_privs(obsolete_sys_name) values('MDL_EXPORT');
insert into obsolete_sys_privs(obsolete_sys_name) values('MDL_IMPORT');
insert into obsolete_sys_privs(obsolete_sys_name) values('DEPLOY');
insert into obsolete_sys_privs(obsolete_sys_name) values('DEPLOYMENT_TARGET_USER');
insert into obsolete_sys_privs(obsolete_sys_name) values('CREATE_MIVDEFINITION');
insert into obsolete_sys_privs(obsolete_sys_name) values('CONTROL_CENTER_EXECUTE');
insert into obsolete_sys_privs(obsolete_sys_name) values('CONTROL_CENTER_DEPLOY');
insert into obsolete_sys_privs(obsolete_sys_name) values('CONTROL_CENTER_ADMIN');

commit;


--seed the obseleted obj priv for paris
create table obsolete_obj_privs(obsolete_obj_name varchar2(255));

insert into obsolete_obj_privs(obsolete_obj_name) values('CREATE');
insert into obsolete_obj_privs(obsolete_obj_name) values('DELETE');
insert into obsolete_obj_privs(obsolete_obj_name) values('REFERENCE');
insert into obsolete_obj_privs(obsolete_obj_name) values('VERSION');
insert into obsolete_obj_privs(obsolete_obj_name) values('GENERATION');
insert into obsolete_obj_privs(obsolete_obj_name) values('VALIDATE');
commit;






CREATE OR REPLACE PACKAGE SECURITY_PV_UTILITIES AS

FUNCTION CUR_USERID return NUMBER;
FUNCTION IS_CUR_USER_ADMIN return NUMBER;
FUNCTION  GetSysPrivNames(accessCharSet varchar2) RETURN privList pipelined;
FUNCTION  GetObjPrivNames(accessCharSet varchar2) RETURN privList pipelined;
PROCEDURE grant_access_publicview_priv(granting number, userName varchar2);

END SECURITY_PV_UTILITIES;
/

CREATE OR REPLACE PACKAGE BODY SECURITY_PV_UTILITIES IS
	m_isUserInfoInited number :=0; 
	m_userId number:=0; 
	m_isUserAdmin number :=0;

PROCEDURE init AS

	theUserName varchar2(30);
BEGIN
	select user into theUserName from dual;
	select elementid into m_userId from cmpwbuser_v where upper(name) = upper(theUserName);
	
	--now do the is admin info
	select count(*) into m_isUserAdmin 
	from cmpwbrole_v r, CMPRoleAssignment_v a	
	where a.wbuser = m_userId and 
	      r.elementid = a.wbrole and 
              r.isAdmin ='1';

	m_isUserInfoInited :=1; -- initialized

EXCEPTION 
	when others then
	  raise_application_error(-20001, 'SECURITY_PV_UTILITIES found the SQL error:'||SUBSTR(SQLERRM,1,200));

END init;
 
FUNCTION IS_CUR_USER_ADMIN return NUMBER 
IS
	

BEGIN 
if(m_isUserInfoInited = 0) then

	init;
	
end if;

return m_isUserAdmin;

EXCEPTION 
	when others then
	  raise_application_error(-20001, 'SECURITY_PV_UTILITIES found the SQL error:'||SUBSTR(SQLERRM,1,200));

END  IS_CUR_USER_ADMIN;



FUNCTION CUR_USERID return NUMBER 
IS


BEGIN 
if(m_isUserInfoInited = 0) then

	init;
	
end if;

return m_userId;

EXCEPTION 
	when others then
	  raise_application_error(-20001, 'SECURITY_PV_UTILITIES found the SQL error:'||SUBSTR(SQLERRM,1,200));

END  CUR_USERID;



FUNCTION  GetSysPrivNames(accessCharSet varchar2) RETURN privList pipelined AS 
 
 input_len number; 	
 sys_id number;
 sys_name varchar2(255);
 bitChar CHAR(1);
 max_sys_id number;
 isObsolete number;
BEGIN
	


	if(accessCharSet is null) then 
	--pipe row(sysPriv_t(NULL)); if do this, caller will get one
	--row with content NULL, this is not good 
	return; -- if just return, caller will not get anything, this is correct
	end if;
	
	input_len:=LENGTH(accessCharSet);
	select max(sysPrivId) into max_sys_id from SYS_PRIV_ID_TO_NAME_TAB ;
	
	for i in 1..input_len loop
	   exit when i > (max_sys_id+1); -- id is less 1 than position
	   bitChar:=SUBSTR(accessCharSet,i,1); --substr(pos,length)

	   if(bitChar='0') then 
            null;
 	   else
        	   begin 
		
		   select sysPrivId, sysPrivName into sys_id, sys_name 
		   from  SYS_PRIV_ID_TO_NAME_TAB where sysPrivId = i-1;		  

		   select count(*) into isObsolete from
         	   obsolete_sys_privs where obsolete_sys_name = sys_name;	
		   if(isObsolete > 0) then 
		    null; --nothing	
		   
                   else
	             pipe row(priv_t(sys_name));
		   end if;
	        exception 
                when NO_DATA_FOUND then 
	         null;
	        end;
   	 end if;
	end loop;
       

EXCEPTION
  when others then 
	raise_application_error(-20001,'SecurityPublicViewUtil found the SQL error:'||SUBSTR(SQLERRM,1,200));

end GetSysPrivNames;


FUNCTION  GetObjPrivNames(accessCharSet varchar2) RETURN privList pipelined AS 
 
 input_len number; 	
 obj_id number;
 obj_name varchar2(255);
 bitChar CHAR(1);
 max_obj_id number;
 isObsolete number;
BEGIN
	


	if(accessCharSet is null) then 
	--pipe row(sysPriv_t(NULL)); if do this, caller will get one
	--row with content NULL, this is not good 
	return; -- if just return, caller will not get anything, this is correct
	end if;
	
	input_len:=LENGTH(accessCharSet);
	select max(objPrivId) into max_obj_id from OBJ_PRIV_ID_TO_NAME_TAB ;
	
	for i in 1..input_len loop
	   exit when i > (max_obj_id+1); -- id is less 1 than position
	   bitChar:=SUBSTR(accessCharSet,i,1); --substr(pos,length)

	   if(bitChar='0') then 
            null;
 	   else
        	   begin 
		
		   select objPrivId, objPrivName into obj_id, obj_name 
		   from  OBJ_PRIV_ID_TO_NAME_TAB where objPrivId = i-1;		  

		   select count(*) into isObsolete from
         	   obsolete_obj_privs where obsolete_obj_name = obj_name;	
		   if(isObsolete > 0) then 
		    null; --nothing	
		   
                   else
	             pipe row(priv_t(obj_name));
		   end if;
	        exception 
                when NO_DATA_FOUND then 
	         null;
	        end;
   	 end if;
	end loop;
       

EXCEPTION
  when others then 
	raise_application_error(-20000,'SecurityPublicViewUtil found the SQL error:'||SUBSTR(SQLERRM,1,200));

end GetObjPrivNames;

PROCEDURE grant_access_publicview_priv(granting number, userName varchar2)

as 
stmt varchar2(2000);

begin
     	if(granting = 0) then

	  stmt:= 'revoke EXECUTE on start_access_publicview from '||userName;
	else
 	  stmt:='grant EXECUTE on start_access_publicview to '||userName;
	end if;
--    execute immediate stmt;     
     execute immediate  stmt;

exception 
	when others then
	 if sqlcode <> -1927
         then  
	raise_application_error(-20002,'SecurityPublicViewUtil found the SQL error:'||SUBSTR(SQLERRM,1,200));
         end if;
end grant_access_publicview_priv;



END SECURITY_PV_UTILITIES;
/



---since if join the base table and fco_read_lookup table, there could
--- have dup rows due to a user or a role of user can read that
---obj. but for CLOB we can not use distinct key word, hence here we
---create a view which uniquely identify a user can read a obj or not
---through himself or a role
--UNIION the privowner to let admin can see all the users info
create or replace view FCO_READ_LOOKUP_V
(fcoId
) 
as ( select distinct 
l.fcoId
from FCO_READ_LOOKUP l
where l.privilegeOwnerId =  SECURITY_PV_UTILITIES.CUR_USERID or
l.PRIVILEGEOWNERID in(select wbrole from CMPRoleAssignment_V where wbuser= SECURITY_PV_UTILITIES.CUR_USERID) 
UNION ALL
select distinct 
elementid 
from cmpprivilegeowner_v 
where SECURITY_PV_UTILITIES.IS_CUR_USER_ADMIN =1
);