2020年5月30日 星期六

記錄資料刪除人員

為確保資料安全性與歸責，AP(應用程式)可直接將異動人員及異動時間等資料一併儲存，並希望能將資料異動歷程寫入Log，常見以Trigger方式於資料庫端主動處理，以下將ORACLE及MSSQL兩種資料庫執行DML(Data Manipulation Language)指令時，在Trigger中所能取得更新前、後之資料整理如下表，以MSSQL當執行新增、修改動作時可使用INSERTED（更新後）、DELETED（更新前）虛擬資料表取得必要資訊，但對於刪除而言，因異動後記錄已不復存在，而無法藉此取得刪除人員，為解決此問題，在此將介紹MSSQL2016新功能SESSION_CONTEXT，藉此可於目前工作階段（Session）中指定及取回索引鍵值方式，即可達成隱匿傳遞參數功能以解決此難題(參數先寫入Session中，Trigger再由所屬Session中取得)，相對ORACLE則可使用DBMS_APPLICATION_INFO套件(Package)完成。
更新前
更新後
ORACLE
:OLD
:NEW
MSSQL
DELETED
INSERTED
新增（INSERT）
未定義，所有欄位均為NULL
新增後資料值
刪除（DELETE）
資料列刪除前（原始值）
未定義，所有欄位均為NULL
修改（UPDAE）
資料列更新前（原始值）
更新後新資料值（更新值）

兩種資料庫於工作階段（Session）設定及取回之函數及基本用法如下（以傳遞員編A825為例）：
資料庫
I/O
參數設定/讀取
ORACLE
寫入
EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO('A825')
讀取
SELECT USERENV('CLIENT_INFO') FROM DUAL
SELECT CLIENT_INFO FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID')
MSSQL
寫入
EXEC sp_set_session_context 'user_id', 'A825';
讀取
SELECT SESSION_CONTEXT(N'user_id');

ORACLE 8i起提供DBMS_APPLICATION_INFO套件(Package)，已歷史悠久不多作著墨，因此僅針對MSSQL2016所提供函數說明如下:
SESSION_CONTEXT: 工作階段（Session）取得索引鍵/值組
SESSION_CONTEXT(N'key')

sp_set_session_context: 工作階段（Session）設定索引鍵/值組
sp_set_session_context [ @key= ] N'key', [ @value= ] 'value' 
                     [ , [ @read_only = ] { 0 | 1 } ] 
[ ; ] 

以下將進行三項測試
測試(1): 更新作業，透過取得更新後(:NEW或 INSERTED)等相資關料已可滿足整所需。
測試(2): 刪除作業，於資料庫端模擬T-SQL或PL/SQL之應用。
測試(3): 刪除作業，於AP端模擬開發程式運行(以C#為例)。
#
測試
異動人員
說明
1
更新資料(PMT:A, Val:10->11)
A802
一般常見模式。
2
刪除資料(PMT:B)
A803
兩者雷同。
3
刪除資料(PMT:C)-以C#為例
A804
略不同。
ORACLE需透過資料庫連線物件CONNECTION之參數傳遞，

測試資料、Trrigger建立，以及後續測試如下:
ORACLE
MSSQL
建立
-------------------------------
--0.1. T1
-------------------------------
--DROP TABLE T1
CREATE TABLE T1
(
PMT          VARCHAR2(10),
VAL          INT,
UPD_USER     VARCHAR2(10),
DATE_UPD     DATE DEFAULT (SYSDATE)
)
-------------------------------
--0.2. T1
-------------------------------
--DROP TABLE T1_LOG
CREATE TABLE T1_LOG
(
ACT          VARCHAR2(10),
PMT          VARCHAR2(10),
VAL          INT,
UPD_USER     VARCHAR2(10),
DATE_CREATE  DATE DEFAULT(SYSDATE)
)  
-------------------------------
--0.3. TRG_T1
-------------------------------
CREATE OR REPLACE TRIGGER TRG_T1
BEFORE UPDATE OR DELETE on T1
FOR EACH ROW
BEGIN
  IF UPDATING THEN
     INSERT INTO T1_LOG
           (ACT, PMT, VAL, UPD_USER)
     VALUES ('UPDATING'
, :NEW.PMT
, :NEW.VAL
, :NEW.UPD_USER);
    END IF;

    IF DELETING THEN--刪除
        INSERT INTO T1_LOG
               (ACT, PMT, VAL, UPD_USER)
        VALUES ('DELETING'
                , :OLD.PMT
                , :OLD.VAL
                , USERENV('CLIENT_INFO')
                )
    END IF;
END;
-------------------------------
--0.1. T1
-------------------------------
--DROP TABLE IF EXISTS T1
CREATE TABLE T1
(
PMT          VARCHAR(10),
VAL          INT,
UPD_USER     varchar(10),
DATE_UPD     date DEFAULT (GETDATE())
)
-------------------------------
--0.2. T1_LOG
-------------------------------
---DROP TABLE IF EXISTS T1_LOG
CREATE TABLE T1_LOG
(
ACT                  varchar(10),
PMT                  varchar(10),
VAL                  int,
UPD_USER             varchar(10),
DATE_CREATE           datetime DEFAULT (GETDATE())
)
-------------------------------
--0.3. TRG_T1
-------------------------------
--DROP TRIGGER IF EXISTS TRG_T1
CREATE TRIGGER TRG_T1
ON T1
FOR UPDATE, DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED)
--UPDATE
     INSERT INTO T1_LOG
           (ACT, PMT, VAL, UPD_USER)
     SELECT 'UPDATING'
