Multiple Access to Database - The Answer

Dear List -

I have a database:

mysql> show tables;
+---------------------+
| Tables_in_hospital2 |
+---------------------+
| Intake3             |
| Visit3              |
+---------------------+


mysql> describe Intake3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Site   | varchar(6)  | NO   | PRI |         |       |
| MedRec | int(6)      | NO   | PRI | NULL    |       |
| Fname  | varchar(15) | YES  |     | NULL    |       |
| Lname  | varchar(30) | YES  |     | NULL    |       |
| Phone  | varchar(30) | YES  |     | NULL    |       |
| Height | int(4)      | YES  |     | NULL    |       |
| Sex    | char(7)     | YES  |     | NULL    |       |
| Hx     | text        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> describe Visit3;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| Indx   | int(4)       | NO   | PRI | NULL    | auto_increment |
| Site   | varchar(6)   | YES  |     | NULL    |                |
| MedRec | int(6)       | YES  |     | NULL    |                |
| Notes  | text         | YES  |     | NULL    |                |
| Weight | int(4)       | YES  |     | NULL    |                |
| BMI    | decimal(3,1) | YES  |     | NULL    |                |
| Date   | date         | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

I want Bob to have access to all tables and fields, with all privileges.
I want John to have read access to  Visit3: fields [Site, MedRec, 
Weight, BMI]

1] How do I do it?
2] In the case that I have two users with write access to a table, how 
do I lock the tables/fields so that the two users can not change the 
same varible at the same time?

Thanks.

Ethan

==============

--> THE ANSWER <---
First - AS ROOT - Create the users and their privileges:

create user 'bob'@'localhost' identified by '1234';
grant all on hospital2.* to 'bob'@'localhost';

create user 'john'@'localhost' identified by '5678';
grant select on hospital2.Visit3 to 'john'@'localhost';
grant select (Site,MedRec,Weight,BMI,Date) on  hospital2.Visit3 to 
'john'@'localhost';


========
logout and login as bob

ethan@rosenberg:~/Desktop$ /usr/bin/mysql -u bob -p
Enter password:
Welcome to the MySQL monitor.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hospital2          |
+--------------------+

use hospital2;

show tables;

+---------------------+
| Tables_in_hospital2 |
+---------------------+
| Intake3             |
| Visit3              |
+---------------------+

mysql> describe Visit3;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| Indx   | int(4)       | NO   | PRI | NULL    | auto_increment |
| Site   | varchar(6)   | YES  |     | NULL    |                |
| MedRec | int(6)       | YES  |     | NULL    |                |
| Notes  | text         | YES  |     | NULL    |                |
| Weight | int(4)       | YES  |     | NULL    |                |
| BMI    | decimal(3,1) | YES  |     | NULL    |                |
| Date   | date         | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+


logout and login as john

ethan@rosenberg:~/Desktop$ /usr/bin/mysql -u john -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

use hospital2;

mysql> show tables;
+---------------------+
| Tables_in_hospital2 |
+---------------------+
| Visit3              |
+---------------------+

--> NOTE only access to one table <--

mysql> describe Visit3;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| Site   | varchar(6)   | YES  |     | NULL    |       |
| MedRec | int(6)       | YES  |     | NULL    |       |
| Weight | int(4)       | YES  |     | NULL    |       |
| BMI    | decimal(3,1) | YES  |     | NULL    |       |
| Date   | date         | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

--> Compare this to bob. See above <--

mysql> INSERT INTO Visit3 (Site) VALUES(15);
ERROR 1142 (42000): INSERT command denied to user 'john'@'localhost' for 
table 'Visit3'

--> NOTE: Readonly privileges <--

The explanation of Lock Tables is long and complicated.  It can be found 
here:

http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html

I hope this clarifies the issue.

PS:

I respectfully request that if a user asks a question, that an explicit 
answer be given, not just a reference to the manual.  It could be then 
followed by a manual reference.  The user then has his answer and can 
look up more detail in the manual.


Thanks.

Ethan


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php