[MDEV-10959] MariaDB does not support password expiration, but mysql.user table has password_expired column Created: 2016-10-05  Updated: 2016-10-06  Resolved: 2016-10-06

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.17
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

MariaDB will not support password expiration until MDEV-7597 is implemented. Despite that, the mysql.user table in MariaDB 10.1 has the password_expired column from MySQL 5.6.

Is this a bug, or is it intentional?

Below you can see the results of initializing a new MariaDB 10.1 datadir and inspecting the definition of the mysql.user table:

$ sudo mysql_install_db --user=mysql --datadir=/tmp/mysql_datadir
 
Installing MariaDB/MySQL system tables in '/tmp/mysql_datadir' ...
 
2016-10-05 16:59:20 140731367938176 [Note] /usr/sbin/mysqld (mysqld 10.1.17-MariaDB) starting as process 2039 ...
 
OK
 
Filling help tables...
 
2016-10-05 16:59:25 140090919553152 [Note] /usr/sbin/mysqld (mysqld 10.1.17-MariaDB) starting as process 2069 ...
 
OK
 
Creating OpenGIS required SP-s...
 
2016-10-05 16:59:27 139900032452736 [Note] /usr/sbin/mysqld (mysqld 10.1.17-MariaDB) starting as process 2100 ...
 
OK
 
 
 
To start mysqld at boot time you have to copy
 
support-files/mysql.server to the right place for your system
 
 
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
 
To do so, start the server, then issue the following commands:
 
 
 
'/usr/bin/mysqladmin' -u root password 'new-password'
 
'/usr/bin/mysqladmin' -u root -h ip-172-31-19-192.us-west-2.compute.internal password 'new-password'
 
 
 
Alternatively you can run:
 
'/usr/bin/mysql_secure_installation'
 
 
 
which will also give you the option of removing the test
 
databases and anonymous user created by default.  This is
 
strongly recommended for production servers.
 
 
 
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
 
MySQL manual for more instructions.
 
 
 
You can start the MariaDB daemon with:
 
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/tmp/mysql_datadir'
 
 
 
You can test the MariaDB daemon with mysql-test-run.pl
 
cd '/usr/mysql-test' ; perl mysql-test-run.pl
 
 
 
Please report any problems at http://mariadb.org/jira
 
 
 
The latest information about MariaDB is available at http://mariadb.org/.
 
You can find additional information about the MySQL part at:
 
http://dev.mysql.com
 
Support MariaDB development by buying support/new features from MariaDB
 
Corporation Ab. You can contact us about this at sales@mariadb.com.
 
Alternatively consider joining our community based development effort:
 
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
 
 
 
$ sudo mysqld --user=mysql --datadir=/tmp/mysql_datadir &
 
[1] 2136
 
2016-10-05 17:00:13 140427088873600 [Note] mysqld (mysqld 10.1.17-MariaDB) starting as process 2137 ...
 
$ mysql -u root
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 10.1.17-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> SHOW CREATE TABLE mysql.user;
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| user  | CREATE TABLE `user` (
 
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
 
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
 
  `ssl_cipher` blob NOT NULL,
 
  `x509_issuer` blob NOT NULL,
 
  `x509_subject` blob NOT NULL,
 
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
 
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
 
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
 
  `max_user_connections` int(11) NOT NULL DEFAULT '0',
 
  `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
 
  `authentication_string` text COLLATE utf8_bin NOT NULL,
 
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `max_statement_time` decimal(12,6) NOT NULL DEFAULT '0.000000',
 
  PRIMARY KEY (`Host`,`User`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2016-10-06 ]

It was certainly intentional, done as a part of the merge here:
https://github.com/MariaDB/server/commit/8075b05b7dc03f67abc7c7a3d788dcf87b3483c9

Does it cause any problems? Otherwise, I'd think it makes more sense for migrating from MySQL 5.6 to MariaDB 10 to keep the columns that are already there rather than remove them.

Comment by Geoff Montee (Inactive) [ 2016-10-06 ]

I don't think having this column causes problems. I just submitted this issue because the presence of this column confused at least one user who thought that MariaDB had some sort of password expiration feature, and since it doesn't, I wasn't sure why we had this column. But I see that the documentation already has a pretty good note about why this column is there:

MySQL-compatibility option, not implemented in MariaDB.

https://mariadb.com/kb/en/mariadb/mysqluser-table/

So that's probably good enough.

Generated at Thu Feb 08 07:46:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.