Re: [PHP-DB] Multiple Access to Database - The Answer
[snip] 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 Ethan, Glad you figured it out. What you wrote after that is silly. If someone on this (or any) list posts the reference to the right place in the right manual - which will answer your question, then consider yourself lucky, and well served. No one owes you a darn thing on these lists. And anyway if the referenced place in the manual already spells it out.. then why should someone re-type all that (or copy and paste all that)??! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] 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
[PHP-DB] Multiple Access to Database
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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Access to Database
Ethan, you've send the same question a few days ago. There's no point in asking the same question twice. Matijn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Multiple Access to Database
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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Multiple Access to Database
I believe you would set that up through PHP. Best, Karl On Jan 14, 2012, at 9:55 PM, Ethan Rosenberg wrote: 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
