ORACLE Responsibility Menu Reference to Other User

每新入一个员工就要向ORACLE增加新用户与权责，如果责任过多的话，一行一行向FORM里添加是有点累人。

况且ORACLE没有角色这种权责分配方便性，无法简便同部门员工的相同权责菜单。

在自己安装的实验环境上进行测试 （VM Workstation + LINUX AS 4.7+ ORACLE EBS R12）。

测试内容： ORACLE Responsibility Menu Reference to Other User.

思考逻辑：

1. 用户权责所在表的拥有者（OWNER = ‘FND’）

2. 创建一个计算OWNER = ‘FND’所有表记录数的汇总表 cux_trigger_tables

3. 计算系统没用登入用户前的表记录数；接着，以一用户登入系统新增加一个新用户权责菜单，此时登出系统，再计算指定表的记录数

4. 返回初始没用登入用户与新增加一个用户权责菜单时间点的指定表记录数差异

1. 创建package：

CREATE OR REPLACE PACKAGE apps.cux_trigger_tools_pkg
AS
   TYPE t_cursor IS REF CURSOR;
/*
CREATE TABLE cux_trigger_tables
(
   trigger_table_id   NUMBER
 , table_name         VARCHAR2 (50)
 , trigger_sql        VARCHAR2 (3000)
 , trigger_app_id     NUMBER
 , num_rows           NUMBER
 , trigger_num_rows   NUMBER
 , trigger_flag       VARCHAR (2)
 , creation_date      DATE
 , trigger_date       DATE
)

-- table sequence id
CREATE SEQUENCE cux_trigger_tables_s
   START WITH 1
   MAXVALUE 2000000000
   MINVALUE 1
   NOCYCLE
   CACHE 20
   NOORDER;


-- testing applications sequence id
CREATE SEQUENCE cux_trigger_app_s
   START WITH 1
   MAXVALUE 2000000000
   MINVALUE 1
   NOCYCLE
   CACHE 20
   NOORDER;
*/

   FUNCTION execute_immediate_sql (p_sql IN VARCHAR2)
      RETURN NUMBER;
      
--step1      
--保证系统当前没有任何登录用户
--p_where = 'FND' 加载OWNER='FND'的所有表插入到cux_trigger_tables
   FUNCTION load_trigger_tables (p_where IN VARCHAR2)
      RETURN NUMBER;

--step2
--保证系统当前没有任何登录用户，计算此时OWNER='FND'的所有表的记录数
   FUNCTION load_tables_counter (p_trigger_app_id IN NUMBER)
      RETURN NUMBER;
      
--step3
--以一个用户登录系统去增加一个新用户以及权责菜单  


--step4  
--增加一个新用户权责菜单后，请登出系统，重新计算此时OWNER='FND'所有表的记录数
   FUNCTION trigger_tables_counter (p_trigger_app_id IN NUMBER)
      RETURN NUMBER;

--step5
--计算出初始没有登录用户表记录数，与新增加一个用户权责后，两个时间点表之间的记录数差异
--返回差异记录数的表
   PROCEDURE search_trigger (p_trigger_app_id   IN     NUMBER
                           , x_cursor              OUT t_cursor);
END cux_trigger_tools_pkg;
/


