![]() ![]() Notice how I had to specify USAGE above, even though it had nothing to do with what I was actually trying to do (modify SSL requirement). Use ALTER USER statement for this operation. Here’s the warning text: Using GRANT statement to modify existing user’s properties other than privileges is deprecated and will be removed in future release. Query OK, 0 rows affected, 1 warning (0.07 sec) For backwards compatibility, such commands are still supported, but deprecated: No longer do users have to resort to GRANT commands to modify account attributes, such as SSL. The new syntax allows for a clean delineation between commands which modify account attributes and those which modify privileges. Separating account and privilege attributes SET authentication_string = 'mysql, dba=root, users=data_entry, finance=accounting'Īs of 5.7.6, this can be done with the ALTER USER command, instead: ALTER USER BY 'mysql, dba=root, users=data_entry, finance=accounting' In 5.6, this requires an update of er, and FLUSH PRIVILEGES: UPDATE er TO you need to update the mapping for the anonymous user. You can create the additional user account: CREATE USER LOCK TO PROXY ON PROXY ON you want to add another group of users with different privileges. As an example, let’s say you are using the PAM authentication plugin, and you’ve created accounts as follows to support this: CREATE USER WITH authentication_pam Updating proxy user mappingĪnother use case which required direct updates of the er table in the past is modification of the proxy user mapping for authentication plugins like PAM or Windows Native authentication. Mysql> ALTER USER IDENTIFIED WITH sha256_passwordįor users who have deployed the mysql_no_login plugin to lock down system accounts, that also means you can convert to the new “locked account” introduced in MySQL Server 5.7.7, without affecting users or directly modifying er. The new ALTER USER syntax supports this use case: mysql> CREATE USER IDENTIFIED WITH mysql_native_password In earlier versions, there was no way to change the authentication plugin for an existing user account without directly manipulating the er table (discouraged). Here’s a couple of important use cases the new syntax covers: Changing authentication plugin These attributes, and the syntax by which they are modified, is now consistent across CREATE USER and ALTER USER commands. Password/credentials or auth plugin mapping.These are consistent with CREATE USER – the same attributes which can be defined with a CREATE USER command can now be modified using an ALTER USER command. ![]() This all changes for the better in MySQL Server 5.7 – here’s how: Account attributes ![]() MySQL has a long history of confusing these – for example, requiring a GRANT command to set account resource limits or require SSL. With changes made in MySQL Server 5.7.6, a better distinction is made between privilege-level attributes (those which are managed via GRANT and REVOKE statements) and account-level attributes (those managed using CREATE USER and ALTER USER statements). Before MySQL Server 5.7.6, ALTER USER could only be used to expire a user’s password. Complimenting the expanded CREATE USER syntax introduced in MySQL Server 5.7.6 is more useful ALTER USER syntax.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |