WL#7194: Define and implement authorization model to manage XA-transactions

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

In current server implementation there is an issue with ability to
commit/rollback XA transaction by a user who doesn’t have sufficient
privileges to access to tables involved in transaction.

Lets consider the following use case:

$ mysql -u root
mysql> CREATE USER u1@localhost;
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE USER u2@localhost;
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE SCHEMA u1_test;
Query OK, 1 row affected (0,01 sec)

mysql> CREATE TABLE u1_test.t1 (a INT);
Query OK, 0 rows affected (0,03 sec)

mysql> GRANT SELECT, INSERT ON u1_test.t1 TO u1@localhost;
Query OK, 0 rows affected (0,01 sec)

mysql> ^DBye

Using the statements listed above, we have created two users. One of them
(u2@localhost) doesn't have permissions to access the table u1_test.t1.

Then a new connection is established to a server using credential of the user
u1@localhost:

$ client/mysql -u u1 u1_test

mysql> XA START 'xid1';
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT INTO t1 VALUES (100);
Query OK, 1 row affected (0,00 sec)

mysql> XA END 'xid1';
Query OK, 0 rows affected (0,00 sec)

mysql> XA PREPARE 'xid1';
Query OK, 0 rows affected (0,00 sec)

mysql> ^DBye

The statements listed above start an XA transaction, insert a row in a table,
end transaction and prepare it to successive commit/rollback.
Since the user u1@localhost has permission on the table u1_test.t1 all these
statements are executed successfully.

Now we connect to a server using credential of the user u2@localhost that
doesn't have permissions to access to the table u1_test.t1.

$ client/mysql -u u2
mysql> XA COMMIT 'xid1’;    <<<===== This statement is executed successfully.
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT * FROM u1_test.t1;
ERROR 1142 (42000): SELECT command denied to user 'u2'@'localhost' for table
't1'
mysql> ^DBye

So as we can see the issue is that an unprivileged user, who  doesn't have
access privileges for the underlying table(s), still can commit/rollback
a prepared XA transaction on behalf of any other user.

The goal of the WL is to add a functionality that allows to restrict capability
for fetching information about a prepared XA transaction to further finalize its
state. 
FR.1: A new system privilege must be introduced to control capability to run the
statement XA RECOVER. The new privilege will be referenced here as
XA_RECOVER_ADMIN.
FR.2: To execute the statement XA RECOVER the new system privilege
XA_RECOVER_ADMIN must be granted to a user.
FR.3: Attempt to run the statement XA RECOVER by a user whom wasn't granted 
the new system privilege XA_RECOVER_ADMIN must lead to issuing an error.
FR.4: A user whom was granted the privilege SUPER and wasn't granted the
privilege XA_RECOVER_ADMIN mustn't be allowed to execute the statement
XA RECOVER.
The simplest and currently the most reasonable way to achieve goals described in
the section High-Level Description is to allow to extract information about
prepared XA transactions for only those users who have a special system 
privilege.
The rationale behind this approach is that
  * without knowing a value of xid it is impossible to finalize a transaction.
    Only a transaction initiator (who knows its xid) or a user with
    a special privilege to fetch information about prepared transactions
    can provide actual (meaningful) xid value for a prepared XA transaction
    branch and thus finalize a XA transaction branch by issuing either
    the statement XA COMMIT or XA ROLLBACK. In other words, it is restricted
    access to the statement XA RECOVER to get a list of prepared XA transactions
    but isn't restricted access to statements XA COMMIT / XA ROLLBACK
    to finalize an XA transaction provided that XID value is known.
    It also need to note that this WL doesn’t aim to protect against brute-force 
    attack when a user tries to finalize a prepared transaction branch by
    guessing its xid value. Therefore this use case is out of scope for the
    worklog.
  * doing this way we don’t change the way the most typical use case works.
    That is, the sequence of statements within the same connection { XA START ;
    some dml statements ; XA END ; XA PREPARE ; XA COMMIT|ROLLBACK }
    still will be executed successfully without changes in behavior after
    the WL7194 be implemented;
  * XA COMMIT/XA ROLLBACK actually just finalize a transaction branch.
    All permissions checking for every DML statement involved in a XA
    transaction were already done. Moreover in case a user runs XA ROLLBACK
    no changes will be made at all and consequently no checking for permissions
    is required. So, the only thing we do matter is whether a user has a system
    privilege to find out a value of XID to further finalize a state of
    a prepared XA transaction.

In case an application that started XA transaction disconnects from a server
and then reconnects again it still will be able to finalize transaction since
the application knows about xid of its prepared XA transaction.

In case application is failed (i.e. the application was crashed or restarted)
before prepared XA transactions were committed/rolled back it is required that
a new system privilege be granted to a user on behalf the application connected
to mysql server in order to get a list of prepared XA transactions.

This new system privilege has the name XA_RECOVER_ADMIN. In case this privilege
wasn’t granted to a user any attempt to execute the statement XA RECOVER by the
user will lead to emission of the error ER_XAER_RMERR. The error code
ER_XAER_RMERR is chosen in according with XA specification for function
xa_recover():
   [XAER_RMERR]
       An error occurred in determining the XIDs to return.
In addition to issuing the error ER_XAER_RMERR the warning 
ER_SPECIFIC_ACCESS_DENIED_ERROR is issued to provide extra information about the
reason of failure.

By default the privilege XA_RECOVER_ADMIN not granted for a new created user.

When server is stared with option --initialize/--initialize-insecure the
privilege XA_RECOVER_ADMIN is granted to the user root@localhost with
GRANT OPTION.

During server upgrade the new privilege will be granted to users who has also
the privilege SUPER provided that there isn't any user who already has the
privilege XA_RECOVER_ADMIN. For this use case the privilege XA_RECOVER_ADMIN
is granted with GRANT OPTION.