duplicate_grantee

NAME

duplicate_grantee(): Create new account (grantee), identical to given account

TYPE

Procedure

DESCRIPTION

Given an existing GRANTEE anda new one, duplicate existing GRANTEE, along with all applied set of privileges and password, to new account, creating the new account if necessary.

This is essentially a Copy+Paste of an account.

The new account is generated via GRANT commands. For clarification, there is no direct tampering with the mysql system tables (no DML used).

SYNOPSIS

duplicate_grantee(
    IN existing_grantee TINYTEXT CHARSET utf8,
    IN new_grantee TINYTEXT CHARSET utf8
  ) 
  MODIFIES SQL DATA

Input:

  • existing_grantee: an existing account/GRANTEE name. An error is thrown when no such account is found.
  • new_grantee: name for new account.
    The new account is created, if not existing.
    In case this account already exists, it is added the set of privileges applying to existing_grantee, and its password is updated.
Both existing_grantee and new_grantee can be provided in relaxed format: 'web_user@10.0.0.%' is a valid input, and is implicitly translated to "'web_user'@'10.0.0.%'", which is the fully qualified account name.

EXAMPLES

Duplicate an account, creating a new GRANTEE. Verify operation's result: 

mysql> SELECT * FROM similar_grants WHERE sample_grantee like '%apps%';
+----------------+----------------+------------------+
| sample_grantee | count_grantees | similar_grantees |
+----------------+----------------+------------------+
| 'apps'@'%'     |              1 | 'apps'@'%'       |
+----------------+----------------+------------------+

mysql> call duplicate_grantee('apps@%', 'apps@myhost');
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM similar_grants WHERE sample_grantee like '%apps%';
+----------------+----------------+----------------------------+
| sample_grantee | count_grantees | similar_grantees           |
+----------------+----------------+----------------------------+
| 'apps'@'%'     |              2 | 'apps'@'%','apps'@'myhost' |
+----------------+----------------+----------------------------+

mysql> SHOW GRANTS FOR 'apps'@'%';
+---------------------------------------------------------------------------------------------------------------------+
| Grants for apps@%                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'apps'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'apps'@'%'                                                      |
| GRANT SELECT (title, description, film_id), UPDATE (description) ON `sakila`.`film` TO 'apps'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'apps'@'myhost';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for apps@myhost                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'apps'@'myhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'apps'@'myhost'                                                      |
| GRANT SELECT (title, description, film_id), UPDATE (description) ON `sakila`.`film` TO 'apps'@'myhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------+

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

match_grantee(), mysql_grantee(), similar_grants, sql_accounts

AUTHOR

Shlomi Noach
 
common_schema documentation