Permissions and Security Under MySQL
| I l @ ve RuBoard |
| First, if this database will hold any sensitive information, you need a password to access the root account. Otherwise, anyone can change and grant permissions on every database and table inside MySQL without a password. To do this, issue the following commands from the console: mysqladmin -uroot password <password> Here, <password> is the password that you want to assign to the root user . Access can be controlled on a site, database, or table basis to a combination of specific users, hosts, and users at hosts . Users are not explicitly created, but are created using grant statements. For example, now that you have a database called TestDB, create a user called DbUser who has permission to read, but not write from the tables in that database. Notice that, with a password in place, you need to enter it to gain access for root. [turner@linux tmp]$ mysql -uroot -p TestDB Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 542 to server version: 3.23.41 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. These next lines are read as: "Grant select privileges on all tables in the TestDB database to DbUser1 if that user is connecting from the local host and uses the password pw1." mysql> grant select on TestDB.* to DbUser1@localhost identified by 'pw1'; Query OK, 0 rows affected (0.00 sec) The next lines basically say, "Grant insert privileges on the specific table TestTable in the database TestDB to DbUser2 on localhost identified by pw2." mysql> grant insert on TestDB.TestTable to DbUser2@localhost identified by 'pw2' ; Query OK, 0 rows affected (0.01 sec) The following lines are read as: "Grant insert, update, and select on all database tables in TestDB to DbUser3." mysql> grant insert,update,select on TestDB.* to DbUser3@localhost identified by 'pw3'; Query OK, 0 rows affected (0.00 sec) Finally, these next lines can be read as: "Grant all privileges on all TestDB tables (including table creations and destruction) to DbRoot. mysql> grant all on TestDB.* to DbRoot@localhost identified by 'pw4'; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye Start by testing DbUser1. As expected, the user can read from tables but can't insert into them. [turner@linux tmp]$ mysql -uDbUser1 -p TestDB Enter password: Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from TestTable; +---------+---------+ String1 String2 +---------+---------+ Foo Bar +---------+---------+ 1 row in set (0.00 sec) mysql> insert into TestTable values ('Foo1', 'Bar1'); ERROR 1142: insert command denied to user: 'DbUser1@localhost' for table 'TestTable' mysql> quit Bye DbUser2 has a unique situation. This user can insert records into one specific table but can't read them back. [turner@linux tmp]$ mysql -uDbUser2 -p TestDB Enter password: Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> insert into TestTable values ('Foo1', 'Bar1'); Query OK, 1 row affected (0.00 sec) mysql> select * from TestTable; ERROR 1142: select command denied to user: 'DbUser2@localhost' for table 'TestTable' mysql> quit Bye DbUser3 has a somewhat more normal existence. This user can read, write, and update any table in the database but can't create a new one. [turner@linux tmp]$ mysql -uDbUser3 -p TestDB Enter password: Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from TestTable; +---------+---------+ String1 String2 +---------+---------+ Foo Bar Foo1 Bar1 +---------+---------+ 2 rows in set (0.00 sec) mysql> insert into TestTable values ('Foo2', 'Bar2'); Query OK, 1 row affected (0.00 sec) mysql> create table TestTable2 ( -> String3 CHAR(50)); ERROR 1142: create command denied to user: 'DbUser3@localhost' for table 'TestTable2' mysql> quit Bye Only DbRoot can do everything, but even this user's power is limited to the TestDB database. If DbRoot tried to wield power elsewhere, she'd be turned back. [turner@linux tmp]$ mysql -uDbRoot -p TestDB Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 546 to server version: 3.23.41 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table TestTable2 ( -> String3 CHAR(50)); Query OK, 0 rows affected (0.00 sec) mysql> quit Bye In addition to using the user@localhost format, you can use user@foo.bar.com to allow a user to come in from a specific site (via either JDBC or the mysql client). This is commonly used in two-tier and three- tier application architectures, in which the database server and the applications server are placed on separate physical machines. This is often done because the performance tuning needed to make a database server run efficiently is often not the same as the tuning needed to run a Web server well. This also allows multiple Web servers to be operated against a common database. Other format variants include these:
It is very important to restrict permissions properly. If you're creating a user who only will use JDBC connections from a specific machine, put that machine restriction in the grant statement. You can see what privileges a user has by using the show grants command, as shown here: mysql> show grants for DbUser3@localhost; +---------------------------------------------------------------------------------------+ Grants for DbUser3@localhost +---------------------------------------------------------------------------------------+ GRANT USAGE ON *.* TO 'DbUser3'@'localhost' IDENTIFIED BY PASSWORD '78a36c1c267f0583' GRANT SELECT, INSERT, UPDATE ON TestDB.* TO 'DbUser3'@'localhost' +---------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) USAGE is simply the ability to connect to the database. |
| I l @ ve RuBoard |
EAN: 2147483647
Pages: 203