Hi, Oleksandr!
On Apr 20, Oleksandr Byelkin wrote:
> revision-id: 784cc5970dd (mariadb-10.4.11-68-g784cc5970dd)
> parent(s): c5e00fea102
> author: Oleksandr Byelkin <sa...@mariadb.com>
> committer: Oleksandr Byelkin <sa...@mariadb.com>
> timestamp: 2020-02-20 14:06:09 +0100
> message:
>
> MDEV-19650: Privilege bug on MariaDB 10.4
>
> Also fixes:
> MDEV-21487: Implement option for mysql_upgrade that allows root@localhost to
> be replaced
> MDEV-21486: Implement option for mysql_install_db that allows root@localhost
> to be replaced
>
> Add user mariadb.sys to be definer of user view
> (and has right on underlying table global_priv for
> required operation over global_priv
> (SELECT,UPDATE,DELETE,INSERT,FILE))
>
> Also changed definer of gis functions in case of creation,
> but they work with any definer so upgrade script do not try
> to push this change.
>
> diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
> index 29f2a4c1ef6..af852444d0c 100644
> --- a/scripts/mysql_system_tables.sql
> +++ b/scripts/mysql_system_tables.sql
> @@ -33,9 +33,17 @@ CREATE TABLE IF NOT EXISTS db ( Host char(60) binary
> DEFAULT '' NOT NULL, Db c
> -- Remember for later if db table already existed
> set @had_db_table= @@warning_count != 0;
>
> -CREATE TABLE IF NOT EXISTS global_priv (Host char(60) binary DEFAULT '',
> User char(80) binary DEFAULT '', Priv JSON NOT NULL DEFAULT '{}'
> CHECK(JSON_VALID(Priv)), PRIMARY KEY Host (Host,User)) engine=Aria
> transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global
> privileges';
> +CREATE TABLE IF NOT EXISTS global_priv (Host char(60) binary DEFAULT '',
> User char(80) binary DEFAULT '', Priv JSON NOT NULL DEFAULT '{}'
> CHECK(JSON_VALID(Priv)), PRIMARY KEY (Host,User)) engine=Aria transactional=1
> CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges';
>
> -CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW IF NOT EXISTS user
> AS SELECT
> +set @had_sys_user= @@warning_count != 0 OR 0 <> (select count(*) from
> mysql.global_priv where Host="localhost" and User="mariadb.sys");
> +
> +CREATE TEMPORARY TABLE tmp_user_sys LIKE global_priv;
> +INSERT INTO tmp_user_sys (Host,User,Priv) VALUES
> ('localhost','mariadb.sys','{"access":512,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0}');
> +INSERT INTO global_priv SELECT * FROM tmp_user_sys WHERE NOT @had_sys_user;
> +DROP TABLE tmp_user_sys;
1. This could've been simply INSERT IGNORE, I suspect
2. why access:512 ? It's FILE_ACL, iirc.
> +
> +CREATE DEFINER='mariadb.sys'@'localhost' SQL SECURITY DEFINER VIEW IF NOT
> EXISTS user AS SELECT
> Host,
> User,
> IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password',
> 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'),
> ''), '') AS Password,
> @@ -101,6 +109,11 @@ CREATE TABLE IF NOT EXISTS servers ( Server_name
> char(64) NOT NULL DEFAULT '', H
>
> CREATE TABLE IF NOT EXISTS tables_priv ( Host char(60) binary DEFAULT '' NOT
> NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary DEFAULT ''
> NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL, Grantor char(141)
> DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
> ON UPDATE CURRENT_TIMESTAMP, Table_priv
> set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create
> View','Show view','Trigger','Delete versioning rows') COLLATE
> utf8_general_ci DEFAULT '' NOT NULL, Column_priv
> set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT
> '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name), KEY Grantor (Grantor) )
> engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin
> comment='Table privileges';
>
> +CREATE TEMPORARY TABLE tmp_user_sys LIKE tables_priv;
> +INSERT INTO tmp_user_sys
> (Host,Db,User,Table_name,Grantor,Timestamp,Table_priv) VALUES
> ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0','Select,Insert,Update,Delete');
why Insert,Update,Delete ?
> +INSERT INTO tables_priv SELECT * FROM tmp_user_sys WHERE NOT @had_sys_user;
> +DROP TABLE tmp_user_sys;
> +
> CREATE TABLE IF NOT EXISTS columns_priv ( Host char(60) binary DEFAULT ''
> NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(80) binary
> DEFAULT '' NOT NULL, Table_name char(64) binary DEFAULT '' NOT NULL,
> Column_name char(64) binary DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL
> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Column_priv
> set('Select','Insert','Update','References') COLLATE utf8_general_ci DEFAULT
> '' NOT NULL, PRIMARY KEY (Host,Db,User,Table_name,Column_name) ) engine=Aria
> transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Column
> privileges';
>
Regards,
Sergei
VP of MariaDB Server Engineering
and secur...@mariadb.org
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
