REM --------------------------------------------------------------
REM File Name   : chkplcy.sql
REM Author      : Kannan Parthasarathy
REM Date        : 25-March-2004
REM Description : Check for the Fine-grained access feature
REM               support in the target database and conditionally
REM               install Repos policies
REM --------------------------------------------------------------

DECLARE
    CURSOR CK_POLICY_CHECK IS
          SELECT 'TRUE' 
          FROM V$OPTION 
          WHERE LOWER(PARAMETER) = 'fine-grained access control' and VALUE = 'TRUE'
                AND EXISTS(SELECT 1 FROM USER_TAB_PRIVS_RECD WHERE TABLE_NAME = 'DBMS_RLS' AND PRIVILEGE = 'EXECUTE');

    CURSOR CK_REPOS_INSTALLED IS
          SELECT 'TRUE' FROM SYS.TAB WHERE TNAME = 'RM$REPOSITORIES';

    POLICY_ENABLED  VARCHAR2(10);
    REPOS_INSTALLED VARCHAR2(10);
BEGIN
    OPEN CK_POLICY_CHECK;
    FETCH CK_POLICY_CHECK into POLICY_ENABLED;
    IF POLICY_ENABLED = 'TRUE' THEN
        -- Mark the policy functions for reload
        UPDATE CK_INSTALLED_OBJECTS 
        SET CI_STAGE = 'JRFUN', CI_CREATED = 'R'
        WHERE CI_STAGE = 'JRFUN_DELETED';

        -- Update the views to use policy definition
        UPDATE CK_INSTALLED_OBJECTS
        SET CI_FILENAME = 'jrview.sql', CI_CREATED = 'R'
        WHERE CI_FILENAME = 'jrvwnp.sql';
        
        UPDATE CK_INSTALLED_OBJECTS
        SET CI_FILENAME = 'jrvrvsys.sql', CI_CREATED = 'R'
        WHERE CI_FILENAME = 'jrvsysnp.sql';
        
        -- Mark the poilcies for reload
        UPDATE CK_INSTALLED_OBJECTS 
        SET CI_STAGE = 'JRPOLICY', CI_CREATED = 'R'
        WHERE CI_STAGE = 'JRPOLICY_DELETED';
        
        -- Mark triggers to be reloaded
        UPDATE CK_INSTALLED_OBJECTS
        SET    CI_CREATED = 'R'
        WHERE  CI_TYPE = 'TRIGGER' 
        AND CI_STAGE = 'JRTRIG' 
        AND CI_TABLE_IRID IN (SELECT CI_TABLE_IRID 
                        FROM CK_INSTALLED_OBJECTS 
                        WHERE CI_STAGE='JRPOLICY');
    ELSE
        -- Mark the policy functions as deleted
        UPDATE CK_INSTALLED_OBJECTS 
        SET CI_STAGE = 'JRFUN_DELETED'
        WHERE CI_STAGE = 'JRFUN' and CI_NAME like '%POLICY';

        -- Update the views to use no policy definition
        UPDATE CK_INSTALLED_OBJECTS
        SET CI_FILENAME = 'jrvwnp.sql', CI_CREATED = 'R'
        WHERE CI_STAGE='JRVIEW' 
                AND CI_NAME IN (SELECT CI_NAME 
                                FROM CK_INSTALLED_OBJECTS 
                                WHERE CI_STAGE = 'JRPOLICY');
        
        UPDATE CK_INSTALLED_OBJECTS
        SET CI_FILENAME = 'jrvsysnp.sql', CI_CREATED = 'R'
        WHERE CI_STAGE = 'JRVIEW'
              AND CI_NAME = 'SDD_OBJECT_VERSIONS';
        
        -- Mark the poilcies as deleted
        UPDATE CK_INSTALLED_OBJECTS 
        SET CI_STAGE = 'JRPOLICY_DELETED'
        WHERE CI_STAGE = 'JRPOLICY';

        -- Mark triggers to be reloaded
        UPDATE CK_INSTALLED_OBJECTS
        SET    CI_CREATED = 'R'
        WHERE  CI_TYPE = 'TRIGGER' 
        AND CI_STAGE = 'JRTRIG' 
        AND CI_TABLE_IRID IN (SELECT CI_TABLE_IRID 
                        FROM CK_INSTALLED_OBJECTS 
                        WHERE CI_STAGE='JRPOLICY_DELETED');
    END IF;

    OPEN CK_REPOS_INSTALLED;
    FETCH CK_REPOS_INSTALLED INTO REPOS_INSTALLED;
    -- If this is new install, reset all 'R' to 'N' in CI_CREATED
    IF REPOS_INSTALLED IS NULL THEN
        UPDATE CK_INSTALLED_OBJECTS
        SET CI_CREATED = 'N'
        WHERE CI_CREATED = 'R';
    END IF;
END;
/