Unfortunately, there is built-in natural ways in MySQL to perform selective/exceptional tasks.
You could use below script(linux console bash script)
#!/bin/bash
db_host='localhost'
db_name='adhoctuts'
db_user='root'
db_pass='Adhoctuts2018#'
table_list=$(mysql -h $db_host -u $db_user -p"$db_pass" -se "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema='$db_name' and table_name not like 'tbl1' AND table_name not like '\_\_%';" $db_name | cut -f1)
table_arr=(${table_list//,/ })
declare -A user_list=(["'aht_r'@'localhost'"]="Adhoctuts2018#")
for user in "${!user_list[@]}"
do
pass=${user_list[$user]}
mysql -h $db_host -u $db_user -p"$db_pass" -se "drop user if exists $user; create user $user identified by '$pass';"
mysql -h $db_host -u $db_user -p"$db_pass" -se "revoke all privileges, grant option from $user;" $db_name
mysql -h $db_host -u $db_user -p"$db_pass" -se "grant usage on $db_name.* TO $user;" $db_name
for tbl in "${table_arr[@]}"; do
echo "grant select on $tbl TO $user"
mysql -h $db_host -u $db_user -p"$db_pass" -se "grant select on $tbl TO $user;" $db_name
done
done
If you have a windows console you could use the following .bat file:
@ECHO OFF
%= Define the database and root authorization details =%
set db_host=192.168.70.138
set db_name=adhoctuts
set db_user=adhoctuts
set db_pass=Adhoctuts2018
mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema='%db_name%' and table_name not like 'tbl1' AND table_name not like '\_\_%%';" %db_name% > tbls
setlocal EnableDelayedExpansion
set user_cnt=2
set user[1]='Adhoctuts1'@'192.168.%%.%%'
set pass[1]=Adhoctuts1_2018#
set user[2]='Adhoctuts2'@'192.168.%%.%%'
set pass[2]=Adhoctuts2_2018#
set i=1
:loop
set user=!user[%i%]!
set pass=!pass[%i%]!
mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "drop user if exists %user% ; create user %user% identified by '%pass%';"
mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "revoke all privileges, grant option from %user%;" %db_name%
for /F "usebackq delims=" %%a in ("tbls") do (
mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "grant select on %%a TO %user%;" %db_name%
)
if %i% equ %user_cnt% goto :end_loop
set /a i=%i%+1
goto loop
:end_loop
del /f tbls
First you write the query to get the list of the needed tables, next you define the list of users you want to grant access for. You need to execute the script every time the database structure changes. I have created separate short tutorial for MySQL selective/exceptional tasks.
https://adhoctuts.com/mysql-selective-exceptional-permissions-and-backup-restore/
https://youtu.be/8fWQbtIISdc