MySQL User Permissions and Database Queries
Specific Queries and User Management
A specific query that shows global users who have permission to grant on all tables of the ‘jmmusuarios’ database:
CREATE USER 'globalnoconcede'@'localhost' IDENTIFIED BY 'soyglobal';
GRANT ALL PRIVILEGES ON JMMUSUARIOS.* TO 'globalnoconcede'@'localhost';
SELECT DB.User, DB.Db, DB.Grant_priv
FROM MYSQL.DB
WHERE DB.Db LIKE 'jmmusuarios'
AND DB.Grant_Priv LIKE 'Y';
Query that shows the functions and procedures in the system:
DESCRIBE MYSQL.PROC;
SELECT PROC.name, PROC.db, PROC.type
FROM MYSQL.PROC;
Superusers and Their Permissions
Display superusers, along with read and write permissions on all tables of all databases:
SELECT USER.Host, USER.User, USER.Select_Priv,
USER.Insert_Priv, USER.Delete_Priv, USER.Update_Priv
FROM MYSQL.USER
WHERE USER.User NOT IN (SELECT DB.User
FROM MYSQL.DB)
AND USER.User NOT IN (SELECT TABLES_PRIV.User
FROM MYSQL.TABLES_PRIV);
Permissions Breakdown
- alter routine, create routine, event, execute, trigger: Procedure permissions
- create, create view, drop, show view, create view, alter, index, references: Table and view permissions
- temporary files, lock tables: Server administration permissions
- table_priv: Indicates table permissions. Use in queries like:
WHERE tables_priv.db LIKE 'database_name'
AND tables_priv.table_name LIKE 'table_name';
Note: In local environments, only trigger permissions can be granted.
Function to Check Global Permissions
DROP FUNCTION IF EXISTS F12_PERMISOS_GLOBAL;
DELIMITER |
CREATE FUNCTION F12_PERMISOS_GLOBAL(usuario CHAR(16),
base VARCHAR(64))
RETURNS TEXT BEGIN
DECLARE lectura CHAR(1) DEFAULT 'N';
DECLARE insercion CHAR(1) DEFAULT 'N';
DECLARE eliminacion CHAR(1) DEFAULT 'N';
DECLARE actualizacion CHAR(1) DEFAULT 'N';
DECLARE numusuarios INT DEFAULT 0;
DECLARE numbases INT DEFAULT 0;
DECLARE resultado TEXT DEFAULT 'The user --> ';
SET resultado = CONCAT(resultado,usuario);
SET numusuarios = (SELECT COUNT(DB.User)
FROM MYSQL.DB
WHERE DB.User LIKE usuario
AND DB.Db LIKE base);
SET numbases = (SELECT COUNT(SCHEMATA.schema_name)
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMATA.schema_name LIKE base);
IF numusuarios <> 1 OR numbases <> 1 THEN
SET resultado = CONCAT(resultado,' PLEASE CHECK ARGUMENTS');
ELSE
SET lectura = (SELECT DB.Select_Priv
FROM MYSQL.DB
WHERE DB.User LIKE usuario
AND DB.Db LIKE base);
IF lectura LIKE 'Y' THEN
SET resultado = CONCAT(resultado,'\n YES, has read permission (select) ');
ELSE
SET resultado = CONCAT(resultado,'\n NO, does not have read permission (select) ');
END IF;
SET insercion = (SELECT DB.Insert_Priv
FROM MYSQL.DB WHERE DB.User LIKE usuario AND DB.Db LIKE base);
IF insercion LIKE 'Y' THEN
SET resultado = CONCAT(resultado,'\n YES, has insert permission (Insert)');
WHERE DB.User LIKE usuario
AND DB.Db LIKE base);
IF actualizacion LIKE 'Y' THEN
SET resultado = CONCAT(resultado,'\n YES, has update permission (Update) ');
ELSE
SET resultado = CONCAT(resultado,'\n NO, does not have update permission (update)');
END IF; END IF; RETURN(resultado) END | DELIMITER ;