Review the Permission of the user in MySQL or MariaDB

Check the permission having a user:

select user,host from mysql.user;
Select * from mysql.user;
SELECT * FROM information_schema.user_privileges;

--Check for Super Privileges:
SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SUPER';

Display all grant information for a user

SHOW GRANTS [ FOR username@hostname ]
SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS for 'root'@'%';
SHOW GRANTS FOR 'joe'@'office.example.com';
SHOW GRANTS FOR 'joe'@'home.example.com';

Display all privileges for the current user:

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Additional views for check grants

 --User-Specific Grants
 SELECT * FROM mysql.user;
 --Database-Specific Grants
 SELECT * FROM mysql.db;
 --Table-Specific Grants
 SELECT * FROM mysql.tables_priv;
 --Column-Specific Grants
 SELECT * FROM mysql.columns_priv;

Privileges list:

TABLES:
UPDATE,TRIGGER,SELECT,REFERENCES(creating Foregin key),INDEX(Create or Drop index),INSERT,DROP,DELETE,CREATE TEMPORARY TABLES(create,select,insert,drop etc on temp table ),ALTER

VIEWS:
CREATE VIEW,DROP,SHOW VIEW

ROUTINES or PROCEDURE:
ALTER ROUTINE (alter or drop),CREATE ROUTINE,EXECUTE

Server administrator privileges:
ALL [PRIVILEGES],CREATE ROLE,CREATE USER,DROP ROLE,PROCESS(show processlist or information_schema.processlist),PROXY,RELOAD(FLUSH) ,SHOW DATABASES,SHUTDOWN,SUPER,USAGE,CREATE TABLESPACE etc.

Grant the privileges to the user:

_{Note:
[Table name] = “*” = means all users
[HOSTNAME] = “%” = means all hosts otherwise specify IP address or hostname}

Syntax:
 GRANT ALL ON [database name].[table name] TO [user name]@[host name];
 GRANT privileges ON object TO user;

Example:
--Grant DML operation privileges to Contact table
GRANT SELECT, INSERT, UPDATE, DELETE ON contacts TO 'sunny'@'localhost';

--Grant all privileges on contacts table
GRANT ALL ON contacts TO 'sunny'@'localhost';

--Grant all privileges to the Database all objects:
GRANT ALL ON database_name.* TO 'sunny'@'localhost';

Revoke the privileges for the user:

Syntax:
 REVOKE ALL ON [database name].[table name] FROM [user name]@[host name

REVOKE privileges ON object FROM user;

Example:
-- Revoke delete update privileges on contact table from sunny user.
REVOKE DELETE, UPDATE ON contacts FROM 'sunny'@'localhost';

-- Revoke all privileges on contact table from sunny user.
REVOKE ALL ON contacts FROM 'sunny'@'localhost';

--Revoke select from contacts table from all users.
 REVOKE SELECT ON contacts FROM '*'@'%';

Grant and Revoke Execute permission on Procedure/Function

Syntax:
 GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;
 Example:
 GRANT EXECUTE ON FUNCTION calsalary TO 'sunny'@'localhost';

Syntax:
 REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user;
 Example:
 REVOKE EXECUTE ON FUNCTION calsalary FROM 'sunny'@'localhost';