/* Formatted on 21/05/2013 12:45:10 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE PACKAGE BODY apps.cux_trigger_tools_pkg
AS
   FUNCTION execute_immediate_sql (p_sql IN VARCHAR2)
      RETURN NUMBER
   AS
      v_result   NUMBER;
   BEGIN
      v_result := 0;

      EXECUTE IMMEDIATE p_sql INTO v_result;

      RETURN v_result;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN -1;
   END execute_immediate_sql;


   FUNCTION load_trigger_tables (p_where IN VARCHAR2)
      RETURN NUMBER
   AS
      v_trigger_app_id   NUMBER;
      v_load_count       NUMBER;
   BEGIN
      v_trigger_app_id := cux_trigger_app_s.NEXTVAL;

      INSERT INTO cux_trigger_tables (trigger_table_id
                                    , table_name
                                    , trigger_sql
                                    , trigger_app_id
                                    , creation_date)
         SELECT cux_trigger_tables_s.NEXTVAL
              , table_name
              , ('SELECT COUNT(1) FROM ' || table_name) trigger_sql
              , v_trigger_app_id
              , SYSDATE
           FROM all_tables
          WHERE 1 = 1 AND table_name LIKE '' || p_where || '%';

      COMMIT;


      SELECT COUNT (1)
        INTO v_load_count
        FROM cux_trigger_tables
       WHERE 1 = 1 AND trigger_app_id = v_trigger_app_id;

      IF (v_load_count <= 0)
      THEN
         RETURN -1;
      END IF;

      RETURN v_trigger_app_id;
   END load_trigger_tables;


   FUNCTION load_tables_counter (p_trigger_app_id IN NUMBER)
      RETURN NUMBER
   AS
      v_udpated_count   NUMBER;
   BEGIN
      UPDATE cux_trigger_tables
         SET num_rows = execute_immediate_sql (trigger_sql)
       WHERE 1 = 1 AND trigger_app_id = p_trigger_app_id;

      v_udpated_count := SQL%ROWCOUNT;

      COMMIT;

      RETURN (v_udpated_count);
   END load_tables_counter;

   FUNCTION trigger_tables_counter (p_trigger_app_id IN NUMBER)
      RETURN NUMBER
   AS
      v_udpated_count   NUMBER;
   BEGIN
      UPDATE cux_trigger_tables
         SET trigger_num_rows = execute_immediate_sql (trigger_sql)
           , trigger_date = SYSDATE
       WHERE 1 = 1 AND trigger_app_id = p_trigger_app_id;

      v_udpated_count := SQL%ROWCOUNT;

      COMMIT;

      RETURN (v_udpated_count);
   END trigger_tables_counter;


   PROCEDURE search_trigger (p_trigger_app_id   IN     NUMBER
                           , x_cursor              OUT t_cursor)
   AS
   BEGIN
      UPDATE cux_trigger_tables
         SET trigger_flag = 'Y'
       WHERE     1 = 1
             AND num_rows <> trigger_num_rows
             AND trigger_app_id = p_trigger_app_id;

      COMMIT;

      OPEN x_cursor FOR
         SELECT *
           FROM cux_trigger_tables
          WHERE     1 = 1
                AND trigger_flag = 'Y'
                AND trigger_app_id = p_trigger_app_id;
   END search_trigger;
END cux_trigger_tools_pkg;
/

2. 测试开始：

--step1      
--保证系统当前没有任何登录用户
--加载OWNER='FND'的所有表插入到cux_trigger_tables
DECLARE
   v_count   NUMBER := 0;
   v_owner   NUMBER := 'FND';
BEGIN
   v_count := cux_trigger_tools_pkg.load_trigger_tables (v_owner);
   DBMS_OUTPUT.put_line (v_count);
END;




--step2
--保证系统当前没有任何登录用户，计算此时OWNER='FND'的所有表的记录数
DECLARE
   v_count            NUMBER := 0;
   v_trigger_app_id   NUMBER := 9999;
BEGIN
   v_count := cux_trigger_tools_pkg.load_tables_counter (v_trigger_app_id);
   DBMS_OUTPUT.put_line (v_count);
END;



--step3
--以一个用户登录系统去增加一个新用户以及权责菜单  


--step4  
--增加一个新用户权责菜单后，请登出系统，重新计算此时OWNER='FND'所有表的记录数
DECLARE
   v_count            NUMBER := 0;
   v_trigger_app_id   NUMBER := 9999;
BEGIN
   v_count := cux_trigger_tools_pkg.trigger_tables_counter (v_trigger_app_id);
   DBMS_OUTPUT.put_line (v_count);
END;

/***********************************************************************/


--step5
--计算出初始没有登录用户表记录数，与新增加一个用户权责后，两个时间点表之间的记录数差异
--返回差异记录数的表
  SELECT trigger_table_id
       , table_name
       , trigger_sql
       , trigger_app_id
       , num_rows
       , trigger_num_rows
       , (trigger_num_rows - num_rows) ditinction_rows
       , trigger_flag
       , creation_date
       , trigger_date
    FROM cux_trigger_tables
   WHERE 1 = 1 
         AND num_rows <> trigger_num_rows 
         AND trigger_flag = 'Y'
ORDER BY table_name;

3. 综上测试所知，在实验环境下新增加一个用户权责菜单前后的表记录数差异，再对这些表的信息作人性化分析得知，新增加用户权责菜单相关表有以下：

FND_USER

WF_LOCAL_USER_ROLES

WF_USER_ROLE_ASSIGNMENTS

TABLE_NAME

FND_APPL_SESSIONS

FND_CONCURRENT_REQUESTS

FND_CONC_PP_ACTIONS

FND_CRM_HISTORY

FND_LOGINS

FND_LOGIN_RESPONSIBILITIES

FND_LOGIN_RESP_FORMS

FND_LOG_MESSAGES

FND_LOG_TRANSACTION_CONTEXT

FND_USER

FND_USER_PREFERENCES

FND_USER_RESP_GROUPS

 

