Rem drv: Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/v102010/user_model_data_upgrade.sql /st_emcore_10.2.0.4.2db11.2/1 2008/10/22 19:52:28 pchebrol Exp $ Rem Rem user_model_data_upgrade.sql Rem Rem Copyright (c) 2005, 2008, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem user_model_data_upgrade.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem pchebrol 10/20/08 - Bug 7431956 Rem dcawley 09/01/05 - Remove update of flat role grants Rem dcawley 08/26/05 - Add / Rem dcawley 08/26/05 - Reapply polices on jobs and targets Rem dcawley 08/25/05 - Disable callbacks Rem dcawley 08/15/05 - Rebuld flat role grants Rem dcawley 08/12/05 - Change role grant Rem dcawley 08/12/05 - Do not invalidate contexts during grants Rem chyu 07/26/05 - modifying the migrate header position Rem chyu 07/18/05 - updating the new rep manager header Rem rpinnama 05/12/05 - Rem chyu 04/01/05 - fixing the user_mode_data_upgrade Rem dcawley 02/24/05 - Compress additional tables Rem dcawley 02/17/05 - Include composites Rem dcawley 02/08/05 - Add new privs Rem scgrover 02/01/05 - scgrover_compress_indexes_050131 Rem scgrover 02/01/05 - Created Rem Rem put index compression here since we need to call a plsql procedure to do it. BEGIN EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_PRIV_GRANTS_PK', 2, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_ROLE_GRANTS_PK', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_USER_TARGETS_PK', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_USER_JOBS_PK', 1, FALSE); END; / Rem Rem Add new privileges Rem INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) VALUES ('PUBLISH_REPORT', 0, 'Ability to publish reports for public viewing'); INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) VALUES ('VIEW_ANY_REPORT', 0, 'Ability to view any report'); -- Report Definition privileges INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) VALUES ('VIEW_REPORT', 5, 'Ability to view report definition and stored reports, generate on demand reports and do a create like'); -- Template privileges INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) VALUES ('VIEW_TEMPLATE', 4, 'Ability to view a template and apply it to any target on which you have OPERATOR_TARGET'); INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) VALUES ('FULL_TEMPLATE', 4, 'Ability to view a template and apply it to any target on which you have OPERATOR_TARGET'); -- Corrective Action privileges INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) VALUES ('VIEW_CA', 6, 'Internal privilege, not for granting'); INSERT INTO MGMT_PRIVS (priv_name, priv_type, description) VALUES ('FULL_CA', 6, 'Internal privilege, not for granting'); Rem Rem Privilege includes Rem -- FULL_TEMPLATE includes VIEW_TEMPLATE INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_TEMPLATE', 'VIEW_TEMPLATE'); -- FULL_CA includes VIEW_CA INSERT INTO MGMT_PRIV_INCLUDES VALUES ('FULL_CA', 'VIEW_CA'); Rem Rem Add super user privileges for SYS and SYSTEM Rem INSERT INTO MGMT_PRIV_GRANTS (GRANTEE, PRIV_NAME, GRANTEE_IS_ROLE) VALUES ('SYS', 'SUPER_USER', 0); INSERT INTO MGMT_PRIV_GRANTS (GRANTEE, PRIV_NAME, GRANTEE_IS_ROLE) VALUES ('SYSTEM', 'SUPER_USER', 0); Rem Rem Add callback for target deletion Rem BEGIN EM_TARGET.ADD_TGT_PRE_DELETION_CALLBACK('MGMT_USER.HANDLE_TARGET_DELETED'); INSERT INTO MGMT_TARGET_DELETE_EXCEPTIONS (table_name) VALUES ('MGMT_USER_TARGETS'); COMMIT; END; / Rem Rem Enable the VPD policies if the DBMS_RLS package is present Rem DECLARE fga_option VARCHAR2(32); no_policy EXCEPTION; PRAGMA EXCEPTION_INIT(no_policy, -28102); BEGIN SELECT value INTO fga_option FROM V$OPTION WHERE parameter = 'Fine-grained access control'; DELETE FROM MGMT_PARAMETERS WHERE parameter_name = MGMT_USER.TARGET_POLICY_ENABLED; -- If fine grained access control is available -- IF fga_option = 'TRUE' IF '&EM_REPOS_MODE' = EMD_MAINTENANCE.G_COMPONENT_MODE_CENTRAL THEN -- Drop the old policy on the targets table BEGIN dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_TARGETS', MGMT_USER.EM_TARGET_POLICY_NAME); EXCEPTION WHEN no_policy THEN -- No policy so ignore NULL; END; -- Add the new policy to the targets table dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_TARGETS', MGMT_USER.EM_TARGET_POLICY_NAME, '&EM_REPOS_USER', 'MGMT_USER.EM47455450524544494341544554', 'SELECT', TRUE); -- Drop the old policy on the jobs table BEGIN dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_JOB', MGMT_USER.EM_JOB_POLICY_NAME); EXCEPTION WHEN no_policy THEN -- No policy so ignore NULL; END; -- Add the new policy on the jobs table dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_JOB', MGMT_USER.EM_JOB_POLICY_NAME, '&EM_REPOS_USER', 'MGMT_USER.EM4745545052454449434154454A', 'SELECT', TRUE); -- Drop the old policy on the templates table BEGIN dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_TEMPLATES', MGMT_USER.EM_TEMPLATE_POLICY_NAME); EXCEPTION WHEN no_policy THEN -- No policy so ignore NULL; END; -- Add the new policy on the templates table dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_TEMPLATES', MGMT_USER.EM_TEMPLATE_POLICY_NAME, '&EM_REPOS_USER', 'MGMT_USER.GET_TEMPLATE_PREDICATE', 'SELECT', TRUE); -- Drop the old policy on the report definition table BEGIN dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_IP_REPORT_DEF', MGMT_USER.EM_REPORT_DEF_POLICY_NAME); EXCEPTION WHEN no_policy THEN -- No policy so ignore NULL; END; -- Add the new policy on the report definition table dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_IP_REPORT_DEF', MGMT_USER.EM_REPORT_DEF_POLICY_NAME, '&EM_REPOS_USER', 'MGMT_USER.GET_REPORT_DEF_PREDICATE', 'SELECT', TRUE); -- Drop the old policy on the stored report table BEGIN dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_IP_STORED_REPORT', MGMT_USER.EM_REPORT_POLICY_NAME); EXCEPTION WHEN no_policy THEN -- No policy so ignore NULL; END; -- Add the new policy on the stored report table dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_IP_STORED_REPORT', MGMT_USER.EM_REPORT_POLICY_NAME, '&EM_REPOS_USER', 'MGMT_USER.GET_STORED_REPORT_PREDICATE', 'SELECT', TRUE); -- Drop the old policy on the corrective actions table BEGIN dbms_rls.drop_policy('&EM_REPOS_USER', 'MGMT_CORRECTIVE_ACTION', MGMT_USER.EM_CA_POLICY_NAME); EXCEPTION WHEN no_policy THEN -- No policy so ignore NULL; END; -- Add the new policy on the corrective actions table dbms_rls.add_policy('&EM_REPOS_USER', 'MGMT_CORRECTIVE_ACTION', MGMT_USER.EM_CA_POLICY_NAME, '&EM_REPOS_USER', 'MGMT_USER.GET_CA_PREDICATE', 'SELECT', TRUE); INSERT INTO mgmt_parameters (parameter_name, parameter_value, parameter_comment, internal_flag) VALUES (MGMT_USER.TARGET_POLICY_ENABLED, 'Y', 'Indicates whether fine grained access control will be used', 1); -- SET EM USER CONTEXT FOR REPOS USER SETEMUSERCONTEXT('&EM_REPOS_USER', MGMT_USER.OP_SET_IDENTIFIER); ELSE INSERT INTO mgmt_parameters (parameter_name, parameter_value, parameter_comment, internal_flag) VALUES (MGMT_USER.TARGET_POLICY_ENABLED, 'N', 'Indicates whether fine grained access control will be used', 1); END IF; END; / Rem Rem Temporarily disable the privilege callbacks to try and speed up the Rem processing. There is no need for the callbacks to be issued since Rem the upgrade will not give any additional privileges Rem BEGIN UPDATE MGMT_USER_CALLBACKS SET type = -MGMT_USER.PRIV_GRANTED_CALLBACK WHERE type = MGMT_USER.PRIV_GRANTED_CALLBACK; UPDATE MGMT_USER_CALLBACKS SET type = -MGMT_USER.PRIV_REVOKED_CALLBACK WHERE type = MGMT_USER.PRIV_REVOKED_CALLBACK; COMMIT; END; / Rem Rem Upgrade the MGMT_VIEW_USER Rem DECLARE view_user VARCHAR2(256); BEGIN -- Get the user name and password for view user.. MGMT_VIEW_PRIV.GET_VIEW_USER(view_user); -- Grant view any report em system privilege to view user MGMT_USER.GRANT_PRIV(view_user, 'VIEW_ANY_REPORT', MGMT_USER.NO_GUID, 0); COMMIT; -- Grant execute privilege on MGMT_VIEW_UTIL to view user EXECUTE IMMEDIATE 'GRANT EXECUTE ON MGMT_VIEW_UTIL TO ' || view_user; -- Grant select privilege on MGMT_MESSAGES to view user EXECUTE IMMEDIATE 'GRANT SELECT ON MGMT_MESSAGES TO ' || view_user; -- Grant the MGMT_IP_TGT_GUID_ARRAY type to view user EXECUTE IMMEDIATE 'GRANT EXECUTE ON MGMT_IP_TGT_GUID_ARRAY TO ' || view_user; EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM MGMT_IP_TGT_GUID_ARRAY FOR MGMT_IP_TGT_GUID_ARRAY'; EXCEPTION WHEN OTHERS THEN --Ignore exceptions view_user := ''; END; / Rem Rem Remove indirect grants Rem DELETE FROM MGMT_PRIV_GRANTS WHERE direct_grant = 0; UPDATE MGMT_PRIV_GRANTS SET ref_count = 0; Rem Rem Remove the MANAGE_TARGET_GROUP privilege and replace it with FULL_TARGET Rem BEGIN FOR g IN (SELECT grantee, guid FROM MGMT_PRIV_GRANTS WHERE priv_name = 'MANAGE_TARGET_GROUP') LOOP MGMT_USER.GRANT_PRIV(g.grantee, 'FULL_TARGET', g.guid, 0); END LOOP; DELETE FROM MGMT_PRIV_GRANTS WHERE priv_name = 'MANAGE_TARGET_GROUP'; END; / Rem Rem Create roles for each group Rem Rem Rem Create roles for each group Rem DECLARE rname VARCHAR2(30); cnt NUMBER := 1; BEGIN -- Find the distinct set of all direct group grants FOR group_role IN (SELECT DISTINCT g.priv_name, g.guid, t.target_name FROM MGMT_PRIV_GRANTS g, MGMT_TARGETS t, MGMT_TYPE_PROPERTIES p WHERE direct_grant = 1 AND priv_name IN (MGMT_USER.OPERATOR_TARGET, MGMT_USER.MAINTAIN_TARGET, MGMT_USER.CLONE_FROM_TARGET, MGMT_USER.FULL_TARGET) AND t.target_guid = g.guid AND p.target_type = t.target_type AND p.property_name IN (MGMT_GLOBAL.G_IS_GROUP_PROP, MGMT_GLOBAL.G_IS_COMPOSITE_PROP)) LOOP -- Create a role of the form R_ with the role -- description containing details of the target. The target name -- or target guid cannot be included in the role name due to the -- 30 character size limitation in role name IF group_role.priv_name = MGMT_USER.OPERATOR_TARGET THEN rname := 'R' || cnt || '_OPERATOR'; ELSIF group_role.priv_name = MGMT_USER.MAINTAIN_TARGET THEN rname := 'R' || cnt || '_MAINTAIN'; ELSIF group_role.priv_name = MGMT_USER.CLONE_FROM_TARGET THEN rname := 'R' || cnt || '_CLONE'; ELSIF group_role.priv_name = MGMT_USER.FULL_TARGET THEN rname := 'R' || cnt || '_FULL'; END IF; MGMT_USER.CREATE_ROLE(rname, 'This role has ' || group_role.priv_name || ' on all the members of the group ' || group_role.target_name); -- Get the grantees and grant them the role FOR grnt IN (SELECT g.grantee FROM MGMT_PRIV_GRANTS g, MGMT_TARGETS t, MGMT_TYPE_PROPERTIES p WHERE direct_grant = 1 AND priv_name = group_role.priv_name AND guid = group_role.guid AND t.target_guid = g.guid AND p.target_type = t.target_type AND p.property_name IN (MGMT_GLOBAL.G_IS_GROUP_PROP, MGMT_GLOBAL.G_IS_COMPOSITE_PROP)) LOOP MGMT_USER.GRANT_ROLE(grnt.grantee, rname, 0, 0); END LOOP; -- Grant the privilege on the group to the role MGMT_USER.GRANT_PRIV(rname, group_role.priv_name, group_role.guid, 0); -- Get the members of the group and grant the privilege on each member -- to the role FOR t IN (SELECT member_target_guid FROM MGMT_FLAT_TARGET_MEMBERSHIPS WHERE composite_target_guid = group_role.guid) LOOP MGMT_USER.GRANT_PRIV(rname, group_role.priv_name, t.member_target_guid, 0); END LOOP; COMMIT; cnt := cnt + 1; END LOOP; END; / Rem Rem Remove unused privileges Rem DELETE FROM MGMT_PRIVS WHERE priv_name = 'MANAGE_ANY_USER'; DELETE FROM MGMT_PRIVS WHERE priv_name = 'MANAGE_CREDENTIAL_GROUP'; DELETE FROM MGMT_PRIVS WHERE priv_name = 'MANAGE_TARGET_GROUP'; COMMIT; Rem Rem Enable the privilege callbacks Rem BEGIN UPDATE MGMT_USER_CALLBACKS SET type = MGMT_USER.PRIV_GRANTED_CALLBACK WHERE type = -MGMT_USER.PRIV_GRANTED_CALLBACK; UPDATE MGMT_USER_CALLBACKS SET type = MGMT_USER.PRIV_REVOKED_CALLBACK WHERE type = -MGMT_USER.PRIV_REVOKED_CALLBACK; COMMIT; END; / Rem Rem Clean up the user contexts Rem TRUNCATE TABLE MGMT_USER_CONTEXT; COMMIT;