Oracle 业务表自制审计字段使用

2019独角兽企业重金招聘Python工程师标准>>>

审计字段即记录数据的创建人、创建时间、修改人、修改时间的字段、体现在每一张数据库表中。为了减少代码量需要设置一套通用的方法。

思路：登录用户存入session，访问数据库的时候拦截器获取链接先设入数据库session，随后进行业务逻辑，每个表设置trigger，每次更新时获取session中的用户名称，设置入审计字段。

1、用户信息Web到App的传递。

思路：拦截分发器，每次分发请求的时候在ServiceRequest中设置用户信息传递到App。

首先设置页面拦截，获取session中的用户信息放入用户线程上下文。

web-context.xml

<bean id="sessionInterceptor" class="com.palic.egis.common.web.util.SessionInterceptor">
        <description>AuthorizationController用来检查用户是否login</description>
        <property name="worktableAssociatedFilter">
			<list>
				<value>/index.screen</value>
			</list>
		</property>
    </bean> 
    <bean id="defaultHandlerMapping" class="com.paic.pafa.app.web.servlet.handler.BeanNameUrlHandlerMapping">
        <description>
           	 当一个HTTP请求进来的时候，interceptors先拦截请求，进行预先处理。
        </description>
        <property name="interceptors">
            <list>
                <ref local="sessionInterceptor"/>
              </list>
        </property>
    </bean>

common-context.xml

<!--=====================================================================-->
	<!-- 线程context的配置 -->
	<!--=====================================================================-->
	<bean id="userThreadContext"
          class="com.paic.pafa.core.service.PafaThreadContext">
        <description>线程的Context</description>
    </bean>

SessionInterceptor.java  拦截器实现

/**
 * 用于检查Session里是有用户登录信息的拦截器。Interceptor的功能类似于Servlet的Filter。
 * 当一个HTTP请求进来的时候，interceptors先拦截请求，进行预先处理。
 * 
 * @author Leo Liao, 2005-4-14, created
 * @version $Revision$Date$
 * @see com.palic.egis.support.privilege.web.controller.LoginController
 */
public class SessionInterceptor extends HandlerInterceptorAdapter {
	// 用于获取用户信息的LoginController
	private Controller	authorizationController;

	// 目前SessionAdmin有待完善，请暂时不要使用
	// private SessionAdmin sessionAdmin;

	/**
	 * 检查session里是不是有用户信息
	 * 
	 * @see com.palic.egis.support.privilege.web.controller.LoginController
	 */
	private boolean checkSession(HttpServletRequest request) {
		HttpSession session = request.getSession();
		if (session != null && session.getAttribute("currentUser") != null) {
			return true;
		}
		return false;
	}

	public final boolean preHandle(HttpServletRequest request,
			HttpServletResponse response, Object handler)
			throws PafaWebException {
		try {
			if (!checkSession(request)) {
				authorizationController.handleRequest(request, response);
				if (!checkSession(request)) {
					throw new PafaWebException("appdemo.error.session.invalid");
				}
			}

			// begin 设置当前用户到线程上下文
			HttpSession session = request.getSession();
			String uid = (String) session.getAttribute("currentUser");

			PafaThreadContext tc = (PafaThreadContext) PafaAppCommonContexton
					.getInstance()
					.getBean(SystemObjectName.USER_THREAD_CONTEXT);

			tc.putTxnID(PafaCoreContexton.getInstance().getIDGenerator()
					.getID());
			tc.putUserID(uid);

			// end

		} catch (PafaWebException ex) {
			// throw new PafaWebException("appdemo.error.session.invalid", ex);
			request.getSession().setAttribute("loginError",
					ex.getInitialCause().getMessage());
		} catch (Exception ex) {
			// throw new PafaWebException("appdemo.error.session.invalid", ex);
			request.getSession().setAttribute("loginError", ex.getMessage());
		}
		return true;

	}

	/**
	 * 清除threadContext中设置的用户信息
	 */
	public final void afterCompletion(HttpServletRequest httpservletrequest,
			HttpServletResponse httpservletresponse, Object obj,
			Exception exception) throws Exception {
		PafaThreadContext tc = (PafaThreadContext) PafaAppCommonContexton
				.getInstance().getBean(SystemObjectName.USER_THREAD_CONTEXT);
		tc.clear();

		super.afterCompletion(httpservletrequest, httpservletresponse, obj,
				exception);
	}

}

common-context.xml    分发器配置

