MySQL Account Management Statements
MySQL Access Privileges - Part 2
Division 7
Forward: In this part of the series, we look at MySQL Account Management Statements.
By: Chrysanthus Date Published: 8 Aug 2012
Introduction
The mysql Program
The mysql program called, mysql, 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 mysql program if you have covered the prerequisite studies from this blog. If the mysql program is in the same computer as the MySQL server, then that computer is called localhost. In this series you will use mysql 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, better typed as, 'you'@'localhost'. 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. 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 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. 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 this 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'@'host_name' [identified by 'password']
User_name is the user’s name such as, ‘john’. host_name is the name of the computer that has the client program. It can be one word e.g. ‘lion’ or it can be a domain name e.g. ‘yahoo.com’. In this series, it is localhost because the client program (mysql) and the server are in the same computer. The square bracket content in the syntax is optional at the creation of the user, but not later. You can have something like:
create user 'you'@'localhost';
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'@'localhost' identified by 'you123';
For this tutorial, assume that this is your (the reader) 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 (statement).
There is a difference between user name and account name. In the above example, the user_name is, “you” and the account name is you@localhost (better typed as 'you'@'localhost'). So your user account name is, 'you'@'localhost'.
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 mysql 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'@'localhost, the CREATE USER and GRANT OPTION privileges as follows:
grant CREATE USER, GRANT OPTION on *.* to 'you'@'localhost';
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'@'localhost' 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, after logging in (using the mysql client program for example) the user you'@'localhost can now create his own user as follows:
create user 'john'@'localhost' identified by 'john123';
The user, you@'localhost', can then go on to grant the user john'@'localhost', privileges. If you@'localhost' want him to be able to create new users, you@'localhost' , after logging in, would type:
grant CREATE USER on *.* to 'john'@'localhost';
If you want him to be able to give some or all of his own privileges to the users he creates you@'localhost would type:
grant GRANT OPTION on *.* to 'john'@'localhost';
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'@'localhost';
or
grant CREATE USER on *.* to 'john'@'localhost' 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.
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'@'localhost';
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'@'localhost';
In this case you cannot create a user, but if you have 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.
You cannot give to someone privileges that you do not have. The root cannot give to you privileges the he does not have. 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 starts having 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'@'localhost';
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 to use. You will see it 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'@'localhost';
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'@'localhost';
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'@'localhost' = password('john123');
Note that the Set Password statement has an assignment operator. In simple terms the syntax is:
set password for 'user_name'@'host_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.
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 (non-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'@'localhost' to 'jeff'@'yahoo.com';
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 mysql client program, and is using the windows operating system, then you can login as the following command line illustrates:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -h localhost -u you –pyou123
This command line has the host name and user name separated, without the @ symbol; it still has the account name. It also has the password attached to the switch, -p. This is a command for the operating system command prompt, and it is not a SQL statement for the mysql client program; that is why its form is different from the SQL statements.
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 program will join the host name that it knows with @ and the user name. With that, the script program has determined the account name. It will then use the account name and the password received to actually login to the MySQL server.
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 mysql 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@host_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. It is the root user who normally has this privilege; you can ask him for it, but he will hardly give you. We have the mysql client program and the mysql database. Both components have the same name and they are installed during the general installation of the server. Do not confuse between the two components.
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
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT