Check the User permission in MySQL or MariaDB

Login with MySQL Database.

mysql -u root -p

Show grant or permission of the user.

-- Check user and privileges present to the user:
SELECT * FROM information_schema.user_privileges;

Select * from mysql.user 

SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SUPER';

--Check the permission of testuser
SHOW GRANTS FOR 'testuser'@'localhost';
--Check the permission of current user
SHOW GRANTS for Current_USER;
--Check the permission of root user
SHOW GRANTS for 'root'@'localhost'
--Check the permission of testuser
SHOW GRANTS FOR 'testuser'@'localhost';
--Check the permission of current user
SHOW GRANTS for Current_USER;
--Check the permission of root user
SHOW GRANTS for 'root'@'localhost'

Create a user with grant privileges on the MySQL database for all objects.

grant select on database_name.* to 'testuser'@'localhost' identified by 'Password';

Common Privileges used:

Routines or Procedure privileges: ALTER ROUTINE (alter or drop),CREATE ROUTINE,EXECUTE

Table: 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

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 on the Mysql database.

-- Grant all privileges make user as Superuser
grant all on database_name.* to 'testuser'@'localhost;

-- Grant select on specific table
GRANT SELECT ON db_name.sales TO 'testuser'@'localhost';

-- Grant privileges to all the databases
GRANT SELECT, INSERT ON *.* TO 'testuser'@'localhost';

--Grant on specific columns of the table
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'testuser'@'localhost';

-- Grant procedure or routine priviliges
 GRANT CREATE ROUTINE ON DB_NAME.* TO 'testuser'@'localhost';
 GRANT EXECUTE ON PROCEDURE DB_NAME.myproc TO 'testuser'@'localhost';

Revoke the permission from the User in MySQL:

-- Revoke all privileges from the User.
 revoke all privileges on database_name.* from 'testuser'@'localhost';

--Revoke only delete privilege
revoke delete on database_name.* from 'testuser'@.'localhost';

--Revoke only alter privilege
revoke alter on database_name.* from ''@'localhost';

--Revoke CREATE permissions for all databases * and all tables *
revoke create on *.* from 'testuser'@'localhost';

--Revoke drop from specific database
REVOKE DROP ON database_name.* FROM 'testuser'@'localhost';

--Revoke all privileges with grant option
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user

5. Flush commands are used to reload all the privileges after making all the changes with grant or revoke commands in MySQL. It’s better to run these commands to refresh the privileges.

FLUSH PRIVILEGES;

Note:

GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of the GRANT statement is not granted retroactively to any account.

REVOKE ALL at the global level revokes all granted static global privileges and all granted dynamic privileges.

The FLUSH PRIVILEGES statement reads the global_grants table for dynamic privilege assignments and registers any unregistered privileges found there.