--view fnd_user_resp_groups
SELECT u.user_id user_id
     , (SELECT responsibility_name
          FROM fnd_responsibility_tl
         WHERE     responsibility_id = wur.role_orig_system_id
               AND application_id = fa.application_id
               AND language = 'US') app_responsibility_name
     , wur.role_name
     , wur.role_orig_system_id responsibility_id
     , fa.application_id responsibility_application_id
     , fsg.security_group_id security_group_id
     , fnd_date.canonical_to_date ('1000/01/01') start_date
     , TO_DATE (NULL) end_date
     , TO_CHAR (NULL) description
     , TO_NUMBER (NULL) created_by
     , TO_DATE (NULL) creation_date
     , TO_NUMBER (NULL) last_updated_by
     , TO_DATE (NULL) last_update_date
     , TO_NUMBER (NULL) last_update_login
  FROM fnd_user u
     , wf_user_role_assignments_v wura
     , wf_user_roles wur
     , fnd_application fa
     , fnd_security_groups fsg
 WHERE     wura.user_name = u.user_name
       AND wur.role_orig_system = 'FND_RESP'
       AND wur.partition_id = 2
       AND wura.role_name = wur.role_name
       AND wura.user_name = wur.user_name
       AND fa.application_short_name = /* Val between 1 st and 2 nd separator */
              REPLACE (SUBSTR (wura.role_name
                             ,   INSTR (wura.role_name
                                      , '|'
                                      , 1
                                      , 1)
                               + 1
                             , (  INSTR (wura.role_name
                                       , '|'
                                       , 1
                                       , 2)
                                - INSTR (wura.role_name
                                       , '|'
                                       , 1
                                       , 1)
                                - 1))
                     , '%col'
                     , ':')
       AND fsg.security_group_key =             /* Val after 3 rd separator */
                                   REPLACE (SUBSTR (wura.role_name
                                                  ,   INSTR (wura.role_name
                                                           , '|'
                                                           , 1
                                                           , 3)
                                                    + 1)
                                          , '%col'
                                          , ':')
       AND u.user_name = 'QWR';

 

4. 增加一新用户权责菜单：

-- 1. 判斷要插入的 USER_ID 是否存在
SELECT *
  FROM fnd_user
 WHERE 1 = 1 AND user_id = 999;



-- 2. 建立新用戶 USER_ID, USER_NAME, 其它採用引用模板用戶
INSERT INTO fnd_user
   SELECT 999
        , 'QWR_T1'
        , last_update_date
        , last_updated_by
        , creation_date
        , created_by
        , last_update_login
        , encrypted_foundation_password
        , encrypted_user_password
        , session_number
        , start_date
        , end_date
        , description
        , last_logon_date
        , password_date
        , password_accesses_left
        , password_lifespan_accesses
        , password_lifespan_days
        , employee_id
        , email_address
        , fax
        , customer_id
        , supplier_id
        , web_password
        , security_group_id
        , user_guid
        , gcn_code_combination_id
        , person_party_id
     FROM fnd_user
    WHERE 1 = 1 AND user_name = 'QWR';



--3. 建立新用戶的角色 USER_NAME, USER_ID
INSERT INTO wf_local_user_roles
   SELECT 'QWR_T1'
        , role_name
        , user_orig_system
        , 999
        , role_orig_system
        , role_orig_system_id
        , start_date
        , expiration_date
        , security_group_id
        , partition_id
        , assignment_type
        , created_by
        , creation_date
        , last_updated_by
        , last_update_date
        , last_update_login
        , owner_tag
        , parent_orig_system
        , parent_orig_system_id
        , role_end_date
        , role_start_date
        , user_end_date
        , user_start_date
        , effective_start_date
        , effective_end_date
        , assignment_reason
     FROM wf_local_user_roles wur
    WHERE 1 = 1 AND user_name = 'QWR';



--4. 其中有一行，以新用戶建立一個角色
UPDATE wf_local_user_roles
   SET role_name = 'QWR_T1'
     , role_orig_system_id = 999
     , parent_orig_system_id = 999
 WHERE 1 = 1 AND user_name = 'QWR_T1' AND role_name = 'QWR'



--5. 分配給新用戶指定的角色 USER_NAME, USER_ID
INSERT INTO wf_user_role_assignments
   SELECT 'QWR_T1'
        , role_name
        , relationship_id
        , assigning_role
        , start_date
        , end_date
        , created_by
        , creation_date
        , last_updated_by
        , last_update_date
        , last_update_login
        , user_start_date
        , role_start_date
        , assigning_role_start_date
        , user_end_date
        , role_end_date
        , assigning_role_end_date
        , partition_id
        , effective_start_date
        , effective_end_date
        , user_orig_system
        , 999
        , role_orig_system
        , role_orig_system_id
        , parent_orig_system
        , parent_orig_system_id
        , owner_tag
        , assignment_reason
     FROM wf_user_role_assignments
    WHERE 1 = 1 AND user_name = 'QWR';



--6. 其中有一行，以新用戶建立一個新的角色分配
UPDATE wf_user_role_assignments
   SET role_name = 'QWR_T1'
     , assigning_role = 'QWR_T1'
     , role_orig_system_id = 999
 WHERE 1 = 1 AND user_name = 'QWR_T1' AND role_name = 'QWR';