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