Creating User and granting rights in MySQL

Implementing Database in MySQL – Part 8

Forward: In this part of the series, we see how to create a user and grant rights to the user in a MySQL database.

By: Chrysanthus Date Published: 6 Aug 2012

Introduction

This is part 8 of my series, Implementing Database in MySQL. I assume you have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we see how to create a user and grant rights to the user in a MySQL database.

We are still dealing with data definition. Remember, data definition deals with creating of tables, altering tables, dropping tables, creating of indexes and giving different users, privileges to different tables. We have seen how to create tables and give them indexes. In the previous part of the series we saw how to alter and drop tables. In this part we shall see how to create a user account (user name and password) and grant him certain privileges (permission).

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Creating a User
Creating a user means creating a user account. In the simplest form an account consist of a user ID and a password. The database administrator (DBA) has the right to create a new user. In this series, you are the database administrator with userID, root, and password, sql. You can create other users (people’s account) who will access the database. After you create a user, he has no privilege, and he cannot access the database (see below).

In simple terms the syntax of the SQL statement to create a user is:

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

The database administrator (you at the moment) has the privilege to create a user.

The user-name is the name the user gives you. The password is the secret word the user gives you. We shall create a user with the user-name, newguy and password new44.

You can drop (remove) a user. The syntax to that is:

DROP USER user-name

The database administrator (you at the moment) has the privilege to drop a user.

By now you should know how to start the server, connect to the server as DBA and choose the database. To do these, you execute the following commands, typing the password, when requested.

cd c:
"C:Program FilesMySQLMySQL Server 5.1binmysql" -u root -p
USE wholesale;

By now, you should also know how to drop the connection, stop the database and stop the server. To do these you execute the command:

QUIT

- Start the server; connect to the server and choose the wholesale database.
- You will create a new user account. Execute the following SQL statement (the password is in single quotes):

CREATE USER newguy IDENTIFIED BY 'new44';

All SQL statements end with semicolon.

Granting Permissions
When you create a user he cannot access the database. You need to grant him certain privileges on what he can and cannot do. The SQL GRANT statement enables system administrators (DBA) to grant privileges to MySQL user accounts. I will give you the complete syntax. Read it; you will not fully understand it. After that I will tell you how to grant privileges to the user you have created.

The complete MySQL Grant Statement syntax is:

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

In many cases you the database administrator will want to grant certain users, the privilege to Delete rows in a table, and/or to Insert rows in a table and/or to Update rows in a table and/or to Select (view) rows of a table. In the above syntax, object_type can mean a table.

This complete syntax is not well documented. Know that priv_type in the syntax can mean DELETE and/or INSERT and/or UPDATE and/or SELECT.

You can read this complete syntax and other MySQL syntaxes in the same way I read the ALTER TABLE syntax in one of the previous parts of the series.

- Type and execute the following SQL statement to give the user, newguy certain privileges.

GRANT DELETE, INSERT, UPDATE, SELECT ON Products TO newguy;

Note that in the statement, the privileges are separated by commas.

- Now, drop the connection, stop the database and stop the server.

There is a lot more to granting rights than what I have given in this tutorial. I intend to write a whole series on granting rights and security. What I have given you here is enough to get you working.

We have come to the end of the basics of data definition in a database. Let us stop here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

Creating User and granting rights in MySQL

Implementing Database in MySQL – Part 8

Introduction

Related Links

Comments