Rem
Rem $Header: rdbms/admin/dve112.sql /st_rdbms_11.2.0/8 2011/07/27 23:55:18 cchui Exp $
Rem
Rem dve112.sql
Rem
Rem Copyright (c) 2010, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dve112.sql - Downgrade DV from 11.2 to 11.1
Rem
Rem    DESCRIPTION
Rem      - This script will be called by cmpdwpth.sql for patch downgrades
Rem      - Also invoked by dve111.sql for version downgrades
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    cchui       07/22/11 - Remove unnecessary alter table
Rem    youyang     06/16/11 - backport 12395489
Rem    jibyun      05/03/11 - Backport jibyun_bug-12356827 from main
Rem    sanbhara    03/01/11 - Backport sanbhara_bug-10225918 from main
Rem    jibyun      03/02/11 - Backport jibyun_bug-11662436 from main
Rem    dvekaria    03/02/11 - Backport dvekaria_bug-9068994_1 from main
Rem    jheng       01/24/11 - Backport jheng_bug-7137958 from main
Rem    jheng       01/20/11 - Backport jheng_bug-8501924 from main
Rem    vigaur      06/02/10 - Create dve112.sql script
Rem    vigaur      06/02/10 - Created
Rem

EXECUTE DBMS_REGISTRY.DOWNGRADING('DV');

-- Bug 6503742
update DVSYS.FACTOR$ SET GET_EXPR = 'UTL_INADDR.GET_HOST_ADDRESS(DVSYS.DBMS_MACADM.GET_INSTANCE_INFO(''HOST_NAME''))' where name='Database_IP';

drop view dvsys.dba_dv_datapump_auth;

