MySQL Grants and Roles for Users

How to control what resources and features a user can access

Basic Concepts

Grants are ways of given "permissions" to users
You normally grant something to a specific user for a specific Database
You can even be as specific as dbname.tablename instead of dbname.* or even for specific procedures so
- GRANT EXECUTE ON PROCEDURE dbname.procedurename to user@localhost;
In MySQL 8+ you can also create "roles" which are treated as "groups" of grants
and you can REVOKE permissions/grants as well

Grants

INSERT, SELECT, UPDATE, DELETE
CREATE, CREATE VIEW, SHOW VIEW
REFERENCES
TRIGGERS, CREATE ROUTINE, ALTER ROUTINE, EXECUTE

DROP, ALTER, INDEX

GRANT select,insert,update,delete,alter,
index,references,drop,alter routine, 
create routine,create, create view, 
show view,execute, trigger
on dbname.* to 'user'@'localhost';

Roles

New feature in 8.x
Allow you to create a named "Alias" that you can provide grants to
Makes it much easier to manage grants

Using an _ in the name appears to cause errors, avoid this

# You will need to do these as mysql 'root' 
create user 'user'@'localhost' Identified WITH SHA256_PASSWORD by 'passwordgoeshere';
CREATE ROLE 'websearch', 'processsales', 'basicadmin';

GRANT SELECT on dbname.* to 'websearch';
GRANT INSERT,SELECT,UPDATE,DELETE,EXECUTE on dbname.* to 'processsales';

GRANT 'processsales' to user@localhost;

# If a user has multiple roles, you can specify that only certain roles start for the user, or ALL of them
# If you skip the next step, the user may not see their database at all.
SET DEFAULT ROLE ALL to user@localhost;

SHOW GRANTS for websearch;
SHOW GRANTS for processsales;
SHOW GRANTS for user@localhost;
SHOW GRANTS for user@localhost USING processsales;

#  Now we add a "SHOW VIEW" grant to processsales
GRANT show view on dbname.* to processsales;

# and notice any user that has "processsales" as a grant ALSO gets "show view"
SHOW GRANTS for user@localhost USING processsales;

# REVOKE is very similar to grant and you can revoke from a user or a role
REVOKE delete on dbname.* from processsales;

# notice again that if you revoke delete from the role, it affects any user granted that role
SHOW GRANTS FOR processsales;
SHOW GRANTS for user@localhost USING processsales;

Listing the ROLEs

This gives you the list of ROLEs created, and if they have been assigned (1) to at least one user, or not assigned (0) to anyone

It does not tell you any specifics about the individual ROLE, so you need to use the SHOW GRANTS to find out that.

USE mysql;
SELECT DISTINCT User 'Role Name', if(from_user is NULL,0, 1) Active 
FROM mysql.user LEFT JOIN role_edges ON from_user=user 
WHERE account_locked='Y' AND password_expired='Y' AND authentication_string='';

Class Assignment

Create Three Roles
- "websearch"
  - select
  - show view
  - execute (maybe ONLY give execute, and then for specific procedures)
- "processsales"
  - select
  - insert
  - update
  - delete
  - show view
  - execute (maybe only give execute at all, and again for specific procedures)
  - create temporary tables
- "webadmin"
  - create
  - alter
  - drop
  - create view
  - trigger
  - create routine
  - alter routine
  - references
  - index
  - the rest from the previous two roles

Basic Concepts

Grants

Roles

Listing the ROLEs

Class Assignment

Resources