Support setting session vars in user property

[yandongxiao]

• [Feature] Support setting session vars in user property #48477

Feature request

In order to let the newly established JDBC connection default to connecting to the external catalog and the database, and since it is not possible to specify the external catalog and external database in the BI tool (client side), so we can save user's properties on the FE(the server side).

When the user connects to FE SQL, the Session variables need to be initialized according to the user's attribute information.

We can support to set more attributes, including: catalog, database, and session.xxx.

Describe the solution you'd like

We can use the following methods to CRUD user's properties

1. Supports displaying user attributes through the SHOW PROPERTY method.
2. Supports setting user attributes through ALTER USER. like, ALTER USER (IF EXISTS)? user SET properties
3. Supports setting the session.xxx attributes of a user through CREATE USER. Like CREATE USER (IF NOT EXISTS)? user authOption? (DEFAULT ROLE roleList)? properties?
4. Note: Supports setting multiple attributes at once.

For example:

CREATE USER 'alice'@'%' PROPERTIES ('session.query_delivery_timeout' = '600');

ALTER USER jack SET PROPERTIES ('catalog' = 'default_catalog', 'database' = 'test_db2', 'session.query_delivery_timeout' = '500');

mysql> show properties;
+--------------------------+-----------------+
| Key                      | Value           |
+--------------------------+-----------------+
| max_user_connections     | 1024            |
| catalog                  | mycatalog       |
| database                 | test_db         |
| query_delivery_timeout   | 600             |
+--------------------------+-----------------+

The implementation details to consider

When setting up user's property:

1. When setting up a catalog and database, we need to pay attention to user permissions, as well as whether the catalog and database exist.
2. When setting a session, we need to consider whether the session variable exists, whether it is a global session variable, whether the session variable supports modification, and the type of the session variable.

When the user connects to FE SQL:

1. Even if the setting fails, it should not affect the normal operation of the SQL Client. For example, after setting the Database and Catalog, these values may also become invalid over time.

When Replaying Journal:

1. Even if the setup fails during the Replay, it should not affect the normal progress of the Replay.

[alvin-celerdata]

@yandongxiao thanks for the contribution. I wonder whether or not we only support alter use "xxx" set property statement. And we don't support set property anymore. This will make people can do this only in one way. We'd better to not provide many ways to finish one thing.

[yandongxiao]

Currently, we have reused SHOW PROPERTY to allow users to obtain a list of all properties. If users are aware of the SET PROPERTY syntax, they will naturally attempt to use SET PROPERTY to set user property information.

In the user documentation, we will only write the syntax of ALTER USER xxx SET PROPERTIES, guiding users to use the new syntax. I think it might be a good choice to remove the SET PROPERTY syntax (and perhaps also SHOW PROPERTY) in a future version.

[yandongxiao]

@yandongxiao thanks for the contribution. I wonder whether or not we only support alter use "xxx" set property statement. And we don't support set property anymore. This will make people can do this only in one way. We'd better to not provide many ways to finish one thing.

Done

[alvin-celerdata]

@yandongxiao
Besides SHOW PROPERTIES, is there other command to show user's properties?

[yandongxiao]

@yandongxiao Besides SHOW PROPERTIES, is there other command to show user's properties?

I did not find any other commands.

[vitalyDE]

Hi @yandongxiao, I set the USER PROPERTIES and was expecting to see the changes reflected when running SHOW VARIABLES. Could you please confirm if this is the correct way to verify the changes, or if I should be using a different method?

For example:

-- check var
mysql> SHOW VARIABLES LIKE '%query_mem_limit%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| query_mem_limit          | 15000000000     |
+--------------------------+-----------------+

-- set user properties
ALTER USER jack SET PROPERTIES ('session.query_mem_limit' = '25000000000');

-- open new session
mysql> SHOW VARIABLES LIKE '%query_mem_limit%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| query_mem_limit          | 25000000000     |
+--------------------------+-----------------+

[yandongxiao]

‌‌‌‌Your usage method is correct. For specific usage instructions, you can refer to this document: https://docs.starrocks.io/docs/sql-reference/sql-statements/account-management/ALTER_USER/