Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-4994

Audit log doesn't track default database changes

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: 5.7.23-23, 8.0.12-2rc1
    • Fix Version/s: None
    • Component/s: None

      Description

      When you connect to PS server audit log always shows the database which was specified upon connecting and if you switch default database it will always show the first one in the log. Consequently if you didn't specify any upon connecting DB field will always be empty.

      Here's the test case:

      --audit-log-strategy=SYNCHRONOUS
      set global audit_log_include_databases='test';
      use test;
      create table t1 (a int);
      insert into t1 values (1);
      alter table t1 add column b int;
      
      use test2;
      create table t2 (a int);
      insert into t2 values (1);
      alter table t2 add column b int;
      

      In my log files CASE1 is when I connected without specifying default database and CASE2 is where I specified "test" database as default upon connecting.

      In CASE1 you can see following:

      <AUDIT_RECORD
        NAME="Query"
        RECORD="25_2018-11-05T17:30:20"
        TIMESTAMP="2018-11-05T17:32:26 UTC"
        COMMAND_CLASS="alter_table"
        CONNECTION_ID="5"
        STATUS="0"
        SQLTEXT="alter table t2 add column b int"
        USER="root[root] @ localhost []"
        HOST="localhost"
        OS_USER=""
        IP=""
        DB=""
      />
      

      so here DB field is empty, but table "t2" is actually in "test2" database.

      In CASE2 we get following:

      <AUDIT_RECORD
        NAME="Query"
        RECORD="23_2018-11-05T17:41:50"
        TIMESTAMP="2018-11-05T17:42:44 UTC"
        COMMAND_CLASS="alter_table"
        CONNECTION_ID="5"
        STATUS="0"
        SQLTEXT="alter table t2 add column b int"
        USER="root[root] @ localhost []"
        HOST="localhost"
        OS_USER=""
        IP=""
        DB="test"
      />
      

      so here DB="test", but this is wrong because the actual database of table "t2" is "test2" but it is showing the "test" here because I used that one when first connecting to the server.

       

      For this particular issue you don't even need "set global audit_log_include_databases='test';" but I left it because my logs were created with it so I don't have to record again.

        Smart Checklist

          Attachments

          1. 55_case1_audit.log
            5 kB
          2. 55_case2_audit.log
            5 kB
          3. 56_case1_audit.log
            5 kB
          4. 56_case2_audit.log
            6 kB
          5. 57_case1_audit.log
            9 kB
          6. 57_case2_audit.log
            9 kB
          7. 80_case1_audit.log
            9 kB
          8. 80_case2_audit.log
            9 kB

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                tomislav.plavcic@percona.com Tomislav Plavcic
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 1 hour, 45 minutes
                  1h 45m