[MDEV-9786] Create user with require ssl throws syntax error Created: 2016-03-24  Updated: 2016-03-29  Resolved: 2016-03-24

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.1.10
Fix Version/s: 10.2.0

Type: Bug Priority: Major
Reporter: Jonas Krauss Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu Server 12.04 LTS


 Description   

On execution of this simple user creation statement

CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'blablabla' REQUIRE SSL;

this error message is returned:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REQUIRE SSL' at line 1

The same error message appears if the user is first created without the require constraint but then altered later like

ALTER USER 'repl'@'%.mydomain.com' REQUIRE SSL;

Thanks for any advice in advance.

 Comments   
Comment by Sergei Golubchik [ 2016-03-24 ]

ALTER USER statement doesn't exists in 10.1, so the error you should've had in the second case must be "... syntax to use near USER", not "near REQUIRE".

As for your first case, as far as I can see it should work. We have something similar in our test case:

create user foo identified by 'password' require SSL;

and it is successfully run in our tests many times every day.

There must be something special in your case that made it fail. Do you have any idea what that could possibly be?

Comment by Jonas Krauss [ 2016-03-24 ]

Thanks for the quick reply. You are right on the ALTER USER statement, I was not aware this does not exist anymore in 10.1.

I do not know of any special circumstances of our setup. Actually we have four MariaDB servers running on different machines, all on 10.1.8 and up. I just tried the same query on the other machines as well and it does not go through.

I also tried your test query, same return:

MariaDB [(none)]> create user foo identified by 'password' require SSL;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'require SSL' at line 1

The most recent installation was setup one week ago on Ubuntu Server 14 LTS, here is the version string: Server version: 10.1.12-MariaDB-1~trusty - mariadb.org binary distribution
MariaDB was installed through apt-get and the official way as described in the knowledge base.

Here is SHOW CREATE TABLE mysql.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'

Let me know if I can pull more helpful information.

Comment by Sergei Golubchik [ 2016-03-24 ]

Sorry, that was my fault. Both CREATE USER ... REQUIRE SSL and ALTER USER were added only in 10.2, 10.1 does not support either command.

In 10.1 you should use GRANT ... REQUIRE SSL statement. This is documented here:

Comment by Jonas Krauss [ 2016-03-29 ]

Thanks a lot Sergei, I should read the documentation more careful next time.

In any case: for mysql they maintain a reference by version - I think this is a little easier than the current system used in the MariaDB knowledge base, which states the version in bold over each statement box (that is what I overlooked). Just an idea, I know resources are always limited and there are more important tasks. Keep up the great work!

Comment by Sergei Golubchik [ 2016-03-29 ]

Just a tip: in the knowledge base on the right panel, there often is a Product Versions block with checkboxes. See for example CREATE USER page. You check versions you're interested in and unsupported parts of the manual will be grayed out.

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