Users & Privileges

Every example in this track so far has quietly assumed some connection to MySQL, without asking who that connection actually belongs to. In a real deployment, that question matters enormously. This lesson starts the administration section of the track by covering how MySQL manages users and exactly what each one is allowed to do.

Why Not Just Use root Everywhere?

MySQL's root user can do absolutely anything - create and drop databases, create and remove other users, read and change any table, alter server configuration. Connecting a StreamHub web application directly as root would work, technically - but it also means that a bug in the application, a leaked database password, or a SQL injection vulnerability instantly hands over unrestricted control of the entire database, not just the streamhub schema the application actually needs. This idea - giving every user or application only the access it genuinely needs, and nothing more - comes up again in more depth in Security Best Practices; this lesson covers the mechanics that make it possible.

CREATE USER - the 'user'@'host' Syntax

Creating a dedicated account for the StreamHub application looks like this:

CREATE USER 'streamhub_app'@'localhost' IDENTIFIED BY 'a-strong-password';

The 'user'@'host' part is a genuinely important, MySQL-specific detail: a MySQL user is identified by both a username and which host it's allowed to connect from - not just the username on its own. 'streamhub_app'@'localhost' and 'streamhub_app'@'%' (the % wildcard meaning "any host") are two completely separate accounts as far as MySQL is concerned, even though they share a username - each can have its own password and its own set of privileges. Restricting an account to 'localhost' when the application only ever connects from the same machine as the database server is a simple, meaningful restriction on its own.

GRANT - Giving a User Specific Privileges

A freshly created user can log in, but can't do anything with any database until it's explicitly granted privileges. GRANT hands out exactly the permissions listed, on exactly the tables or schema listed:

GRANT SELECT, INSERT, UPDATE, DELETE ON streamhub.* TO 'streamhub_app'@'localhost';

This is deliberately just enough for a typical web application to function - reading and writing rows in every table in streamhub. Notice what's not included: DROP and ALTER aren't granted here, on purpose. An application account genuinely never needs to drop a table or change the schema while it's running, so it shouldn't be able to - that's the principle of least privilege in practice, not just in theory.

REVOKE - Removing a Privilege

REVOKE is GRANT's mirror image - it takes back a privilege that was previously given. Suppose the streamhub_app account no longer needs to delete rows directly (maybe deletions are now handled through a separate, more carefully audited process):

REVOKE DELETE ON streamhub.* FROM 'streamhub_app'@'localhost';

After this runs, the account retains SELECT, INSERT, and UPDATE, but any DELETE attempt it makes will be rejected.

SHOW GRANTS - Auditing What a User Can Do

Rather than trying to remember every GRANT and REVOKE ever run against an account, SHOW GRANTS just lists its current privileges directly:

SHOW GRANTS FOR 'streamhub_app'@'localhost';

This is worth running periodically as a real check, especially on any account with more access than a brand-new reader - it's easy to grant something temporarily and forget to revoke it later.

FLUSH PRIVILEGES - When It's Actually Needed

A common misconception is that FLUSH PRIVILEGES needs to be run after every GRANT or REVOKE. It doesn't - both of those statements take effect immediately on their own. FLUSH PRIVILEGES is only genuinely necessary after privileges have been changed by directly editing MySQL's internal grant tables (for example, running a raw UPDATE against mysql.user), which reloads the in-memory privilege cache from those tables. If you're only ever using GRANT and REVOKE as shown above, you generally don't need it at all.

A Separate Read-Only Reporting User

Not every account that needs access to StreamHub's data should be able to change it. A BI dashboard that only displays statistics - total signups per month, most-watched genres, and so on - has no legitimate reason to ever modify a row, so it should be physically incapable of doing so, not just trusted not to:

CREATE USER 'reporting'@'%' IDENTIFIED BY 'another-strong-password';
GRANT SELECT ON streamhub.* TO 'reporting'@'%';

With only SELECT granted, this account can run any read query it wants against StreamHub's data, but an INSERT, UPDATE, or DELETE from this connection will always fail - exactly the guarantee you want for a reporting tool that should never be able to touch live data, no matter what bug or bad query it might run.