MySQL User Privileges and Stored Function Examples

Querying Users and Privileges

DESCRIBE MYSQL.USER;

SELECT USER.User
FROM MYSQL.USER;

Creating Users and Granting Privileges

CREATE USER 'profesor'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'profesor'@'localhost';

CREATE USER 'maikelnoviene'@'localhost' IDENTIFIED BY 'maikelnofaltes';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'maikelnoviene'@'localhost';

Checking User Privileges

SELECT USER.User
FROM MYSQL.USER;

SELECT USER.User, USER.Select_Priv, USER.Insert_Priv, USER.Delete_Priv,
USER.Update_Priv
FROM MYSQL.USER;

SELECT USER.User, USER.Select_Priv, USER.Insert_Priv, USER.Delete_Priv,
USER.Update_Priv
FROM MYSQL.USER
WHERE USER.User LIKE 'maikelnoviene';

Counting Superusers

SELECT COUNT(USER.User) AS 'Número de superusuarios'
FROM MYSQL.USER;

Creating a Stored Function

DROP FUNCTION IF EXISTS F01_CUENTA_USUARIOS;

DELIMITER |

CREATE FUNCTION F01_CUENTA_USUARIOS()
RETURNS INT

BEGIN

DECLARE cuenta INT DEFAULT -666;

SET cuenta = (SELECT COUNT(USER.User)
FROM MYSQL.USER);

RETURN(cuenta);

END |

DELIMITER ;

SELECT F01_CUENTA_USUARIOS();

Listing Stored Procedures and Functions

DESCRIBE MYSQL.PROC;

SELECT PROC.name, PROC.db, PROC.type, PROC.param_list, PROC.returns
FROM MYSQL.PROC;

SELECT PROC.name, PROC.db, PROC.type, PROC.param_list, PROC.returns
FROM MYSQL.PROC
WHERE PROC.type LIKE 'function';

SELECT PROC.name, PROC.db, PROC.type, PROC.param_list, PROC.returns
FROM MYSQL.PROC
WHERE PROC.type LIKE 'FUNCTION';

Repeated Examples

DESCRIBE MYSQL.USER;

SELECT USER.User
FROM MYSQL.USER;

CREATE USER 'profesor'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'profesor'@'localhost';

CREATE USER 'maikelnoviene'@'localhost' IDENTIFIED BY 'maikelnofaltes';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'maikelnoviene'@'localhost';

SELECT USER.User
FROM MYSQL.USER;

SELECT USER.User, USER.Select_Priv, USER.Insert_Priv, USER.Delete_Priv,
USER.Update_Priv
FROM MYSQL.USER;

SELECT USER.User, USER.Select_Priv, USER.Insert_Priv, USER.Delete_Priv,
USER.Update_Priv
FROM MYSQL.USER
WHERE USER.User LIKE 'maikelnoviene';

SELECT COUNT(USER.User) AS 'Número de superusuarios'
FROM MYSQL.USER;

DROP FUNCTION IF EXISTS F01_CUENTA_USUARIOS;

DELIMITER |

CREATE FUNCTION F01_CUENTA_USUARIOS()
RETURNS INT

BEGIN

DECLARE cuenta INT DEFAULT -666;

SET cuenta = (SELECT COUNT(USER.User)
FROM MYSQL.USER);

RETURN(cuenta);

END |

DELIMITER ;

SELECT F01_CUENTA_USUARIOS();

DESCRIBE MYSQL.PROC;

SELECT PROC.name, PROC.db, PROC.type, PROC.param_list, PROC.returns
FROM MYSQL.PROC;

SELECT PROC.name, PROC.db, PROC.type, PROC.param_list, PROC.returns
FROM MYSQL.PROC
WHERE PROC.type LIKE 'function';

SELECT PROC.name, PROC.db, PROC.type, PROC.param_list, PROC.returns
FROM MYSQL.PROC
WHERE PROC.type LIKE 'FUNCTION';

Checking Schema Character Sets

SELECT INFORMATION_SCHEMA.schemata,
SCHEMATA.default_character_set_name
FROM INFORMATION_SCHEMA.schemata
WHERE SCHEMATA.default_character_set_name NOT LIKE 'utf8';

Final Repeated Examples

DESCRIBE MYSQL.USER;

SELECT USER.User
FROM MYSQL.USER;

CREATE USER 'profesor'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'profesor'@'localhost';

CREATE USER 'maikelnoviene'@'localhost' IDENTIFIED BY 'maikelnofaltes';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'maikelnoviene'@'localhost';

SELECT USER.User
FROM MYSQL.USER;

SELECT USER.User, USER.Select_Priv, USER.Insert_Priv, USER.Delete_Priv,
USER.Update_Priv
FROM MYSQL.USER;

SELECT USER.User, USER.Select_Priv, USER.Insert_Priv, USER.Delete_Priv,
USER.Update_Priv
FROM MYSQL.USER
WHERE USER.User LIKE 'maikelnoviene';

SELECT COUNT(USER.User) AS 'Número de superusuarios'
FROM MYSQL.USER;

DROP FUNCTION IF EXISTS F01_CUENTA_USUARIOS;

DELIMITER |

CREATE FUNCTION F01_CUENTA_USUARIOS()
RETURNS INT