Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/masking/dm_pkgbody.sql /st_emdbsa_11.2/1 2009/01/29 14:28:58 kmckeen Exp $
Rem
Rem dm_pkgbody.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dm_pkgbody.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    rpattabh    06/12/08 - Compound Masking Changes
Rem    xshen       02/19/08 - eliminate dynamic sql for security
Rem    xshen       02/04/08 - continue conditional masking
Rem    xshen       07/17/07 - fix security bug 6218732
Rem    xshen       07/12/07 - remove masking job when deleting scope
Rem    xshen       03/27/07 - priv checks
Rem    xshen       03/15/07 - outer join for scopes
Rem    xshen       12/03/06 - masking job api
Rem    xshen       09/20/06 - array list
Rem    xshen       08/07/06 - add get_masking_definitions call
Rem    xshen       07/26/06 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_DM AS 

  -- Create a masking definition, return ss guid
  function create_masking_definition(
        p_owner in varchar2,
        p_name in varchar2,
        p_source_name in varchar2,
        p_source_type in varchar2,
        p_description in varchar2 default null)
  return raw
  is
        l_ss_guid raw(16) := sys_guid();
        l_source_id raw(16) := mgmt_target.get_target_guid(p_source_name,p_source_type);
  begin
        insert into mgmt_dm_scopespecs
          (ss_guid,ss_owner,ss_name,source_id,source_name,description)
        values
          (l_ss_guid,p_owner,p_name,l_source_id,p_source_name,p_description);
        return l_ss_guid;
  end create_masking_definition;

  -- Create a masking rule, depending on is_library, either an internal rule or library template, return rule guid
  function create_masking_rule(
        p_owner in varchar2,
        p_name in varchar2,
        p_description in varchar2 default null,
        p_data_type in number default 0,
        p_is_library in number default 0,
        p_rule_order in number default 1,
        p_rule_condition in varchar2 default null,
        p_ss_guid in raw default null,
        p_masked_schema in varchar2 default null,
        p_masked_table in varchar2 default null,
        p_masked_column in varchar2 default null)
  return raw
  is
        l_rule_guid raw(16) := sys_guid();
  begin
        insert into mgmt_dm_ruletemplates 
          (rule_guid,rule_owner,rule_name,description,output_type,is_library,rule_order,rule_condition)
        values
          (l_rule_guid,p_owner,p_name,p_description,p_data_type,p_is_library,p_rule_order,p_rule_condition);

        -- 11g: conditional masking change - Add a child record in COLUMN_RULES table
	if (p_is_library = 0 and l_rule_guid is not null and p_ss_guid is not null and p_masked_schema is not null and p_masked_table is not null and p_masked_column is not null) then
            insert into mgmt_dm_column_rules 
                (ss_guid,table_schema,table_name,column_name,rule_guid)
            values
                (p_ss_guid,p_masked_schema,p_masked_table,p_masked_column,l_rule_guid);
 	end if;

        return l_rule_guid;

  end create_masking_rule;

  procedure add_rule_entry(
        p_rule_guid in raw,
        p_entry_order in number,
        p_rule_type in varchar2, 
        p_rule_option in varchar2,
        p_rule_low in number,
        p_rule_high in number,
        p_start_date in date,
        p_end_date in date,
        p_fixed_string in varchar2,
        p_fixed_number in number,
        p_table_schema in varchar2,
        p_table_name in varchar2,
        p_column_name in varchar2,
        p_udf_name in varchar2,
        p_arr_list in mgmt_dm_array_list default null)
  is
  begin
        -- insert one rule entry
        insert into mgmt_dm_ruleentry
          (rule_guid,entry_order,rule_type,rule_option,rule_high,rule_low,start_date,end_date,fixed_string,fixed_number,table_schema,table_name,column_name,udf_name)
        values
          (p_rule_guid,p_entry_order,p_rule_type,p_rule_option,p_rule_high,p_rule_low,p_start_date,p_end_date,p_fixed_string,p_fixed_number,p_table_schema,p_table_name,p_column_name,p_udf_name);

        -- bulk insert array list items
        if (p_rule_type = mgmt_dm.rule_type_arraylist and p_arr_list is not null) then
           for i in 1..p_arr_list.count loop
             insert into mgmt_dm_alitems (rule_guid,entry_order,arraylist_item) 
             values (p_rule_guid,p_entry_order,p_arr_list(i));
           end loop;
        end if;

  end add_rule_entry;

  -- Note: distinct value is not used by rule processor and will not be saved later.  
  procedure add_ss_column(
        p_ss_guid in raw,
        p_table_schema in varchar2, 
        p_table_name in varchar2,
        p_column_name in varchar2,
        p_column_group in varchar2)
  is
  begin
        insert into mgmt_dm_ss_columns
          (ss_guid,table_schema,table_name,column_name,column_group)
        values
          (p_ss_guid,p_table_schema,p_table_name,p_column_name,p_column_group);
  end add_ss_column;

  procedure add_infcons_column(
        p_ss_guid in raw,
        p_table_schema in varchar2, 
        p_table_name in varchar2,
        p_column_name in varchar2,
        p_parent_schema in varchar2, 
        p_parent_table in varchar2,
        p_parent_column in varchar2)
  is
  begin
        insert into mgmt_dm_infcons_columns
          (ss_guid,table_schema,table_name,column_name,parent_schema,parent_table,parent_column)
        values
          (p_ss_guid,p_table_schema,p_table_name,p_column_name,p_parent_schema,p_parent_table,p_parent_column);
  end add_infcons_column;

  procedure remove_masking_rule(p_rule_guid in raw)
  is
  begin
      -- security check
      if (has_remove_priv(p_rule_guid,masking_format_t) = 0) then 
        return;  
      end if;

      delete mgmt_dm_ruletemplates where rule_guid = p_rule_guid;

  end remove_masking_rule;

  procedure remove_rule_entries(p_rule_guid in raw)
  is
      l_em_user varchar2(256):= mgmt_user.get_current_em_user();     
  begin

      delete mgmt_dm_ruleentry where rule_guid = p_rule_guid;

  end remove_rule_entries;

  procedure update_masking_rule(
        p_rule_guid in raw,
        p_name in varchar2,
        p_description in varchar2 default null,
        p_data_type in number default 0,
        p_is_library in number default 0)
  is
  begin
        
        -- security check
        if (has_remove_priv(p_rule_guid,masking_format_t) = 0) then 
           return;  
        end if;

        update mgmt_dm_ruletemplates set rule_name = p_name where rule_guid = p_rule_guid;

        update mgmt_dm_ruletemplates set description = p_description where rule_guid = p_rule_guid;

        update mgmt_dm_ruletemplates set output_type = p_data_type where rule_guid = p_rule_guid;

        update mgmt_dm_ruletemplates set is_library = p_is_library where rule_guid = p_rule_guid;

  end update_masking_rule;

  procedure get_masking_rule_arraylist(
        p_rule_guid   in raw,
        p_entry_order in number,
        p_array_list  out mgmt_dm_array_list)
  is 
  begin
        select arraylist_item bulk collect into p_array_list from mgmt_dm_alitems 
         where rule_guid = p_rule_guid and entry_order = p_entry_order;
  end get_masking_rule_arraylist;

  -- For now do not remove Job, Job page does not reference masking UI.
  -- Also removes the Rule Templates that is associated with the ss columns.
  procedure remove_masking_definition(p_ss_guid in raw)
  is
      -- using a guid table
      l_rule_guids mgmt_target_guid_array := null;
      l_exec_ids mgmt_job_guid_array := null;
  begin
      -- security check
      if (has_remove_priv(p_ss_guid,masking_definition_t) = 0) then 
        return;  
      end if;

      -- old style (10.2) rules
      select rule_guid bulk collect into l_rule_guids 
        from mgmt_dm_column_rules
       where ss_guid = p_ss_guid;

      -- Remove rules associated with the l_rule_guids
      if (l_rule_guids is not null and l_rule_guids.count > 0) then
        for i in l_rule_guids.first..l_rule_guids.last loop
          delete mgmt_dm_ruletemplates where rule_guid = l_rule_guids(i);
        end loop;
      end if;

      -- 11g subset rules
      select rule_guid bulk collect into l_rule_guids 
        from mgmt_dm_column_rules 
       where ss_guid = p_ss_guid;

      -- Remove rules associated with the l_rule_guids
      if (l_rule_guids is not null and l_rule_guids.count > 0) then
        for i in l_rule_guids.first..l_rule_guids.last loop
          delete mgmt_dm_ruletemplates where rule_guid = l_rule_guids(i);
        end loop;
      end if;

      -- Remove job execution ids
      begin
      
        select execution_id bulk collect into l_exec_ids 
          from mgmt_dm_job_executions
         where ss_guid = p_ss_guid;
         
         if (l_exec_ids is not null and l_exec_ids.count > 0) then
         
            mgmt_jobs.delete_job_executions(p_execution_ids=>l_exec_ids, p_commit=>1);
            
         end if;
         
      exception when others then null;
      
      end;

      -- Remove scope and ss_columns (on delete cascade)
      delete mgmt_dm_scopespecs where ss_guid = p_ss_guid;

      commit;

  end remove_masking_definition;

  procedure add_masking_job(
        p_ss_guid   in raw,
        p_execution_id in raw)
  is 
  begin
      insert into mgmt_dm_job_executions (ss_guid, execution_id, submission_ts) values (p_ss_guid, p_execution_id, sysdate);
  end add_masking_job;

