每新入一个员工就要向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';