-- "Allow Oracle Data Pump Operation" rule set
BEGIN
INSERT INTO DVSYS.RULE_SET$ (ID#,ENABLED,EVAL_OPTIONS,AUDIT_OPTIONS,FAIL_OPTIONS,HANDLER_OPTIONS,VERSION,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)
VALUES(8,'Y',2,1,1,0,1,USER,SYSDATE,USER,SYSDATE);

   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation
     ELSE RAISE;
     END IF;
   
END;
/

--- "Allow Scheduler Job" rule set
BEGIN
INSERT INTO DVSYS.RULE_SET$ (ID#,ENABLED,EVAL_OPTIONS,AUDIT_OPTIONS,FAIL_OPTIONS,HANDLER_OPTIONS,VERSION,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE) 
VALUES (10,'Y',2,1,1,0,1,USER,SYSDATE,USER,SYSDATE);

   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation
     ELSE RAISE;
     END IF;

END;
/

BEGIN 
INSERT INTO DVSYS.RULE_SET_RULE$ (ID#,RULE_SET_ID#,RULE_ID#,RULE_ORDER,ENABLED,VERSION,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)
VALUES(10,8,2,1,'Y',1,USER,SYSDATE,USER,SYSDATE);

   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation
     ELSE RAISE;
     END IF;

END;
/

BEGIN
INSERT INTO DVSYS.RULE_SET_RULE$ (ID#,RULE_SET_ID#,RULE_ID#,RULE_ORDER,ENABLED,VERSION,CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE)
VALUES(18,10,2,1,'Y',1,USER,SYSDATE,USER,SYSDATE);

   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation
     ELSE RAISE;
     END IF;

END;
/

BEGIN
INSERt INTO DVSYS.rule_set_t$(id#, language, name, description) values
(8, 'us', 'Allow Oracle Data Pump Operation', 'Rule set that controls the objects that can be exported or imported by the Oracle Data Pump user.');
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation
     ELSE RAISE;
     END IF;

END;
/

BEGIN
INSERt INTO DVSYS.rule_set_t$(id#, language, name, description) values
(10, 'us', 'Allow Scheduler Job', 'Rule set that stores DV scheduler job authorized users.');
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation
     ELSE RAISE;
     END IF;

END;
/

-- insert datapump and job auth from dvsys.dv_auth$ to their rule sets
DECLARE
  cursor cur is select grant_type, grantee, object_owner, object_name 
                from dvsys.dv_auth$;
  l_rule_name dvsys.dv$rule.name%TYPE;
  l_seq  NUMBER;
BEGIN
  FOR ee IN cur LOOP
    IF ee.grant_type = 'JOB' THEN
      IF ee.grantee IS NOT NULL THEN

        SELECT dvsys.rule$_seq.nextval INTO l_seq FROM DUAL;
        l_rule_name := 'DV$' || TO_CHAR(l_seq);

        IF (ee.object_owner IS NOT NULL) AND (ee.object_owner != '%') THEN
          INSERT INTO DVSYS.rule$ (ID#,RULE_EXPR,VERSION,
                                   CREATED_BY,CREATE_DATE,
                                   UPDATED_BY,UPDATE_DATE)
          VALUES
          (l_seq, '(dvsys.dv_job_invoker = ''' || ee.grantee || 
           ''') AND (dvsys.dv_job_owner = ''' || ee.object_owner || ''')', 1,
           USER,SYSDATE,USER,SYSDATE);
        ELSE
          INSERT INTO DVSYS.rule$ (ID#,RULE_EXPR,VERSION, 
                                   CREATED_BY,CREATE_DATE,
                                   UPDATED_BY,UPDATE_DATE) 
          VALUES
          (l_seq, 'dvsys.dv_job_invoker = ''' || ee.grantee || '''', 1,
           USER,SYSDATE,USER,SYSDATE);
        END IF;
        INSERT INTO DVSYS.rule_t$(id#, name, language) VALUES
        (l_seq, l_rule_name, 'us');
        INSERT INTO DVSYS.RULE_SET_RULE$ (ID#,RULE_SET_ID#,RULE_ID#,
                                          RULE_ORDER,ENABLED,VERSION,
                                          CREATED_BY,CREATE_DATE,
                                          UPDATED_BY,UPDATE_DATE)
        VALUES(dvsys.rule_set_rule$_seq.NEXTVAL, 10, l_seq, 1,'Y',1,USER,
               SYSDATE,USER,SYSDATE);    
      END IF;
 
    ELSIF ee.grant_type = 'DATAPUMP' THEN
      IF ee.grantee IS NOT NULL THEN
        SELECT dvsys.rule$_seq.nextval INTO l_seq FROM DUAL;
        l_rule_name := 'DVDP$' || TO_CHAR(l_seq);

        IF (ee.object_name IS NOT NULL) AND (ee.object_name != '%') THEN
          INSERT INTO DVSYS.rule$ (ID#,RULE_EXPR,VERSION,
                                   CREATED_BY,CREATE_DATE,
                                   UPDATED_BY,UPDATE_DATE)
          VALUES
          (l_seq, '(dvsys.dv_login_user = ''' || ee.grantee ||
           ''') AND (dvsys.dv_dict_obj_owner = ''' || ee.object_owner ||
           ''') AND (dvsys.dv_dict_obj_name = ''' || ee.object_name || ''')', 1,
           USER,SYSDATE,USER,SYSDATE);

        ELSIF (ee.object_owner IS NOT NULL) AND (ee.object_owner != '%') THEN
          INSERT INTO DVSYS.rule$ (ID#,RULE_EXPR,VERSION,
                                   CREATED_BY,CREATE_DATE,
                                   UPDATED_BY,UPDATE_DATE)
          VALUES
          (l_seq, '(dvsys.dv_login_user = ''' || ee.grantee 
           || ''') AND (dvsys.dv_dict_obj_owner = ''' 
           || ee.object_owner || ''')', 1,
           USER,SYSDATE,USER,SYSDATE);
        ELSE
          INSERT INTO DVSYS.rule$ (ID#,RULE_EXPR,VERSION,
                                   CREATED_BY,CREATE_DATE,
                                   UPDATED_BY,UPDATE_DATE)
          VALUES
          (l_seq, 'dvsys.dv_login_user = ''' || ee.grantee || '''', 1,
           USER,SYSDATE,USER,SYSDATE);
        END IF;

        INSERT INTO DVSYS.rule_t$(id#, name, language) VALUES
        (l_seq, l_rule_name, 'us');

        INSERT INTO DVSYS.RULE_SET_RULE$ (ID#,RULE_SET_ID#,RULE_ID#,
                                          RULE_ORDER,ENABLED,VERSION,
                                          CREATED_BY,CREATE_DATE,
                                          UPDATED_BY,UPDATE_DATE)

        VALUES(dvsys.rule_set_rule$_seq.NEXTVAL, 8, l_seq, 1,'Y',1,USER,
               SYSDATE,USER,SYSDATE);
      END IF;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
/

update dvsys.rule$ set rule_expr = 'UPPER(dvsys.dv_login_user) = UPPER(dvsys.dv_dict_obj_name)' where id#=10;
update dvsys.rule$ set rule_expr = 'DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(''DV_ACCTMGR'',dvsys.dv_login_user) = ''Y''' where id#=3;
update dvsys.rule$ set rule_expr = 'DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(''DBA'',dvsys.dv_login_user) = ''Y''' where id#=4;
update dvsys.rule$ set rule_expr = 'DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(''DV_ADMIN'',dvsys.dv_login_user) = ''Y''' where id#=5;
update dvsys.rule$ set rule_expr = 'DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(''DV_OWNER'',dvsys.dv_login_user) = ''Y''' where id#=6;
update dvsys.rule$ set rule_expr = 'DVSYS.DBMS_MACUTL.USER_HAS_ROLE_VARCHAR(''LBAC_DBA'',dvsys.dv_login_user) = ''Y''' where id#=7;
update dvsys.rule$ set rule_expr = '(DVSYS.DBMS_MACUTL.USER_HAS_SYSTEM_PRIV_VARCHAR(''EXEMPT ACCESS POLICY'',dvsys.dv_login_user) = ''N'') OR USER = ''SYS''' where id#=9;
update dvsys.rule$ set rule_expr = 'DVSYS.DBMS_MACADM.IS_ALTER_USER_ALLOW_VARCHAR(dvsys.dv_login_user) = ''Y''' where id#=14;
update dvsys.rule$ set rule_expr = 'DVSYS.DBMS_MACADM.IS_DROP_USER_ALLOW_VARCHAR(dvsys.dv_login_user) = ''Y''' where id#=22;

-- Bug 9068994 Handle downgrade of Drop User
BEGIN
UPDATE DVSYS.RULE_SET$ SET EVAL_OPTIONS = 2 WHERE ID# =3;
   EXCEPTION
   WHEN OTHERS THEN
     IF SQLCODE IN ( -00001) THEN NULL; --ignore unique constraint violation
     ELSE RAISE;
     END IF;

END;
/

DELETE FROM DVSYS.RULE_SET_RULE$
WHERE ID# = 19
AND   RULE_SET_ID# = 3
AND   RULE_ID# = 22;

DELETE FROM DVSYS.rule$ WHERE ID# = 22;

DELETE FROM DVSYS.rule_t$ WHERE ID# = 22;

-- Need to sync DATAPUMP and JOB ruleset, otherwise, dropping these rule sets
-- in dve111.sql cannot find them.
exec dvsys.dbms_macadm.sync_rules;


--Bug Fix 10225918 - drop directory object.
drop directory DV_ADMIN_DIR;

-- Remove DV_GOLDENGATE_ADMIN role grants.
delete from sys.sysauth$ where privilege# =
  (select user# from user$ where name = 'DV_GOLDENGATE_ADMIN');

-- Remove the realm protection for DV_GOLDENGATE_ADMIN.
delete from DVSYS.realm_object$ where
  object_name = 'DV_GOLDENGATE_ADMIN' and object_type = 'ROLE';

-- Remove DV_XSTREAM_ADMIN role grants.
delete from sys.sysauth$ where privilege# =
  (select user# from user$ where name = 'DV_XSTREAM_ADMIN');

-- Remove the realm protection for DV_XSTREAM_ADMIN.
delete from DVSYS.realm_object$ where
  object_name = 'DV_XSTREAM_ADMIN' and object_type = 'ROLE';

-- Remove DV_GOLDENGATE_REDO_ACCESS role grants.
delete from sys.sysauth$ where privilege# =
  (select user# from user$ where name = 'DV_GOLDENGATE_REDO_ACCESS');

-- Remove the realm protection for DV_GOLDENGATE_REDO_ACCESS.
delete from DVSYS.realm_object$ where
  object_name = 'DV_GOLDENGATE_REDO_ACCESS' and object_type = 'ROLE';

-- Revoke execute on utl_file from DVSYS
revoke execute on utl_file from dvsys;

EXECUTE DBMS_REGISTRY.DOWNGRADED('DV', '11.2.0');