, I.PMT, I.VAL
, I.UPD_USER
     FROM INSERTED I
ELSE
--DELETE
     INSERT INTO T1_LOG
           (ACT, PMT, VAL, UPD_USER)
     SELECT 'DELETING'
, D.PMT, D.VAL
        , CAST(SESSION_CONTEXT(N'user_id')
AS varchar(10))  
--SESSION_CONTEXT回傳為sql_variant需轉型
     FROM DELETED D       
END
測試
-------------------------------
--1.0. 測試資料
-------------------------------
/*
TRUNCATE TABLE T1;
TRUNCATE TABLE T1_LOG;
*/
INSERT INTO T1 (PMT, VAL, UPD_USER)
VALUES ('A', 10,  'A801');
INSERT INTO T1 (PMT, VAL, UPD_USER)
VALUES ('B', 20,  'A801');
INSERT INTO T1 (PMT, VAL, UPD_USER)
VALUES ('C', 30,  'A801);
-------------------------------
--1.1. 測試(1)
-------------------------------
UPDATE T1
SET VAL       = 11
  , UPD_USER = 'A802'
  , DATE_UPD = SYSDATE
WHERE PMT='A'
--SELECT * FROM T1_LOG
-------------------------------
--1.2. 測試(2)
-------------------------------
CALL DBMS_APPLICATION_INFO.SET_CLIENT_INFO('A803');
--SELECT USERENV('CLIENT_INFO') FROM DUAL
DELETE T1
WHERE PMT='B'
-------------------------------
--1.0. 測試資料
-------------------------------
/*
TRUNCATE TABLE T1
TRUNCATE TABLE T1_LOG
*/
INSERT INTO T1 (PMT, VAL, UPD_USER)
VALUES('A', 10, 'A801');
INSERT INTO T1 (PMT, VAL, UPD_USER)
VALUES('B', 20, 'A801');
INSERT INTO T1 (PMT, VAL, UPD_USER)
VALUES('C', 30, 'A801');
-------------------------------
--1.1. 測試(1)
-------------------------------
UPDATE T1
SET VAL      = 11
  , UPD_USER = 'A802'
  , DATE_UPD = GETDATE()
WHERE PMT='A'
--SELECT * FROM T1_LOG
-------------------------------
--1.2. 測試(2)
-------------------------------
EXEC sp_set_session_context 'user_id', 'A803'
--SELECT CAST(SESSION_CONTEXT(N'user_id') AS varchar(10))
DELETE T1
WHERE PMT='B'

測試(3)-C#
DB
C#
ORACLE
using Oracle.ManagedDataAccess.Client;
(略)
string sConn = "Data Source=TestDB; User Id=Dev; Password=admin;";
string sSQL  = "DELETE T1 WHERE PMT='C'";
using (OracleConnection conn = new OracleConnection(sConn))
{
    conn.Open();               
    conn.ClientInfo = "A804"; //寫入員編(透過資料庫連線元件參數)
    OracleCommand cmd = new OracleCommand(sSQL);
    cmd.Connection = conn;

    int nRet = cmd.ExecuteNonQuery();
}
MSSQL
using System.Data.SqlClient;
(略)
string sConn = "Data Source=TestDB;Initial Catalog=DevDB;User ID=dev;Password=admin;";
string sSQL = @"EXEC sp_set_session_context 'user_id', 'A804'; //寫入員編
                 DELETE T1 WHERE PMT='C'";
using (SqlConnection conn = new SqlConnection(sConn))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand(sSQL, conn);
    int nRet = cmd.ExecuteNonQuery();
}

執行觸發記錄(T1_LOG)如下所示。其中最重要的在於測試(2)及測試(3)，此解決過去之難題。
ACT
PMT
VAL
UPD_USER
DATE_CREATE
1
UPDATING
A
11
A802
2020/05/30 06:31:04
2
DELETING
B
20
A803
2020/05/30 06:31:16
3
DELETING
C
30
A804
2020/05/30 06:32:46

本文所介紹如何在目前工作階段（Session）中利用隱匿傳遞參數之功能，除可解決前述記錄刪除人員外，先前也曾用以解決VIEW執行效能不佳問題，先將重要篩選條件以此方式傳入VIEW中，VIEW中即中即可藉此有效限縮資料空間進而提升效能，尤其是VIEW之底層原始建構資料表資料量稍大或組成複雜時，資料庫效能難以提升時，此可為另一項解決選項。


參考資料:
1.    https://docs.microsoft.com/EN-US/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql?view=sql-server-2016
2.    https://docs.microsoft.com/EN-US/sql/t-sql/functions/session-context-transact-sql?view=sql-server-2016
3.    https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_appinf.htm


張貼者： 於

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)