Creating User and granting Privileges in MySQL

Implementing Database in MySQL – Part 8

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

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 8 of my series, Implementing Database in MySQL. In this part of the series, we see how to create a user and grant rights to the user of a MySQL database. You should have read the previous parts of the series before reaching here, as this is a continuation.

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).

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 command line tool;, connect to the server as DBA and choose the database. To do these, you have to execute the following commands, typing the password, when requested.

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

By now, you should also know how to close the connection, and quit the tool. To do these, you have to execute the commands:

    close
    quit

Now,
- Start the tool; 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 a 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, close the connection, and quit the tool.

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 for a database. Let us stop here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

Creating User and granting Privileges in MySQL

Implementing Database in MySQL – Part 8

Introduction

Related Links

Comments