-- Check if current em user has the right to delete masking definition or format
--
-- p_guid the guid of the object 
-- p_type: 0 - masking definition
--         1 - masking format
--
-- returns 0 if can not remove
-- returns 1 if can remove
function has_remove_priv(p_guid IN RAW, p_type IN NUMBER)
return NUMBER
is
   l_em_user VARCHAR2(256) := MGMT_USER.get_current_em_user();
   l_owner VARCHAR2(256);
begin
   -- Super user can remove everything.
   IF (MGMT_USER.has_priv(l_em_user, MGMT_USER.SUPER_USER) = 1) THEN
     return 1;
   ELSE
     -- Case baseline 
     BEGIN
       IF p_type = MASKING_DEFINITION_T THEN
        SELECT ss_owner INTO l_owner 
          FROM mgmt_dm_scopespecs 
         WHERE ss_guid = p_guid;
       ELSIF p_type = MASKING_FORMAT_T THEN
        SELECT rule_owner INTO l_owner 
          FROM mgmt_dm_ruletemplates 
         WHERE rule_guid = p_guid;
       END IF; 
     EXCEPTION WHEN OTHERS THEN NULL;
     END;

   END IF;

   -- Creator can remove it.
   IF (l_owner is not null and l_owner = l_em_user) THEN
       return 1;
   END IF;

   return 0;

end has_remove_priv;

END;
/

show errors;