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
