Viewing Historical Operation Records in the Ledger

Prerequisites

• You are an audit administrator or a role that has the AUDITADMIN permission.
• The database is running properly, and a series of addition, deletion, and modification operations are performed on the tamper-proof database to ensure that operation records are generated in the ledger for query.
• The global blockchain table of each CN in the database records data separately. The global blockchain table records only the SQL operations performed on the current CN.

Context

• Only users with the AUDITADMIN attribute can view historical operation records in the ledger. For details about database users and how to create users, see "Database Security Management > Managing Users and Their Permissions > Users" in Developer Guide.
• To query the global blockchain table gs_global_chain, run the following command:

  SELECT * FROM gs_global_chain;

  This table contains 10 columns. For details about each column, see "System Catalogs and System Views > System Catalogs > GS_GLOBAL_CHAIN" in Developer Guide.

• To query the user history table in BLOCKCHAIN schema, the operation is as follows:

  For example, if the schema of the user table is ledgernsp, the table name is usertable, and the name of the corresponding user history table is blockchain.ledgernsp_usertable_hist, you can run the following command:

  SELECT * FROM blockchain.ledgernsp_usertable_hist;

  The user history table contains four fields. For details about the meaning of each field, see Table 1.

  

  Generally, the name of a user history table is in the format of blockchain.<schemaname>_<tablename>_hist. If the schema name or table name of the tamper-proof user table is too long, the length of the table name generated using the preceding format may exceed the upper limit. In this case, the blockchain.<schema_oid>_<table_oid>_hist format is used to name the table.

Procedure

1. View records in the global blockchain table.

   1	gaussdb=# SELECT * FROM gs_global_chain;

   The query result is as follows:

    blocknum |  dbname  | username |           starttime           | relid |  relnsp   |  relname  |     relhash      |            globalhash            |
                   txcommand
   ----------+----------+----------+-------------------------------+-------+-----------+-----------+------------------+----------------------------------+------------------
   ------------------------------------------------------------
           0 | testdb | omm      | 2021-04-14 07:00:46.32757+08  | 16393 | ledgernsp | usertable | a41714001181a294 | 6b5624e039e8aee36bff3e8295c75b40 | insert into ledge
   rnsp.usertable values(1, 'alex'), (2, 'bob'), (3, 'peter');
           1 | testdb | omm      | 2021-04-14 07:01:19.767799+08 | 16393 | ledgernsp | usertable | b3a9ed0755131181 | 328b48c4370faed930937869783c23e0 | update ledgernsp.
   usertable set name = 'bob2' where id = 2;
           2 | testdb | omm      | 2021-04-14 07:01:29.896148+08 | 16393 | ledgernsp | usertable | 0ae4b4e4ed2fcab5 | aa8f0a236357cac4e5bc1648a739f2ef | delete from ledge
   rnsp.usertable where id = 3;

   The query result indicates that user omm has consecutively executed three DML commands: INSERT, UPDATE, and DELETE.

2. View records in the user history table.

   1	gaussdb=# SELECT * FROM blockchain.ledgernsp_usertable_hist;

   The query result is as follows:

    rec_num |     hash_ins     |     hash_del     |             pre_hash
   ---------+------------------+------------------+----------------------------------
          0 | 1f2e543c580cb8c5 |                  | e1b664970d925d09caa295abd38d9b35
          1 | 8fcd74a8a6a4b484 |                  | dad3ed8939a141bf3682043891776b67
          2 | f51b4b1b12d0354b |                  | 53eb887fc7c4302402343c8914e43c69
          3 | 437761affbb7c605 | 8fcd74a8a6a4b484 | c2868c5b49550801d0dbbbaa77a83a10
          4 |                  | f51b4b1b12d0354b | 9c512619f6ffef38c098477933499fe3
   (5 rows)

   The query result shows that user omm inserts three rows of data to the ledgernsp.usertable table, updates one row of data, deletes one row of data, and leaves two rows of data, and the hash values are 1f2e543c580cb8c5 and 437761affbb7c605.

3. Query user table data and verification columns.

   1	gaussdb=# SELECT *, hash_69dd43 FROM ledgernsp.usertable;

   The query result is as follows:

    id | name |       hash_69dd43
   ----+------+------------------
     1 | alex | 1f2e543c580cb8c5
     2 | bob2 | 437761affbb7c605
   (2 rows)

   The query result indicates that the remaining two records in the user table are the same as those in step 2.