If you do not already know how to use MySQL create user statements to create database users, this tutorial will take you through the process. But, before running a MySQL create new user command you need to have all the relevant root privileges.
MySQL allows you to specify who can connect to the database server and from which host they can connect. Because of this, each MySQL create user statement specifies both the username and the hostname. Both feature @ as a separator character.
Assuming the root user connects to the MySQL DB from the localhost, their user account will be root@localhost. For security purposes, the root user can only connect to the DB server from localhost rather than remotely.
Another advantage of the ability to specify user and hostname when creating users is that it gives you the ability to create multiple user accounts that can connect to the database server from different hosts.
All MySQL user accounts are stored in the user grant table of the database.
Creating Database User Accounts – MySQL Create New User
The MySQL CREATE USER statement allows DBA’s to create user accounts as they need them. The syntax for the command is as follows below:
The MySQL CREATE USER clause follows the first_user in the format ‘username’@’localhost’.
The IDENTIFIED BY clause specifies the password which has to be in cleartext. However, before saving the user account in the user table, MySQL encrypts the password. Let us create a new admin user and give them the password master. Our new admin will be connecting to the server from localhost.
The MySQL create user statement to use for this function will be:
Once you create a user, you can view their permission or privileges using the statement SHOW GRANTS:
With our new user, once you run the SHOW GRANTS command, you will realize that the user can do nothing more than login. The symbol *.* lets you know that the user has no privileges so far. Once you create a user, you need to allocate them permissions if they are to access and work on the database.
To allow other users to interact with the database, you use the GRANT statement.
If you want the user to connect to the server from any host, you use a wildcard ‘%’ as you can see below:
When creating a new database user, you need to specify the host from which they will connect to the database. Failure to specify the host gives the user the ability to connect from any of the available hosts. For instance, below is a statement that grants the user the ability to connect to the server from different hosts:
As you specify the username and hostname, make sure to include the quotes especially when there are special characters. The first command below will create a user user1 who can only connect to the database from localhost.
The second command will create user1@localhost that will be able to connect to the database from any host. Notice how the quotes are missing before and after ‘@’ in the second statement.
Creating a user that already exists returns an error.
A MySQL output of the console to see that this user exist would be good. So the reader can check if that user really exist in the database.
Like a output from “SELECT * FROM mysql.user;” In general the console output is a must have on all MySQL related blog posts.
Granting New User’s Privileges
Remember how we said our user ‘admin’ did not have any privileges, this is where we learn how to allocate those privileges. When allocating privileges, after the keyword GRANT, you should specify one or more permissions. If you grant a user multiple permissions on the same statement, separate each from the other using a comma.
You then need to specify whether the privilege is global (*.*), database-based (database.*), table-based (database.table) or even column-based. To give column privileges, you have to specify the column name after the privilege. If you are granting a certain privilege across different columns, list the columns after the privilege and separate the columns with a comma.
The third part of the statement is where you specify which user you are granting what privilege. If you are granting existing users privileges, the GRANT statement modifies what the user can do. If you have not yet created the user, the GRANT statement automatically creates the new user and you can set the user password on the GRANT statement by using the clause IDENTIFIED BY.
Additional specifications include the connection (such as SSL or X095) through which the user will connect to a database for security reasons. Last, you can use the WITH GRANT OPTION statement to grant or remove privileges that you possess from other users.
In shared environments, the WITH clause comes in handy as it enables you to allocate server resources.
As with the MySQL create new user command, to use GRANT and successfully allocate permissions, you must have the GRANT OPTION privilege. Also, you must have the permissions you are allocating in order to allocate them to other users.
We Look At Some Examples Of MySQL GRANT
The typical sequence before granting user’s privileges begin with MySQL create schema. From MySQL create schema, you then create users that will access and use the schema. MySQL create schema is a synonym to MySQL create database and thus they perform the same function.
Once your schema is up and you have users, you are free to start granting the users privileges as you deem fit.
The user we created above, ‘admin’, did not have any privileges, in this section, we are going to grant the said user certain privileges.
The command above grants our user full access to all databases and database objects in our server. This is specified by the clause ON *.*
To grant a user all privileges to a certain database, you can use a statement like below where the user auditor gets full access to the database accounts:
To grant multiple privileges without granting all of them, use the statement:
Apart from the privileges we have looked at above, there are numerous other privileges you can grant users using the GRANT statement. Now that you know how to create new users and grant privileges, you are set to start giving other users access to your databases if you so wish.
