usuarios - tipos de permisos en mysql

MySQL otorga todos los privilegios a la base de datos excepto una tabla

Question

No he podido encontrar una solución razonable para lograr lo siguiente:

Deseo tener un usuario que tenga TODOS los privilegios en una base de datos (o una serie de bases de datos con el mismo esquema), excepto en una tabla, a la que solo tendrán privilegios de SELECCIONAR.

Esencialmente quiero que el usuario tenga libertad de reinado sobre una base de datos pero no poder actualizar una tabla específica.

Hasta ahora he intentado, en vano:

• Otorgar todos los privilegios en esa base de datos (db_name. *) Y luego otorgar específicamente solo los privilegios de selección en la tabla deseada (con la esperanza de que sobreescriba el "todo", lo sé, estúpido).

• Otorgar todos los privilegios en esa base de datos (db_name. *) Y luego revocar insertar, actualizar y eliminar. Pero esto produjo un error que decía que no había una regla de concesión para db_name.table_name.

De lo que he podido reunir tendré que otorgar individualmente todos los privilegios en cada tabla de la base de datos, excepto la tabla de solo lectura.

Por favor alguien dime que hay una manera más fácil.

Nota : estoy ejecutando MySQL 5.1. El último disponible en Ubuntu 10.04.

Answer 1

AFAIK, sí, debe otorgarse individualmente por tabla. Pero oye, tienes una computadora allí. Las computadoras son excelentes para automatizar las tareas repetitivas para usted, así que, ¿por qué no hace un script que haga lo siguiente:

1. Obtenga una lista de todas las tablas en la base de datos ( SHOW TABLES; )
2. Para cada elemento en la lista, conceda todos los permisos
3. Revocar permisos en la mesa especial.

O, alternativamente: 2. Para cada elemento de la lista, compruebe si es la tabla especial; Si no lo es, concede todos los permisos.

La razón por la que no estoy dando el código es que se puede hacer en cualquier lenguaje de scripting con las instalaciones de MySQL, incluso shell script; Usa lo que te resulte más cómodo.

Answer 2

Aquí hay un borrador de lo que uso para otorgar roles en MariaDB. Tal vez establecer un EVENTO lo haría más genial :-)

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;

Answer 3

Desafortunadamente, hay formas naturales incorporadas en MySQL para realizar tareas selectivas / excepcionales.

Podrías usar el siguiente script (linux console bash script)

#!/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

Si tiene una consola de Windows, puede usar el siguiente archivo .bat:

@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

Primero escribe la consulta para obtener la lista de las tablas necesarias y, a continuación, define la lista de usuarios a los que desea otorgar acceso. Debe ejecutar el script cada vez que cambie la estructura de la base de datos. He creado un breve tutorial separado para las tareas selectivas / excepcionales de MySQL.

https://adhoctuts.com/mysql-selective-exceptional-permissions-and-backup-restore/

https://youtu.be/8fWQbtIISdc

Answer 4

Sé que esta es una publicación antigua, pero pensé que agregaría a la pregunta @tdammers para que otros la vean. También puede realizar un SELECT CONCAT en information_schema.tables para crear sus comandos de concesión, y no tener que escribir un script separado.

Primero revoca todos los privilegios de esa db:

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

Luego crea tus declaraciones GRANT:

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";

Copie y pegue los resultados en su cliente MySQL y ejecútelos todos.