Export and import MySQL users and privileges to Azure Database for MySQL
In Azure Database for MySQL, “mysql.user” table is a read-only table, to migrate your mysql.user table you can use the following command to script out the users table content before migration: Generate create user statements: Before going to generate the scripts , we need to check the variable secure_file_priv on your database which limit the directories where you can load or writing files to : mysql> SHOW VARIABLES LIKE "secure_file_priv"; +------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ After checking the path, now you can generate the create users from your database: Option 1: mysql> SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','YourPassword''',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile '/var/lib/mysql-files/create_users.sql'; The generated file contents will look like: create user 'myuser1'@'%' identified by 'YourPassword'; create user 'myuser2'@'%' identified by 'YourPassword'; create user 'myuser3'@'%' identified by 'YourPassword'; create user 'myuser4'@'%' identified by 'YourPassword'; create user 'replication_user'@'%' identified by 'YourPassword'; modify the password above based on your requirements and run the above generated create user statements on Azure database for MySQL. option 2: if you are not aware of the passwords in your database and you want to let the password change on the client side , you can generate a user creation script with temporary password and expired option: SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','Temp@123''', ' password expire',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile '/var/lib/mysql-files/create_users.sql'; The generated file contents will look like: create user 'myuser1'@'%' identified by 'Temp@123' password expire; create user 'myuser2'@'%' identified by 'Temp@123' password expire; create user 'myuser3'@'%' identified by 'Temp@123' password expire; create user 'myuser4'@'%' identified by 'Temp@123' password expire; create user 'replication_user'@'%' identified by 'Temp@123' password expire; run the above generated create user statements on Azure Database for MySQL. after the user logged in to the database with that temp password , and tried to run any query the user will be notified that he must change the password before running any query , and the user can use “set password = ‘newpassword’ ” statement to reset the password: mysql> select 1; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> mysql> set password = 'Mysql@1234'; Query OK, 0 rows affected (0.01 sec) mysql> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) Export users’ privileges: To export the user privileges, you can run the below query: SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session') INTO outfile '/var/lib/mysql-files/user_grants.sql'; The generated file will look like: SHOW GRANTS FOR 'myuser1'@'%'; SHOW GRANTS FOR 'myuser2'@'%'; SHOW GRANTS FOR 'myuser3'@'%'; SHOW GRANTS FOR 'myuser4'@'%'; SHOW GRANTS FOR 'myuser5'@'%'; SHOW GRANTS FOR 'replication_user'@'%'; You can source the generated file to get the required commands , before sourcing the file to generate a ready contents of command lines , connect to your local database with options -Ns to remove the headers from the output: mysql -Ns -h root -p mysql> source /var/lib/mysql-files/user_grants.sql GRANT USAGE ON *.* TO 'myuser1'@'%' GRANT USAGE ON *.* TO 'myuser2'@'%' GRANT USAGE ON *.* TO 'myuser3'@'%' GRANT USAGE ON *.* TO 'myuser4'@'%' GRANT USAGE ON *.* TO 'myuser5'@'%' GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%' Connect to your Azure database for MySQL and run the above generated commands: mysql -h ServerName.mysql.database.azure.com -u username@servername -p mysql> GRANT USAGE ON *.* TO 'myuser1'@'%'; GRANT USAGE ON *.* TO 'myuser3'@'%'; GRANT USAGE ON *.* TO 'myuser4'@'%'; GRANT USAGE ON *.* TO 'myuser5'@'%'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%';Query OK, 0 rows affected (0.03 sec) mysql> GRANT USAGE ON *.* TO 'myuser2'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT USAGE ON *.* TO 'myuser3'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT USAGE ON *.* TO 'myuser4'@'%'; Query OK, 0 rows affected (0.04 sec) mysql> GRANT USAGE ON *.* TO 'myuser5'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication_user'@'%'; Query OK, 0 rows affected (0.02 sec) Please note that select into outfile is not supported in Azure Database for MySQL. Instead, use the Create users and Create privilege queries below and run them from workbench and copy the outputs: Example: Create users: SELECT CONCAT('create user ''',user,'''@''',host,''' identified by ''','YourPassword''',';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session'); Create Privileges: SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user where user not in ('root','mysql.sys','mysql.session'); And run the generated commands: SHOW GRANTS FOR 'myuser1'@'%'; SHOW GRANTS FOR 'myuser2'@'%'; SHOW GRANTS FOR 'myuser3'@'%'; SHOW GRANTS FOR 'myuser4'@'%'; SHOW GRANTS FOR 'myuser5'@'%'; SHOW GRANTS FOR 'replication_user'@'%'; Please visit the Performance considerations guide for best practices while migrating into Azure Database for MySQL. Thank You !Office of Management and Budget (OMB) Uniform Guidance re-write: What you need to know
The federal Office of Management and Budget (OMB) recently released a major rewrite to the Uniform Guidance, the common rules governing most federal grantmaking to charitable nonprofits, and others, effective on Oct. 1, 2024: The Biden-Harris Administration Finalizes Guidance to Make Grants More Accessible and Transparent for Families, Communities, and Small Businesses | OMB | The White House The rewrite addresses longstanding problems in covering nonprofits' actual costs, advances equity by making grants accessible to more nonprofits, and makes other significant reforms that will reduce bureaucratic barriers and costs of seeking, performing, and reporting on grants using federal funds. The National Council of Nonprofits are hosting a special, nationwide, free webinar, OMB Uniform Guidance: What the Updates Mean for Nonprofits, on Thursday, May 30 th at 3:30 – 4:30pm ET to ensure charitable organizations understand the significant improvements to the Uniform Guidance and what the changes mean for their missions. Register here: https://www.councilofnonprofits.org/form/omb-uniform-guidance-webinar
