Security - MySQL - page 788

not available (the server is older than MySQL 5.6.7) or the account is for an
anonymous user (who cannot set the account password to unexpire it). For more
information about password expiration, see Recipe 23.5 .
To use the procedure, invoke it like this:
CALL create_user ( ' user_name ',' host_name ',' password ',' auth_plugin ' );
The procedure definition is shown following. It requires the helper routines ex
ec_stmt() and server_version() from Recipes 9.9 and 10.9 . Scripts to create these
routines are located in the routines directory of the recipes distribution:
CREATE PROCEDURE create_user ( user TEXT , host TEXT ,
password TEXT , plugin TEXT )
BEGIN
DECLARE account TEXT ;
SET account = CONCAT ( QUOTE ( user ), '@' , QUOTE ( host ));
CALL exec_stmt ( CONCAT ( 'CREATE USER ' , account ,
' IDENTIFIED WITH ' , QUOTE ( plugin )));
IF password IS NOT NULL THEN
BEGIN
DECLARE saved_old_passwords INT ;
SET saved_old_passwords = @@ old_passwords ;
CASE plugin
WHEN 'mysql_native_password' THEN SET old_passwords = 0 ;
WHEN 'mysql_old_password' THEN SET old_passwords = 1 ;
WHEN 'sha256_password' THEN SET old_passwords = 2 ;
ELSE SIGNAL SQLSTATE 'HY000'
SET MYSQL_ERRNO = 1525 ,
MESSAGE_TEXT = 'unhandled auth plugin' ;
END CASE ;
CALL exec_stmt ( CONCAT ( 'SET PASSWORD FOR ' , account ,
' = PASSWORD(' , QUOTE ( PASSWORD ), ')' ));
SET old_passwords = saved_old_passwords ;
END ;
END IF ;
IF server_version () >= 50607 AND user <> '' THEN
CALL exec_stmt ( CONCAT ( 'ALTER USER ' , account , ' PASSWORD EXPIRE' ));
END IF ;
END ;
Assigning and checking privileges
Suppose that you have just created an account named 'user1'@'localhost' . You can
assign privileges to it with GRANT , remove privileges from it with REVOKE , and check its
privileges with SHOW GRANTS .
GRANT has this syntax:
GRANT privileges ON scope TO account ;