1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Question about test the query table management ?

Discussion in 'SQL PL/SQL' started by beckham2000, Aug 26, 2017.

  1. beckham2000

    beckham2000 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    technology
    Dear all member!

    I need to setup auditing (or logging) for RESULTS of queries that are run by certain users against certain tables.

    Fine-grained auditing will result in one audit record with the text of the select statement. But how can I audit the actual result of a select query? It seems to me that it should be "select for each row" trigger for this that does not exist.
    For example, when they run some select statement against "patients" table I need to log all PatientID that were selected from this table. So if there were 100 records selected, there should be 100 "audit" records with patientID logged. Is this possible?

    I will appreciate any help.
    Thank you very much!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, not with a trigger. As you have discovered no such 'on select' trigger exists as it's not a transactional operation (insert, update and delete are). You COULD write a procedure to retrieve the data an in that procedure include an insert to your audit table:

    Code (SQL):
    CREATE TABLE patients(
    patient_id    varchar2(20) NOT NULL,
    patient_name    varchar2(80) NOT NULL,
    patient_addr    varchar2(200),
    patient_dob    varchar2(12),
    patient_disease    varchar2(80));

    CREATE TABLE patient_info_audit(
    patient_id    varchar2(20) NOT NULL,
    queried_by    varchar2(120),
    query_dt    DATE);

    CREATE OR REPLACE PROCEDURE query_patient_info(p_patientid IN varchar2)
    IS
        cursor get_patient_data IS
        SELECT *
        FROM patients
        WHERE patient_id = p_patientid;
    BEGIN
        FOR p IN get_patient_data loop
            dbms_output.put_line('===================================================================');
            dbms_output.put_line('Patient ID     : '||p.patient_id);
            dbms_output.put_line('Patient name   : '||p.patient_name);
            dbms_output.put_line('Patient address: '||p.patient_addr);
            dbms_output.put_line('Patient DOB    : '||p.patient_dob);
            dbms_output.put_line('Patient disease: '||p.patient_disease);
            dbms_output.put_line('===================================================================');

            INSERT INTO patient_info_audit
            VALUES(p.patient_id, USER, sysdate);
            commit;
        END loop;
    END;
    /

    SHOW errors
     
    The results would be similar to this:

    Code (SQL):
    ===================================================================
    Patient ID     : ABCDEFG1
    Patient name   : Smorgo Fleedleone
    Patient address: 1 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/01
    Patient disease: Guasternia
    ===================================================================
    ===================================================================
    Patient ID     : ABCDEFG2
    Patient name   : Smorgo Fleedletwo
    Patient address: 2 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/02
    Patient disease: Guasternia
    ===================================================================
    ===================================================================
    Patient ID     : ABCDEFG3
    Patient name   : Smorgo Fleedlethree
    Patient address: 3 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/03
    Patient disease: Guasternia
    ===================================================================
    ===================================================================
    Patient ID     : ABCDEFG4
    Patient name   : Smorgo Fleedlefour
    Patient address: 4 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/04
    Patient disease: Guasternia
    ===================================================================
    ===================================================================
    Patient ID     : ABCDEFG5
    Patient name   : Smorgo Fleedlefive
    Patient address: 5 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/05
    Patient disease: Guasternia
    ===================================================================
    ===================================================================
    Patient ID     : ABCDEFG6
    Patient name   : Smorgo Fleedlesix
    Patient address: 6 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/06
    Patient disease: Guasternia
    ===================================================================
    ===================================================================
    Patient ID     : ABCDEFG7
    Patient name   : Smorgo Fleedleseven
    Patient address: 7 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/07
    Patient disease: Guasternia
    ===================================================================
    ===================================================================
    Patient ID     : ABCDEFG8
    Patient name   : Smorgo Fleedleeight
    Patient address: 8 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/08
    Patient disease: Guasternia
    ===================================================================
    ...
    ===================================================================
    Patient ID     : ABCDEFG99
    Patient name   : Smorgo Fleedleninety-nine
    Patient address: 99 Geeble Lane, Yopneester, Quiaic
    Patient DOB    : 12/12/99
    Patient disease: Guasternia
    ===================================================================

    PL/SQL PROCEDURE successfully completed.

    BING @ quanghoo >
    BING @ quanghoo > SELECT COUNT(*) FROM patient_info_audit;

      COUNT(*)
    ----------
            99

    BING @ quanghoo >
    BING @ quanghoo > SELECT * FROM patient_info_audit;

    PATIENT_ID           QUERIED_BY   QUERY_DT
    -------------------- ------------ ---------
    ABCDEFG1             BING         26-AUG-17
    ABCDEFG2             BING         26-AUG-17
    ABCDEFG3             BING         26-AUG-17
    ABCDEFG4             BING         26-AUG-17
    ABCDEFG5             BING         26-AUG-17
    ...
    ABCDEFG98            BING         26-AUG-17
    ABCDEFG99            BING         26-AUG-17

    99 ROWS selected.

    BING @ quanghoo >
    A procedure is your only option since no one is updating, inserting or deleting data.