Users, Roles and Privileges - Database Cheat Sheet

Run on a real MySQL server, one login at a time: CREATE USER, roles, GRANT and REVOKE, the default role a new account needs, SHOW GRANTS, an audit trail that names the person, and a stored procedure.

On the sheet:

- Who sees what: the role and table grid, read back from MySQL

- CREATE USER: one login per person, with a random password

- Roles: privileges go to roles, roles go to people

- A granted role starts switched off: and SET DEFAULT ROLE

- Checked on every statement: what the front desk may and may not do

- SHOW GRANTS: what an account may really do

- REVOKE: when a job changes, or ends

- An audit trail: USER() against CURRENT_USER() in a trigger

- A stored procedure: saved SQL, run with CALL

Two A4 pages, printed front and back, sized to print once and keep beside you while you work. Written in short sentences and plain English, for readers whose first language is not English and clearer for everyone else because of it.

It pairs with the Design Principles, ACID & SQL and Authentication & Authorization books, so you can learn each idea in full and then revise it here in two pages.

Every query on this sheet was run against a live database before the page was printed, so the result you read is the result you get. Instant PDF download. By M.R. Sterling.