Rem
Rem $Header: whdev/2.0/owb/shiphome/owb/reposasst/upg/bug_9141939.sql /main/1 2010/04/19 21:23:20 glei Exp $
Rem
Rem bug_9141939.sql
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      bug_9141939.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glei        04/19/10 - Created
Rem

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 '||sys.dbms_assert.enquote_name(userName,false);
        else
          stmt:='grant EXECUTE on start_access_publicview to '||sys.dbms_assert.enquote_name(userName,false);
        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;
/