WL#13562: CREATE/ALTER USER COMMENT 'JSON'

Currently mysql.user has a JSON column as follows: 
User_attributes JSON DEFAULT NULL,

This is used to store extra user attributes. Add to it a "user comment" JSON to
allow users to also store their user account metadata into the column:

ALTER USER foo COMMENT '{ "foo":1, [ "a", "b", "c" ] }';

Expose the user comment into the I_S table(s) for users. 

Eventually consider adding indexes over it. 

S1. The new reserved key in the mysql.user.user_attribute JSON field will be
added. The key name will be "metadata" and it will contain a valid JSON object.

S2. Searching the core mysql.user table will likely not be supported in
future releases, so in addition an I_S table "user_attributes" is created with
the fields( USER, HOST, ATTRIBUTE ). ATTRIBUTE contain the data from the
mysql.user.user_attributes->$.metadata sub-field.

S2-b. An additional field COMMENT can be added to the mysql.user table in the
future which supports a FULLTEXT index in the underlying base table
mysql.user_comments (user_attributes->$.metadata.comment. This will however
not implemented in this worklog.

S3. Two statements will be used to add user attributes when creating an 
account.
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option]
    [user_comment] ...

user_comment:
a) ATTRIBUTE quoted_string
where quoted_string is valid JSON object. Example: '{ "free_text" : "This is a
free form text" }' 
OR
b) COMMENT quoted_string
where quoted string is any arbitrary string. Example: 'This is a free form 
text',

If (b) is used a default key "comment" is used when storing the attribute
under mysql.user.user_attributes->$.metadata[0]

The JSON array in (a) is user defined and my contain any key or value.

S4. Two statements for modifying an existing user account will be added to
correspond to the statements in S4. These two statements are exclusive to each
other. 

a) ALTER USER .. ATTRIBUTE '{ "free_text" : "This is a free form text" }';
b) ALTER USER .. COMMENT 'This is a free form text';


S5. If the ATTRIBUTE directive is used in S4 or S5 then the following JSON
parameter must be validated as valid JSON during semantic phase.

S6. If any subfields of the JSON object in mysql.user.user_attributes-
>$.metadata are missing the default value for the I_S.user_attributes.ATTRIBUTE
field is assumed to be an empty string.

S7. SHOW CREATE USER will show how to replicate the JSON object in 
mysql.user.user_attributes->$.metadata[0] using CREATE USER .. ATTRIBUTE, 

S8. All new features will rely on the the previously established privilege 
requirements for CREATE USER and ALTER USER statements,ie global "CREATE USER" 
or the "UPDATE ON mysql.*". 

I1. ALTER USER  COMMENT 

The statement associates an arbitrary text string  to the
authorization ID . This statement only replaces any previous text comment
associated with the  but keeps the previously assigned attributes.

I2. ALTER USER  ATTRIBUTE 

The statement validates  as a valid JSON object before
associating it with the  authorization ID. This operation merges any
previous comments or JSON attributes associated with . If two keys collide
the new key value replace the old key value. If the new key value is null, the
key is dropped.

I3. New table: information_schema.user_attributes
(
HOST char(255) CHARACTER SET ASCII DEFAULT '' NOT NULL,
USER char(32) binary DEFAULT '' NOT NULL,
ATTRIBUTES JSON DEFAULT '',
)

defined as SELECT host,user,user_attributes->"$.comment" FROM mysql.user;


I4. CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option]
    [user_comment] ...

user_comment:
a) ATTRIBUTES quoted_string
where quoted_string is valid JSON object. Example: '{ "free_text" : "This is a
free form text" }' 
OR
b) COMMENT quoted_string
where quoted string is any arbitrary string. Example: 'This is a free form text',

If (b) is used a default key "comment" is used when storing the attribute
under mysql.user.user_attributes->$.metadata[0]

The JSON array in (a) is user defined and may contain any key or value. The key
"comment" is however reserved for storing arbitrary strings using the ALTER USER
.. COMMENT statement.

D1. A new function mysql_alter_user_comment(THD *thd ,List users,
std::string blob, bool expect_text) is introduced.
It does the following:
0. if the expect_text is true:
  1. open the mysq.user table
  2. read the user_attribute field
  3. Parse the information as a JSON object.
  4. Extract the mysql.user_attribute->"$.metadata[0].comment" and replace the
value with the new comment as a string.
  5. commit the changes and close the mysql.user table.
  6. Notable exceptions:
    E4-1) If the mysql.user_attribute->"$.metadata[0].comment" isn't a valid
JSON path, a new JSON object is created with this path. Note that this will not
affect any JSON key-value pairs in either the mysql.user_attribute field nor the
mysql.user_attribute->"$.metadata" field if these already exist.

1. if the expect_text is false:
  1. parse the blob parameter as a JSON object.
  2. open the mysql.user table.
  3. Read the user_attribute field
  4. Extract the mysql.user_attribute->"$.metadata[0]" and replace the value with
the newly parsed JSON object. All previous values are lost.
  5. commit the changes and close the mysql.user table.
  6. Notable exceptions:
     E1-1: In case the blob parameter can't be parsed, set the DA to an error state.
     E2-4: if the mysql.user_attribute->"$.metadata[0]" field isn't a valid JSON
path, a new JSON object is created with this path. Note that this will not
affect the JSON key-values in the mysql.user_attribute field if there already
are such key-values.