<!-- Real pafaAC -->
	<bean id="pafaACTarget"
		class="com.paic.pafa.app.lwc.service.remoting.access.ejb.SmartRemoteStatelessSessionProxyFactoryBean">
		<property name="jndiName">
			<value>ejb/egis/PafaAC</value>
		</property>
		<property name="businessInterface">
			<value>
				com.paic.pafa.app.biz.ac.ApplicationController
			</value>
		</property>
		<property name="jndiTemplate">
			<ref local="pafaACJndi" />
		</property>
	</bean>
	<!-- pafaAC Proxy -->
	<bean id="pafaAC"
        class="com.paic.pafa.app.lwc.core.aop.framework.ProxyFactoryBean">
    	<property name="target">
            <ref local="pafaACTarget"/>
        </property>
    	
    	<property name="proxyInterfaces">
            <value>com.paic.pafa.app.biz.ac.ApplicationController</value>
        </property>
    	<property name="interceptorNames">
    		<list>
    		    <value>dispatchServiceAdvisor</value>
    		</list>
    	</property>
    </bean>
    <bean id="dispatchServiceAdvisor"
        class="com.paic.pafa.app.lwc.core.aop.support.NameMatchMethodPointcutAdvisor">
        <property name="mappedName">
            <value>handleRequest</value>
        </property>
        <property name="advice">
            <ref bean="dispatchServiceInterceptor"/>
        </property>    
    </bean>
    <bean id="dispatchServiceInterceptor"
        class="com.palic.egis.common.web.util.ThreadContextInterceptor">
        <property name="threadContext">
        	<ref bean="userThreadContext"/>
        </property>
    </bean>

ThreadContextInterceptor.java

public class ThreadContextInterceptor implements MethodInterceptor {

	private PafaThreadContext threadContext = null;
	
	public PafaThreadContext getThreadContext() {
		return threadContext;
	}

	public void setThreadContext(PafaThreadContext threadContext) {
		this.threadContext = threadContext;
	}

	public Object invoke(MethodInvocation method) throws Throwable {
		
		Object[] args = method.getArguments();
		
		
		ServiceRequest sr = (ServiceRequest) args[0];
		if (sr != null) {

			SessionDTO dto = sr.getSessionDTO();
			
			if(dto.getUserId() == null){
				dto.setUserId(threadContext.getUserID());				 
			}
			
			if (dto.getTxnId() == null) {
				dto.setTxnId(threadContext.getTxnID());
			}

		}

		return method.proceed();
	}

2、APP层传入数据库

思路：拦截数据源，先设置数据库session在执行业务逻辑

biz-context.xml

<bean id="dsFactory"
        class="com.paic.pafa.app.lwc.service.persistence.datasource.DataSourceFactoryBean" >
        <property name="defaultDSKey">
            <description>缺省的数据源，必须指定为下面map中的entry key之一</description>
            <value>GBSDS</value>
        </property>
        <property name="dataSources">
            <description>可以在map属性里面添加多个数据源</description>
            <map>
                <entry key="GBSDS">
                    <ref local="defaultDS"/>
                </entry>
                <entry key="GBSDS_XA">
                    <ref local="gbsDS_XA"/>
                </entry>
            </map>
        </property>
    </bean>

    <bean id="defaultDS"
		class="com.paic.pafa.app.lwc.core.aop.framework.ProxyFactoryBean">
		<property name="target">
			<ref local="defaultDSTarget" />
		</property>
		<property name="proxyInterfaces">
			<value>javax.sql.DataSource</value>
		</property>
		<property name="interceptorNames">
			<list>
				<value>dbConnectionAdvisor</value>
			</list>
		</property>
	</bean>     
    <bean id="dbConnectionAdvisor"
        class="com.paic.pafa.app.lwc.core.aop.support.NameMatchMethodPointcutAdvisor">
        <property name="mappedName">
            <value>getConnection</value>
        </property>
        <property name="advice">
            <ref bean="dbConnectionInterceptor"/>
        </property>    
    </bean>
    
    <bean id="dbConnectionInterceptor"
        class="com.palic.egis.common.util.SetLcuInterceptor">
    </bean>    
    
   <bean id="defaultDSTarget"
        class="com.paic.pafa.app.lwc.core.naming.JndiObjectFactoryBean">
        <property name="jndiName">
            <value>${defaultDS}</value>
        </property>
        <property name="jndiTemplate">
            <ref local="jndiTemplate"/>
        </property>
    </bean>    
    
    <bean id="gbsDS_XA"
        class="com.paic.pafa.app.lwc.core.naming.JndiObjectFactoryBean">
        <property name="jndiName">
            <value>${GBSDS_XA}</value>
        </property>
        <property name="jndiTemplate">
            <ref local="jndiTemplate"/>
        </property>
    </bean>

SetLcuInterceptor.java

public class SetLcuInterceptor implements MethodInterceptor {

