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