It makes sense that you need a user to carry out tasks and operations in MySQL. Querying, updating data, and importing new data are just a few of the many jobs users perform in the database. Typically, I blog with a focus more on that of a Developer or Programmer’s point of view (to my understanding), with the goal to move into a SQL Developer role.
To be frank, DBA tasks are not my ‘strong suit’. I would not say they bore me. More along the lines of, I do not understand many of the underlying internals in database management.
Therefore, this blog post is a beginning foray into the entry-level database administrative side of things, and what better place to start with than establishing a new user?
Photo by Caspar Rubin on Unsplash
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 5.7.23
A rough draft collaboration?
Hey, I have a fantastic idea. Want to help me write this post?
GREAT!
By all means, please provide: guidance, corrections, and suggestions for best security practices with users in MySQL. You can be assured I do not know them all but, do want to learn correct and sound practices for establishing users in MySQL.
Leave any comments below to help me along as I learn. Many thanks!!!
To be clear, for the purpose of this blog post, the term user indicates a person working within the database directly performing ad-hoc queries, analytics, reporting, etc. Not registration or application users who are using a service or app.
In order to execute the following commands, I am logged in with the root user in my development environment.
To add a new user, MySQL has a command for that. CREATE USER.
1 2 | mysql> CREATE USER 'demo_1'@'localhost'; Query OK, 0 rows affected (0.17 sec) |
Using CREATE USER is recommended over executing DML statements (e.g., INSERT and UPDATE) directly against the underlying tables where user information and data is stored.
The new user still needs a password to authenticate with the system. To provide one, use the ALTER USER command and the IDENTIFIED BY clause:
1 2 | mysql> ALTER USER 'demo_1'@'localhost' IDENTIFIED BY '123'; Query OK, 0 rows affected (0.00 sec) |
A super important point here. The password I have provided for user ‘demo_1’ is pure garbage, used only in this blog post for demonstration. Please use strong, hardened passwords.
Brief Interlude… A better example.
Better yet, you have the option to use sha256_password authentication plugin for a hardened password as shown below:
1 2 | mysql> CREATE USER 't_one'@'localhost' IDENTIFIED WITH sha256_password BY 'stronger'; Query OK, 0 rows affected (0.29 sec) |
To be clear, you can establish a new user with accompanying password, all in one command. See with this next example:
1 2 | mysql> CREATE USER 'demo_2'@'localhost' IDENTIFIED BY 'abc'; Query OK, 0 rows affected (0.01 sec) |
Now the ‘demo_1’ user can log in (not shown):
1 2 3 4 5 6 7 8 | -- confirming the currently logged in user mysql> SELECT USER(); +------------------+ | USER() | +------------------+ | demo_1@localhost | +------------------+ 1 row in set (0.03 sec) |
1 2 3 4 5 6 7 | mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) |
I have many databases installed on my development environment but user ‘demo_1’ has not been granted access to any of them. Let’s take care of that next.
I have the Sakila example database installed and I will GRANT user ‘demo_1’ access to it:
1 2 | mysql> GRANT ALL PRIVILEGES on sakila.* TO 'demo_1'@'localhost'; Query OK, 0 rows affected (0.11 sec) |
And see the permissions with this SHOW GRANTS statement:
1 2 3 4 5 6 7 8 | mysql> SHOW GRANTS FOR 'demo_1'@'localhost'; +------------------------------------------------------------+ | Grants for demo_1@localhost | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'demo_1'@'localhost' | | GRANT ALL PRIVILEGES ON `sakila`.* TO 'demo_1'@'localhost' | +------------------------------------------------------------+ 2 rows in set (0.01 sec) |
Now, ‘demo_1’ user can connect and interact with the Sakila database. See below, after logging in (not shown):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | mysql> SELECT USER(); --Unnecessary command, used to show current user. +------------------+ | USER() | +------------------+ | demo_1@localhost | +------------------+ 1 row in set (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | sakila | +--------------------+ 2 rows in set (0.01 sec) mysql> USE sakila; --connecting to the sakila database. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec) |
Let’s briefly pause and digest just what ALL PRIVILEGES actually means. Here is the definition, directly from the documentation in section, 6.2.1 Privileges Provided by MySQL:
These privilege specifiers are shorthand for “all privileges available at a given privilege level” (except GRANT OPTION). For example, granting ALL at the global or table level grants all global privileges or all table-level privileges, respectively.
Although this user is now fully functional and able to interact with the chosen database, this may not be optimal. Nor what you intended. Also, at this point, user ‘demo_1’ does not have the GRANT OPTION and that is a good thing for GRANT is a powerful privilege. Nevertheless, ‘demo_1’ user can still: DROP, DELETE, or UPDATE tables.
After some evaluation, I hinder demo_1’s ability to execute any commands by using the REVOKE command and basically ‘turn back time’ on user ‘demo_1’:
1 2 3 4 5 6 7 8 9 10 | mysql> REVOKE ALL PRIVILEGES ON sakila.* FROM 'demo_1'@'localhost'; Query OK, 0 rows affected (0.07 sec) mysql> SHOW GRANTS FOR 'demo_1'@'localhost'; +--------------------------------------------+ | Grants for demo_1@localhost | +--------------------------------------------+ | GRANT USAGE ON *.* TO 'demo_1'@'localhost' | +--------------------------------------------+ 1 row in set (0.00 sec) |
After revoking those privileges, you can see below that the information_schema database and not Sakila is visible to user ‘demo_1’:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> SELECT USER(); +------------------+ | USER() | +------------------+ | demo_1@localhost | +------------------+ 1 row in set (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) |
Suppose user ‘demo_1’ only needs to ‘read’ data at this time. That can be handled at either the database, column, or table level respectively by granting just the SELECT privilege applicable to that specific level.
I’ll demonstrate starting at the column level and work my way up from there, progressing to the database level upon completion.
I’ll target a relatively small table for on-screen brevity, however these examples can easily be applied to larger tables as well.
Here you can see I am in the sakila database:
1 2 3 4 5 6 7 | mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | sakila | +------------+ 1 row in set (0.01 sec) |
The store table contains the below structure and data:
1 2 3 4 5 6 7 8 | mysql> SELECT * FROM store; +----------+------------------+------------+---------------------+ | store_id | manager_staff_id | address_id | last_update | +----------+------------------+------------+---------------------+ | 1 | 1 | 1 | 2006-02-15 04:57:12 | | 2 | 2 | 2 | 2006-02-15 04:57:12 | +----------+------------------+------------+---------------------+ 2 rows in set (0.00 sec) |
Up first, with the below GRANT command, I enable read-only access (through the SELECT privilege) for user ‘demo_1’ on the last_update column only:
1 2 | mysql> GRANT SELECT(last_update) ON sakila.store TO 'demo_1'@'localhost'; Query OK, 0 rows affected (0.12 sec) |
User ‘demo_1’ is logged in and able to access the store table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> SELECT USER(); +------------------+ | USER() | +------------------+ | demo_1@localhost | +------------------+ 1 row in set (0.00 sec) mysql> SHOW TABLES; +------------------+ | Tables_in_sakila | +------------------+ | store | +------------------+ 1 row in set (0.00 sec) |
What happens should ‘demo_1’ attempt to read data from all columns?
1 2 | mysql> SELECT * FROM store; ERROR 1143 (42000): SELECT command denied to user 'demo_1'@'localhost' for column 'store_id' in table 'store' |
Does that apply for individual columns within the table also?
1 2 | mysql> SELECT manager_staff_id FROM store; ERROR 1143 (42000): SELECT command denied to user 'demo_1'@'localhost' for column 'manager_staff_id' in table 'store' |
Because of the specific GRANT command from above, the only column user ‘demo_1’ can access through the SELECT privilege is last_update:
1 2 3 4 5 6 7 8 | mysql> SELECT last_update FROM store; +---------------------+ | last_update | +---------------------+ | 2006-02-15 04:57:12 | | 2006-02-15 04:57:12 | +---------------------+ 2 rows in set (0.00 sec) |
You can enable this same functionality at the table level with a similar GRANT command (Note: Below command run as the root user; login not shown.):
1 2 | mysql> GRANT SELECT ON sakila.store TO 'demo_1'@'localhost'; Query OK, 0 rows affected (0.01 sec) |
The key to this privilege level is sakila.store which indicates, database_name.table_name.
Logged back in, ‘demo_1’, can SELECT all columns from table store:
1 2 3 4 5 6 7 8 | mysql> SELECT * FROM store; +----------+------------------+------------+---------------------+ | store_id | manager_staff_id | address_id | last_update | +----------+------------------+------------+---------------------+ | 1 | 1 | 1 | 2006-02-15 04:57:12 | | 2 | 2 | 2 | 2006-02-15 04:57:12 | +----------+------------------+------------+---------------------+ 2 rows in set (0.00 sec) |
Yet, still unable to read data from any other tables in the Sakila database:
1 2 | mysql> SELECT * FROM staff; ERROR 1142 (42000): SELECT command denied to user 'demo_1'@'localhost' for table 'staff' |
For the final section of this blog post, I will GRANT user ‘demo_1’ the SELECT privilege on all tables. The sakila.* notation indicates all tables in the sakila database:
1 2 | mysql> GRANT SELECT ON sakila.* TO 'demo_1'@'localhost'; Query OK, 0 rows affected (0.01 sec) |
Notice the available tables to user ‘demo_1’ after logging back in:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | mysql> SELECT USER(); +------------------+ | USER() | +------------------+ | demo_1@localhost | +------------------+ 1 row in set (0.01 sec) mysql> SHOW TABLES; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec) |
Now ‘demo_1’ has SELECT privileges on those as well:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> SELECT * FROM language; +-------------+----------+---------------------+ | language_id | name | last_update | +-------------+----------+---------------------+ | 1 | English | 2006-02-15 05:02:19 | | 2 | Italian | 2006-02-15 05:02:19 | | 3 | Japanese | 2006-02-15 05:02:19 | | 4 | Mandarin | 2006-02-15 05:02:19 | | 5 | French | 2006-02-15 05:02:19 | | 6 | German | 2006-02-15 05:02:19 | +-------------+----------+---------------------+ 6 rows in set (0.00 sec) |
1 2 3 4 5 6 7 8 | mysql> SELECT * FROM staff_list; +----+--------------+----------------------+----------+-------------+------------+-----------+-----+ | ID | name | address | zip code | phone | city | country | SID | +----+--------------+----------------------+----------+-------------+------------+-----------+-----+ | 1 | Mike Hillyer | 23 Workhaven Lane | | 14033335568 | Lethbridge | Canada | 1 | | 2 | Jon Stephens | 1411 Lillydale Drive | | 6172235589 | Woodridge | Australia | 2 | +----+--------------+----------------------+----------+-------------+------------+-----------+-----+ 2 rows in set (0.00 sec) |
In order to maintain the least amount of output for this blog post, I limited this demonstrated functionality for the SELECT clause privilege only. However, the same is easily applicable for other CRUD commands (e.g., INSERT, UPDATE, and DELETE).
Like what you have read? See anything incorrect? Please share your thoughts and comments below. Thanks for reading!!!
Explore the official MySQL 5.7 Online Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.