	public Object invoke(MethodInvocation mi) throws Throwable {
		
		String userId = PafaCoreContexton.getInstance().getThreadContext().getUserID();

		Connection conn = (Connection) mi.proceed();
		
		if (userId != null && !userId.startsWith("V_") && !userId.equalsIgnoreCase("GMONIUSER")) {		
			
			try {
				PreparedStatement stat = null;
				
					// 设置LCU
					try {
						stat = conn.prepareStatement("{call pub_sys_package.set_attributes(?)}");
	
						stat.setString(1, userId);					
						stat.execute();
	
					} catch (Throwable e) {
						throw e;
					} finally {
						if (stat != null) {
							stat.close();
						}
					}	
			} catch (Throwable e) {
				DevLog.error("SetLcuInterceptor Error:" + e.getMessage());	
			}
		}
		return conn;
	}


}

pub_sys_package

CREATE OR REPLACE PACKAGE BODY pub_sys_package IS

--get user的两种访问1/pro
  PROCEDURE get_user_p(p_user OUT VARCHAR2) IS  
  BEGIN
    p_user := get_user;
  END get_user_p;
  
--get user的两种访问2/fun
  FUNCTION get_user RETURN VARCHAR2 IS
    v_user VARCHAR2(100);
  
    CURSOR cur_empno IS
      SELECT user_empno
        FROM gbs_user
        --根据登录名称查询
       WHERE user_name = USER;
  
  BEGIN
  
    SELECT sys_context('user_policy_context', 'uid')
      INTO v_user
      FROM dual;
  
    IF v_user IS NULL THEN
      OPEN cur_empno;
      FETCH cur_empno
        INTO v_user;
      CLOSE cur_empno;
    END IF;
  
    IF v_user IS NULL THEN
      v_user := USER;    
    END IF;
  
    RETURN v_user;
  END get_user;

  --***********************************************
  -- 功能说明：
  --   在获得Connection时调用，用于将用户信息设置到数据库的context中
  -- 参数说明：
  --   uid     用户标志
  -- 调用函数：
  --   无
  --***********************************************
  PROCEDURE set_attributes(uid VARCHAR2) IS
    regionlist VARCHAR2(100) := NULL;
  
  BEGIN
    dbms_session.set_context('user_policy_context', 'logon', 'true');
    dbms_session.set_context('user_policy_context', 'uid', uid);
  END set_attributes;

  --***********************************************
  -- 功能说明：
  --   在获得Connection时调用，用于将用户信息设置到数据库的context中。egis-pos专用
  --   在lcu长度不够的时代，保全利用该过程传入full_uid，记录在另外的字段中
  -- 参数说明：
  --   uid     用户标志
  -- 调用函数：
  --   无
  --***********************************************
  PROCEDURE set_attributes(uid VARCHAR2, full_uid VARCHAR2) IS
    regionlist VARCHAR2(100) := NULL;
  BEGIN
    dbms_session.set_context('user_policy_context', 'logon', 'true');
    dbms_session.set_context('user_policy_context', 'uid', uid);
    dbms_session.set_context('user_policy_context', 'full_uid', full_uid);
  END set_attributes;

  --***********************************************
  -- 功能说明：
  --   数据库集中：将从UM中获取的region信息set到context中
  -- 参数说明：
  --   uid            用户名
  --   access_region  可访问的region列表，以,分隔
  -- 调用函数：
  --   无
  --***********************************************
  PROCEDURE set_access_attributes(uid VARCHAR2, access_region VARCHAR2) IS
  BEGIN
    dbms_session.set_context('user_policy_context', 'logon', 'true');
    dbms_session.set_context('user_policy_context', 'uid', uid);
    dbms_session.set_context('user_policy_context',
                             'access_region',
                             access_region);
  END set_access_attributes;



  FUNCTION get_fcu(infcu IN VARCHAR2) RETURN VARCHAR2 IS
    v_fcu VARCHAR2(100);  
    v_user_empno VARCHAR2(100);
  
  BEGIN
  
    v_user_empno := pub_sys_package.get_user;
  
    IF v_user_empno = 'SOLIX' THEN
      --如果是归档用户，则使用原有记录的数据 
      v_fcu := infcu;     
    ELSE    
      v_fcu := v_user_empno;    
    END IF;
  
    RETURN v_fcu;
  END;

  FUNCTION get_fcd(infcd IN VARCHAR2) RETURN DATE IS
    v_fcd DATE;    
    v_user_empno VARCHAR2(100);  
  
  BEGIN
  
    v_user_empno := pub_sys_package.get_user;  
    IF v_user_empno = 'SOLIX' THEN
    --如果是归档用户，则使用原有记录的数据
      v_fcd := infcd;     
    ELSE    
      v_fcd := SYSDATE;    
    END IF;
  
