Account Management Statements in MySQL

MySQL Access Privileges - Part 2

Foreword: In this part of the series, we look at MySQL Account Management Statements.

By: Chrysanthus Date Published: 18 Jul 2015

Introduction

This is part 2 of my series, MySQL Access Privileges. In this part of the series, we look at MySQL Account Management Statements. I assume you have read the previous part of the series before reaching here, as this is a continuation. In this series, the phrase, “root user” or “root” identifies the same person.

The Mysqlcom.pl Program
The mysql program called, Mysqlcom, is a client program you use to type in SQL statements for a MySQL server. The mysql prompt is, Mysql>. You must have used the Mysqlcom.pl program if you have covered the pre-knowledge series of this blog. If the Mysqlcom program (and its modules) is in the same computer as the MySQL server, then that computer is called localhost. In this series you will use Mysqlcom in the same computer as the server. So your host (computer) in this series is called, localhost.

Users for Illustrations below
For the teaching illustrations in this part of the series, there are 3 users of the MySQL server. The users are the root, you and another person called John. Your account name is, you@localhost, typed in the connect statement as, “connect "you", "localhost", 3306;”. The account name of the root is, 'root'@'localhost' - See below for more explanation.

The create user Statement
When a MySQL server is just installed, only the root user can use the server. The server is a program that can be used to create databases. In the installation process, the root user determines his user name. If he does not, the name root is assigned to him. In the installation process, the root user has (compulsory) to determine his password. Just after the installation, only the root can use the server and only the root can create databases. The root has all privileges (rights) in the server and all privileges in all databases (created or still to be created).

The root can create users and give them privileges. These new users can create their own users and also give them privileges. The chain can go down with new users creating their own new users. The root and the users can go on to create databases, whenever they like, in accordance with their privileges. The root user is also called the Database Administrator (DBA).

Any user has an account. An account consists of a user name, a password for the user and a number of privileges. The root has all privileges. This means that if an ordinary user has the right (privilege) to DROP (delete) a particular table, then the root also has the same right (privilege) to drop the same table. So, in a company, the root has to be a responsible person.

In this series, the phrase, “ordinary user” means a non-root user. There is only one root user. However, if the root likes, he can give all his privileges (rights) to any ordinary user. That is not however recommended. He can give all his privileges to more than one ordinary user; again that is not recommended.

The create user statement is used to create a new user. In simple terms, the syntax to create a user is:

    CREATE USER user-name [IDENTIFIED BY ‘password’];

user_name is the user’s name such as, ‘john’. You can also have something like:

    CREATE USER user-name;

With this statement, you create a user that does not have a password. You can add the password later, using the SET PASSWORD statement (see below). To create the user name and password at the same time, one would type something like:

    CREATE USER you IDENTIFIED BY 'you123';

For this tutorial, assume that this is your (the reader’s) user name (you) and password (you123).

For a user to create another user, he needs the CREATE USER privilege. The root user has this privilege by default. He can grant the privilege to you and you can grant it to another person. We see how this is done in the next section (grant statement).

The grant Statement
A user (including the root) uses the grant statement to give privileges to another user. In order to use the grant statement, the user must have the GRANT OPTION privilege. The root user has this GRANT OPTION privilege and all other privileges by default (automatically immediately after installation).

In simplified form, the syntax for the grant statement is:

    grant Privileges On Item To User

You do not need to type the password of the user in the grant statement. Before you can use the grant statement or any other statement or a database feature, you must first of all log into the MySQL server with your user name and password. After logging (using Mysqlcom or some other client program), you can then use the statements or features that you have the privileges (rights) for. The root has all the privileges (including the GRANT OPTION) to do anything. In order for the ordinary user to use the create user statement and the grant statement, he needs the CREATE USER and GRANT OPTION privileges respectively. So, the root can give the user, you, the CREATE USER and GRANT OPTION privileges as follows:

    grant CREATE USER, GRANT OPTION on *.* to you;

Here, *.* means every item in the server (not recommended though). Note the dot (.) in *.* . The privileges are separated by commas. Alternatively you can type the above statement as follows:

    grant CREATE USER on *.* to you with GRANT OPTION;

Note the use and position of the word, “with” in this statement. Also note the new position of GRANT OPTION. This alternative version (statement) seems to be more popular.

Now, with the above granting, after logging in (using the Mysqlcom client program for example) the user, you can create his own user as follows:

    create user john identified by 'john123';

The user, you, can then go on to grant the user john, privileges. If you want him to be able to create new users, then you, after logging in, would type:

    grant CREATE USER on *.* to john;

If you want him to be able to give some or all of his own privileges to the users he creates, then you would type:

    grant GRANT OPTION on *.* to john;

Again, *.* means everything and it is not recommended. In the next part of the series, I will show you how to grant privileges for a specific item, such as a database. The above two statements can be combined into one as follows:

    grant CREATE USER, GRANT OPTION on *.* to john;

or

    grant CREATE USER on *.* to john with GRANT OPTION;

The second version seems to be more popular.

Remember, when granting privileges, you do not need to type the password of the user who receives the privileges.

Note: you can only give to another user, privileges that you yourself has been given.

