Week 12 - User management & Permissions
Week 12 - User management & Permissions
This week we'll look at user management and permissions in MySQL, including the GRANT, SET and ALTER commands.
Alright, so you've been putting your heart and souls into your proposals, and you may be wondering… what are we learning today that needs to go in my final project?
The answer is… nothing. Today's lesson won't be necessary for building a database, which is what you're doing for your final assignment. What it will be helpful for, I believe, is when you need to connect to and manage the database(s) for websites in the future.
Creating a user
The syntax for creating a user in MySQL is pretty simple:
CREATE USER '{userName}'@'{domain}'
IDENTIFIED BY '{password}';The user name and password are arbitrary - meaning you can set them to whatever you want, so long as there isn't an existing user with the same user name.
If you didn't want to throw an error while running this statement, you could use the command CREATE USER IF NOT EXISTS. If you use this command, though, make sure you're paying attention to whether or not the user gets created!
The domain (a.k.a. the 'host name') is the IP address of a computer from which a valid connection can be made.
You can use the % wildcard symbol (just like in our LIKE clauses!) to set the domain to "any IP address", or you could specify the IP of a specific machine.
Most often the domain is set to localhost.
Why localhost?
localhost is shorthand for "this machine". If you've got your web server (i.e. your server-side application, be it PHP or whatever) and your database server running on the same machine, then you can give your application (i.e. SQL statements in your PHP or whatever) permission to access the database this way. This is by far the most common way to set up your technology stack.
Using your admin account, also set to the localhost domain, you can still access your database directly by using the SSH protocol Opens in a new window to log in to the server.
Specifying localhost as the user domain is the most secure way to set up your database user accounts. You can't access the database without server access, and if a malicious actor has direct server access, well… you're already screwed.
Passwords in MySQL
Setting or updating a password for an existing user
Here's the syntax for setting or updating a password for an existing user:
ALTER USER '{userName}'@'{domain}'
IDENTIFIED BY '{newPassword}';The ALTER USER statement is the preferred method, according to the documentation Opens in a new window, but you'll see the following syntax occasionally:
SET PASSWORD FOR
'{userName}'@'{domain}' = '{newPassword}';The ALTER USER syntax is preferred due to some extra security measures being in place Opens in a new window.
Default password expiration
Newer versions of MySQL (including the one we use) don't have a default period for password expiration, but older versions were set to expire after 360 days, after which the system would prompt users to update their passwords.
It's still possible to set a default expiration period, assuming your user account has the permission to do so.
-- Passwords will expire every 123 days.
SET GLOBAL default_password_lifetime = 123;
-- Passwords will not expire
-- (this is the default).
SET GLOBAL default_password_lifetime = 0;By the way, if you ever want to see the system variables (and again, you have to have permission), you can check on them with this query:
SHOW GLOBAL VARIABLES;Resetting an expired password
If you ever have MySQL tell you the following:
ERROR 1820 (HY000): You must reset your password
using ALTER USER statement before executing
this statement.…you can update your password thusly:
ALTER USER USER() IDENTIFIED BY '{newPassword}';The USER() function returns the current user in the format {userName}@{domain}
Renaming a user
Pretty simple, actually:
RENAME USER '{oldName}'@'{oldDomain}'
TO '{newName}'@'{newDomain}';
Dropping a user
Shocker:
DROP USER '{userName}'@'{domain}';
Database privileges
What are database privileges?
Database privileges, as you might guess, are the categories of commands that a user is allowed to execute.
How to see existing privileges
Here's the syntax for seeing a user's privileges:
SHOW GRANTS FOR '{userName}'@'{domain}';By default, users have no privileges.
If you run the SHOW GRANTS command for a user with no privileges, the database will give you a response like this:
GRANT USAGE ON *.* TO '{userName}'@'{domain}'GRANT USAGE sounds like you can use the database, doesn't it? Whelp it means the opposite. ¯\_(ツ)_/¯
Granting privileges
The only user who has privileges on a mysql database by default is root (the user who creates the MySQL installation).
The syntax for giving a user the same privileges as the root user is as follows:
GRANT ALL PRIVILEGES ON *.*
TO '{userName}'@'{domain}';What does *.* mean? Well, remember that there can be multiple databases running under a single MySQL installation. All the databases I created for you are running on the same database server.
*.* means "all tables, views and procedures within all databases within this server". The first asterisk means "all databases", and the second asterisk "all tables, views and procedures".
Note that the asterisk here works the same as when we use it in a select statement, essentially meaning "all of this thing".
When I run the following command on our database server:
SHOW GRANTS FOR '{studentNumber}'@'%'…MySQL returns something like the following:
Grants for {studentNumber}@%
-------------------------------------------
GRANT USAGE ON *.* TO '{studentNumber}'@'%'
GRANT ALL PRIVILEGES ON `{studentNumber}`.*
TO '{studentNumber}'@'%'The first row returned means the user exists for databases, with no privileges.
The second row returned means the user has all privileges on all tables, views and procedures for the database named "{studentNumber}".
Let's say you wanted to give someone the ability to read and insert data from one of your tables. The syntax would look like this:
GRANT SELECT, INSERT ON {databaseName}.{tableName}
TO '{userName}'@'{domain}';You can use the same syntax to give someone access to a VIEW as well!
Another example, this time giving someone the ability to call a procedure:
GRANT EXECUTE
ON PROCEDURE {databaseName}.{procedureName}
TO '{userName}'@'{domain}';As I said earlier, there's a ton of different kinds of permissions Opens in a new window you can give to a user. I'm not going to do a deep dive on them here, because, honestly, most of you will have this kind of stuff done for you automagically when you install a CMS, or done for you manually by your database administrator.
But, when you find yourself in a situation when you need to get granular with permissions, go straight to the source and refer to the docs!
Granting multiple privileges, and/or granting privileges to multiple users.
Pretty easy to do, just comma-separate the privileges and/or the user names:
GRANT SELECT, INSERT, DELETE{, orWhatever}
ON {databaseName}.{tableName}
TO '{user1}'@'{domain}',
'{user2}'@'{domain}',
'{user3}'@'{domain}',
'{user4}'@'{domain}',
'{user5}'@'{domain}',
'etc…';Revoking privileges
Don't want your buddy inserting any more data?
REVOKE INSERT ON {databaseName}.{tableName}
FROM '{userName}'@'{domain}';Want to erase all their privileges (without dropping them as a user)?
-- That's cold.
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM '{userName}'@'{domain}';Do you need to FLUSH privileges?
Contrary to popular belief (i.e. most tutorials out there), you don't actually need to use the following command after updating privileges:
FLUSH PRIVILEGES;…or at least not using the methods we're discussing here.
FLUSH PRIVILEGES tells MySQL to re-check the user privileges. As the documentation tells us Opens in a new window, however, if you use what are known as "account management statements", like GRANT, REVOKE, SET PASSWORD, and RENAME USER, the privileges are automatically updated, meaning no need to FLUSH them!
So why does FLUSH PRIVILEGES even exist? Well, it used to be necessary until about MySQL version 3 (a long time ago!), but it's still around now because there's a way to affect users and their permissions without using account management statements.
You can affect users just by using INSERT, UPDATE and DELETE statements, but it is very much not recommended.
I do enjoy telling you how to get yourself into trouble though, so let me tell you about something called…
The database known as `mysql`
When a MySQL server is created, it automatically creates a database for storing information about itself. It's the database version of the metadata in an html document's head, I suppose.
This database is called, unimaginatively, mysql.
The mysql database is functionally no different than any other database - it has tables full of data, and you can use SELECT, INSERT, UPDATE, DELETE with them - in fact, anything you can do with any other database table. Assuming, that is, that you have the correct privileges on mysql (the database, I mean).
There is a table within the mysql database called user, where all the user privileges are stored. If you were to run the following:
UPDATE USER SET Select_priv = 'Y'
WHERE user = '{userName}';…that would indeed update the user, granting them the privilege to SELECT things. However, because this update was not done using an account management statement, the privileges do indeed need to be flushed.
When to create a user
Well, heck, it's your database, but…
You should definitely not create a database user for each website user.
This would be like a restaurant giving an apron and a chef's knife to every customer who walked in the door, and then letting them all in the kitchen.
Maybe you've got a team with a database admin, and some strong developers, and you can create accounts for each one of them.
Most likely, what you want to do is create one database user for each service that uses the database. Got a website? That's a user. An API? That's a user. Microservices? Users.
When it comes to website users, you should be letting your server-side code do the user management, and then it can interact with the database via a single connection.