    RETURN v_fcd;
  END;

  FUNCTION get_lcu(inlcu IN VARCHAR2) RETURN VARCHAR2 IS
    v_lcu VARCHAR2(100);
  BEGIN
    
    v_lcu := pub_sys_package.get_user;
    IF v_lcu IS NULL THEN
      v_lcu := inlcu;
    end IF;
    
    RETURN v_lcu;
  END;

  FUNCTION get_lcd(inlcd IN VARCHAR2) RETURN DATE IS
    v_lcd DATE;
  BEGIN    
    v_lcd := SYSDATE;
    RETURN v_lcd;
  END;

END pub_sys_package;

数据库表中trigger实例：

插入

create or replace trigger TR_I_table_name
before insert on table_name
for each row
declare
    --通用变量定义
    v_trigger_user varchar2(100);
    v_trigger_date date;
    v_sqlcode varchar2(6);
    v_sqlerrm varchar2(200);
    v_error_comment varchar2(300);

    --针对审计字段更新功能定义的游标和变量
    cursor c_switch(cp_switch gbs_tr_switch.switch_for%type) is
        select status from gbs_tr_switch
        where trigger_name='TR_I_table_name' and switch_for =cp_switch;
    v_status gbs_tr_switch.status%type;

begin
    v_error_comment:='before get user';
    v_trigger_user :=pub_sys_package.get_user;
    v_trigger_date :=sysdate;

    --需求来源：表中记录的审计字段信息更新
    --功能描述：用于保证审计信息的完整性
    v_error_comment:='before GBS_insert_4_audit_column';
    open c_switch('table_name_in');
    fetch c_switch into v_status;
    if c_switch%found and v_status ='1' then
         :new.created_by:=v_trigger_user;
         :new.created_date:=v_trigger_date;
         :new.updated_by:=v_trigger_user;
         :new.updated_date:=v_trigger_date;
    end if;
    close c_switch;

    --需球来源：XXXX
    --功能描述：XXXX

    --错误处理
    --触发器执行有误，将出错信息插入到gbs_tr_error_log表
    exception
    when others then
    v_sqlcode :=sqlcode;
    v_sqlerrm :=substr(sqlerrm,1,200);
    insert into  gbs_tr_error_log
   (
        error_no ,         --系统错误代码
        error_message ,    --系统错误信息
        trigger_name ,     --出错的trigger
        trigger_user ,     --出错的用户
        trigger_date ,     --出错的时间
        error_comment      --出错详细信息
    )
    values
   (
        v_sqlcode,
        v_sqlerrm,
        'TR_I_table_name',
        v_trigger_user,
        v_trigger_date,
        v_error_comment
   );
end;

更新

CREATE OR REPLACE TRIGGER tr_u_table_name
  BEFORE UPDATE ON table_name
  FOR EACH ROW
DECLARE
  --通用变量定义
  v_trigger_user  VARCHAR2(100);
  v_trigger_date  DATE;
  v_sqlcode       VARCHAR2(6);
  v_sqlerrm       VARCHAR2(200);
  v_error_comment VARCHAR2(300);

  --针对审计字段更新功能定义的游标和变量
  CURSOR c_switch(cp_switch gbs_tr_switch.switch_for%TYPE) IS
    SELECT status
      FROM gbs_tr_switch
     WHERE trigger_name = 'tr_u_table_name'
       AND switch_for = cp_switch;
  v_status gbs_tr_switch.status%TYPE;

BEGIN
  v_error_comment := 'before get user';
  v_trigger_user  := pub_sys_package.get_user;
  v_trigger_date  := SYSDATE;

  --需求来源：表中记录的审计字段信息更新
  --功能描述：用于保证审计信息的完整性
  v_error_comment := 'before GBS_update_2_audit_column';
  OPEN c_switch('table_name_up');
  FETCH c_switch
    INTO v_status;
  IF c_switch%FOUND
     AND v_status = '1' THEN
    :new.updated_by   := v_trigger_user;
    :new.updated_date := v_trigger_date;
  END IF;
  CLOSE c_switch;

EXCEPTION
  WHEN OTHERS THEN
    v_sqlcode := SQLCODE;
    v_sqlerrm := substr(SQLERRM, 1, 200);
    INSERT INTO tr_error_log
      (error_no, --系统错误代码
       error_message, --系统错误信息
       trigger_name, --出错的trigger
       trigger_user, --出错的用户
       trigger_date, --出错的时间
       error_comment --出错详细信息
       )
    VALUES
      (v_sqlcode,
       v_sqlerrm,
       'tr_u_table_name',
       v_trigger_user,
       v_trigger_date,
       v_error_comment);
END;

转载于:https://my.oschina.net/kanlianhui/blog/223801