Above, we saw how the root granted the CREATE USER and GRANT OPTION to you in one statement. If he wanted you to only be able to create a new user, he would have typed:

    grant CREATE USER on *.* to you;

In this case, you cannot give any user, any privilege, even if you created the user. If he wanted you only to be able to give (grant) privileges to other users, he would have typed,

    grant GRANT OPTION on *.* to you;

In this case you cannot create a user, but if you had been given any privileges, you can grant them to other users.

So, you can give privileges to users that you did not create. Remember, it is not advisable to use, *.*, unless you really know what you are doing – see later.

Privileges Given
You cannot give to someone privileges that you do not have. The root has all privileges by default. Nobody can give to another person privileges that he does not have. When a privilege is given, it is copied; it is not transferred. When you grant (give) a privilege to someone, you continue to have the privilege, while he also have it. So, all privileges own by users are single copies from the root.

Revoking Privileges
You can give privileges to a user and he would be using the privileges. For one reason or the other, you can decide to take away, one or two, or more or all of the privileges from the user. In simple terms, one of the syntaxes to revoke privileges is,

revoke Privileges On Items From User

An example is,

revoke CREATE USER on *.* from john;

If you have more than one privilege to revoke, separate the privileges with commas. You can only revoke privileges that are yours (that you granted). Remember, you should be careful when using *.*. You will see examples of particular items in the next part of the series.

When you give somebody a privilege it is like you have copied the privilege to the person. Privileges are not transferred. So when you revoke a privilege from somebody, you continue to use the privilege, but he can no longer use it.

To use the above revoke syntax, you must have the GRANT OPTION privilege. There is another syntax for the revoke statement, which requires but the CREATE USER privilege, in order to use. You will see that in a later part of the series.

Dropping a User
You can drop a user. You do not have to revoke a user’s privileges first before you drop him. When a user is dropped, his account is closed, he looses all his privileges and he can no longer access (login to) the MySQL server (he can no longer access any database or any table. He can access nothing). The syntax to drop a user is:

    drop User account_name

So, if you want to drop the user John, you would type:

    drop user john;

You need the CREATE USER privilege in order to use this statement.

Set Password
Assume that you created the user, John as follows:

    create user john;

This statement without the password is OK; but John will not be able to log into the server, because he does not have a password. It is possible for you to then go on and give John a password as follows:

    set password for john = password('john123');

Note that the Set Password statement has an assignment operator. In simple terms the syntax is:

    set password for user_name = password('psswrd');

Here, password() is a function provided by MySQL. You have to use it as shown above in the Set Password statement. Do not forget to include the reserved word, “for”, in the statement.

In order to use the Set Password statement, you need to have the UPDATE privilege for the special server database called, mysql. Only the root normally has this privilege. He can give you the privilege, but it is something he would hardly do. So, if you (ordinary user) are creating a user, use the Create User statement and include the password in the statement.

If a user (client) forgets his password, the root can use the Set Password statement to give the user a new password. The root does not need to know the old password before doing this. However, he has to be sure that he is giving the password to the correct user.

Renaming User
It is not only the password of a user that can be changed; the account name can also be changed. However, for a user, these two components are changed independently (not-simultaneously). The syntax to change the user account name is:

rename user Old_Account_Name To New_Account_Name

An example is:

rename user jeffrey to jeff;

You need the CREATE USER privilege in order to use the Rename User statement. You also need to know the old user account name.

So, in order to change the password of a user, you (root) do not need the old password. However, in order to change the account name, you need the old account name. The two changes are done independently.

Logging In
After the root and/or some other user has created an account for you in a MySQL database system, you can log into the server. In simple terms a database system consist of a server and the databases. After logging into the server you can use any database if you have the right (privilege) to do so. To login, you need an account name and a password.

If you are working on a computer that has the MySQL server, the Mysqlcom client program, and is using the windows operating system, then you can login using the following sequence of commands:

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql
USE wholesale;

Here, wholesale is assumed to be the database you have privileges for.

Many users log into a MySQL server from a web page. You can log in at a web page. In this case the designer of the database system (web development) knows the host you are logging in from, so you would have to type only the user name and password at the web page. When you click the Submit Button at the web page, the user name and password will go through the Internet network and arrive at the server. At the server, a script (program) will receive the user name and password. The script will use the host name that it knows plus the username and password you entered, to login. Of course, the script will include the port, 3306. With that, the script program has determined the account (name).

Users and the Server System
A user is created for the server (MySQL server) and not necessarily for anything (such as a database) that is in the server. After the user has just been created, the user can only login and he cannot do anything else; the user cannot create a database for example. Before the user can do anything else, he has to be given the relevant privileges.

The show grants Statement
If you login to the MySQL server using the mysqlcom client program, and then you enter:

show grants;

you will see the grant statements that have been used to grant you privileges; this is one way to know the privileges you have. If you want to see the privileges of another user, you would type:

show grants for user_name;

This will list the grant statements that granted the user, privileges.

To use the second Show Grants statement above, you need the SELECT privilege for the special MySQL server database called, mysql.

Well, we have come to the end of this part of the series. Let us take a break here and continue in the next part.

Chrys

Broad Network

Related Articles

Account Management Statements in MySQL

MySQL Access Privileges - Part 2

Introduction

Related Links

Comments