MySQL przyznaje wszystkie uprawnienia do bazy danych z wyjątkiem jednej tabeli

REVOKE ALL PRIVILEGES ON db.* FROM user@localhost;

SELECT CONCAT("GRANT UPDATE ON db.", table_name, " TO user@localhost;")

FROM information_schema.TABLES

WHERE table_schema = "YourDB" AND table_name <> "table_to_skip";

• Pobierz listę wszystkich tabel w bazie danych (

  SHOW TABLES;

  )
• Przyznaj wszystkie uprawnienia dla każdej pozycji na liście
• Odwołaj uprawnienia do tabeli niestandardowej

DELIMITER $$DROP PROCEDURE IF EXISTS refreshRoles $$

CREATE PROCEDURE refreshRoles ()

 COMMENT 'Grant SELECT on new databases/tables, revoke on deleted'

BEGIN

 DECLARE done BOOL;

 DECLARE db VARCHAR(128);

 DECLARE tb VARCHAR(128);

 DECLARE rl VARCHAR(128);

 DECLARE tables CURSOR FOR

 SELECT table_schema, table_name, '_bob_live_sg' FROM information_schema.tables

 WHERE table_schema LIKE '%bob\_live\_sg' AND

 ( false

 OR table_name LIKE 'bundle%'

 OR table_name LIKE 'cart%'

 OR table_name LIKE 'catalog%'

 OR table_name LIKE 'url%'

 ); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true; CREATE ROLE IF NOT EXISTS '_bob_live_sg';

 REVOKE ALL, GRANT OPTION FROM '_bob_live_sg'; OPEN tables;

 SET done = false;

 grant_loop: LOOP

 FETCH tables INTO db, tb, rl;

 IF done THEN

 LEAVE grant_loop;

 END IF;

 SET @g = CONCAT('GRANT SELECT ON `', db, '`.`', tb, '` TO ', rl);

 PREPARE g FROM @g;

 EXECUTE g;

 DEALLOCATE PREPARE g;

 END LOOP;

 CLOSE tables;

END $$DELIMITER ;CALL refreshRoles;

#!/bin/bash# Define the database and root authorization details

db_host='localhost'

db_name='adhoctuts'

db_user='root'

db_pass='Adhoctuts2018#'# Define the query to get the needed tables

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)# Convert the query result into the array

table_arr=(${table_list//,/ })# Declare the associative array of the users as username=>password pair

# e.g: declare -A user_list=(["'user1'"]="pass1" ["'user2'"]="pass2")

# In our case there is a single user

declare -A user_list=(["'aht_r'@'localhost'"]="Adhoctuts2018#")

for user in "${!user_list[@]}"

do

 pass=${user_list[$user]}

 # Recreate user

 mysql -h $db_host -u $db_user -p"$db_pass" -se "drop user if exists $user; create user $user identified by '$pass';" # Provide SELECT privilege

 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

@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% > tblssetlocal 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