[MDEV-22914] Difference of MySQL 8.0 syntax from MariaDB Created: 2020-06-16  Updated: 2020-06-24

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Artyom Assignee: Kaj Arnö
Resolution: Unresolved Votes: 0
Labels: None


 Description   

1. MySQL 8.0 supports a different compression algorithm for each table (table option COMPRESSION). MariaDB supports defining one global algorithm for all tables. Perhaps, while MariaDB does not have the ability to set different algorithms, it is possible to make, like syntactic sugar, the possibility of using the COMPRESSION table option if the algorithm specified in it matches the global one, otherwise give an error or warning.
2. In MariaDB, the CREATE DATABASE does not have the encryption option, but the encryption option is available for individual tables. It might make sense to add this option, as in MYSQL 8.0.
3. MariaDB lacks the

{OJ ...}

construct for compatibility with ODBC. This helps with compatibility with some third-party applications, but is not official ODBC syntax.
4. MySQL 8.0 supports options for Password Reuse Policies, Password-requiring Policies, Double Password Support (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/password-management.html#password-reuse-policy). Some time ago, a movement was being considered towards supporting these options (https://jira.mariadb.org/plugins/servlet/mobile#issue/MDEV-9245).
5. You were opposed to supporting the ALL keyword in SET ROLE, as this is not in the standard (see comments
https://mariadb.com/kb/en/set-default-role/#comment_2830). Is it right now?
 6. In MySQL 8.0, when creating a user, you can specify a comment and attributes. This is a regular JSON column in the user table, maybe it makes sense to add support for this in MariaDB

 Comments   
Comment by Alexander Krizhanovsky (Inactive) [ 2020-06-18 ]

https://jira.mariadb.org/browse/MDEV-11180 was closed as invalid, but could make sense to make MariaDB syntax compatible with MySQL.

Comment by Vicențiu Ciorbaru [ 2020-06-18 ]

For SET ROLE ALL, MariaDB's implementation was specifically designed to only enable one role at a time. I believe this will require a few "hacks" to get it to work, hacks that we might not want to maintain.

Potential implementation ideas:
1. Check thd->priv_role == "ALL", and then do privilege checking in sequence for all roles, until access is granted or until all roles are exhausted. A bit of a performance bottleneck.
2. Create a "pseudo" ALL role, that has all roles that the user has available granted it to it. Reuse the privilege caching mechanism that is normally used for roles on this one as well. When role "ALL" is enabled, only do privilege checks through this pseudo-role. This has little performance impact, but implies a bit of changes in logic.

If we support the "ALL" role, it might be expected to support a subset of roles enabled at any one time. This can be done either via method 1 or method 2.

Option 3.
Do a dynamic cache of grants, similar to how query_cache works. Store privileges for a combination of assigned roles in a hash-table. Query that one first, if we get a HIT, we fetch immediately, otherwise a MISS inserts the combination into the hash-table.

Comment by Kaj Arnö [ 2020-06-18 ]

1. Depends on outcome of GSoC code submission. Please wait.
2. Most implementable one. We will evaluate internally.
3. Please clarify! Is this in MySQL 8? MySQL 5.7? Is there a clear definition for it somewhere?
4. Fairly implementable. We will evaluate internally.
5. No. Not compatible with our ROLEs, which can be contradictory.
6. Fairly implementable! We will evaluate internally.

Comment by Artyom [ 2020-06-18 ]

> 3. Please clarify! Is this in MySQL 8? MySQL 5.7? Is there a clear definition for it somewhere?

I mean OJ from JOIN Clause (5.7 and 8.0)
https://dev.mysql.com/doc/refman/5.7/en/join.html
https://dev.mysql.com/doc/refman/8.0/en/join.html

Comment by Sergei Golubchik [ 2020-06-24 ]

3. is supported since at least 2007 (MySQL-5.1?) and it was always supported in MariaDB since the first day it was branched off MySQL
5. Having many active roles is very much against the standard. It is essential that the CURRENT_ROLE returns only one role name because a CURRENT_ROLE can be a grantor or a definer. For example, one cannot have a stored procedure where a definer is a list of roles.

Generated at Thu Feb 08 09:18:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.