Implementing Database in MySQL – Part 8
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 Files\MySQL\MySQL Server 5.1\bin\mysql” -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
 | Xᒽ
 | 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
To arrive at any of the parts of this division, type the corresponding title below in the search box of this page and click Search (use menu if available).
Implementing Database in MySQL – Part 1
Implementing Database in MySQL – Part 2
Implementing Database in MySQL – Part 3
Implementing Database in MySQL – Part 4
Implementing Database in MySQL – Part 5
Implementing Database in MySQL – Part 6
Implementing Database in MySQL – Part 7
Implementing Database in MySQL – Part 8
Implementing Database in MySQL – Part 9
Implementing Database in MySQL – Part 10
Implementing Database in MySQL – Part 11
Implementing Database in MySQL – Part 12
Implementing Database in MySQL – Part 13
Implementing Database in MySQL – Part 14
Implementing Database in MySQL – Part 15
Â
Written by Chrys
Tools For Improving Concentration.
Powerful Techniques To Test, Concentrate And Command Your Mind.
Tools For Improving Concentration.
SQL Pocket
SQL is the language of databases. It’s used to create and maintain database objects, place data into those objects, query the data, modify the data, and, finally, delete data that is no longer needed. Databases lie at the heart of many, if not most business applications. Chances are very good that if you’re involved with software development, you’re using SQL to some degree. And if you’re using SQL, you should own a good reference or two. Now available in an updated second edition, our very
List Price: $ 14.99
Price: [wpramaprice asin="0596526881"]
Find More Mysql Command Products
