Adding and Removing User Access


Adding and Removing User Access

There are several different methods you can use to control user access to your MySQL databases. To begin with, assign a user name and password to every user who accesses your MySQL databases. Then you can use the GRANT and REVOKE commands of mysql to specifically indicate the databases and tables users and host computers can access, as well as the rights they have to those databases and tables.

Caution 

Database servers are common targets of attacks from crackers. While this chapter gives some direction for granting access to your MySQL server, you need to provide much more stringent protection for the server if you are allowing Internet access. Refer to the General Security Issues section of the MySQL manual ( /usr/share/doc/mysql*/manual.txt ) for further information on securing your MySQL server.

Adding Users and Granting Access

Although you have a user account defined to create databases (the root user, in this example), to make a database useful, you might want to allow access to other users as well. The following procedure describes how to grant privileges for your MySQL database to other users.

Note 

If you are upgrading your MySQL from a version previous to 3.22, run the mysql_fix_privilege_tables script. This script adds new GRANT features to your databases. If you don't run the script, you will be denied access to the databases.

In this example, I am adding a user named bobby who can log in to the MySQL server from the localhost. The password for bobby is i8yer2shuz. (Remember that there does not have to be a Fedora or RHEL user account named bobby. So any user on the localhost with the password for bobby can log in to that MySQL account.)

  1. If you are not already connected to a mysql session, type the following command ( assuming the mysql user name of root):

     $  mysql -u root -p  Enter password:  *******  mysql> 
  2. To create the user named bobby and a password i8yer2shuz , use the GRANT command as follows :

     mysql>  GRANT USAGE ON *.*  ->  TO bobby@localhost IDENTIFIED BY "i8yer2shuz";  

    At this point, someone could log in from the localhost using the name bobby and i8yer2shuz password ( mysql -u bobby -p ). But the user would have no privilege to work with any of the databases. Next you need to grant privileges.

  3. To grant bobby privileges to work with the database called allusers , type the following:

     mysql>  GRANT DELETE,INSERT,SELECT,UPDATE ON allusers.*  ->  TO bobby@localhost;  

    In this example, the user named bobby is allowed to log in to the MySQL server on the localhost and access all tables from the allusers database ( USE allusers ). For that database, bobby can use the DELETE, INSERT, SELECT , and UPDATE commands.

  4. To see the privileges that you just granted, select mysql as your current database, then select the db table as follows:

     mysql>  USE mysql;  Database changed mysql>  SELECT * FROM db WHERE db="allusers";  +-------------- +-----------+--------+--------------+------------+-- ---------------+---------------- Host Db User Select_priv Insert_priv Update_priv Delete_priv +---------------+-----------+--------+--------------+------------+-- ---------------+---------------- localhost allusers bobby  Y  Y  Y  Y +---------------+------------+-------+--------------+------------+-- ---------------+------------------ 

    The output here shows all users who are specifically granted privileges to the allusers database. Only part of the output is shown here because it is very long. You can make a very wide Terminal window to view the output if you don't like reading wrapped text. Other privileges on the line will be set to N (no access).

Revoking Access

You can revoke privileges you grant using the REVOKE command. To revoke all privileges for a user to a particular database, use the following procedure:

  1. If you are not already connected to a mysql session, type the following command (assuming the mysql user name of root):

     $  mysql -u root -p  Enter password:  *******  mysql> 
  2. To revoke all privileges of a user named bobby to use a database named allusers on your MySQL server, type the following:

     mysql>  REVOKE ALL PRIVILEGES ON allusers.*  ->  FROM bobby@localhost;  

    At this point, bobby has no privileges to use any of the tables in the allusers databases.

  3. To see the privileges that you just granted, select mysql as your current database, then select the db table as follows:

     mysql>  USE mysql;  Database changed mysql>  SELECT * FROM db WHERE db="allusers";  

    The output should show that the user named bobby is no longer listed as having access to the allusers database. (The results might just say Empty set .)




Fedora 6 and Red Hat Enterprise Linux Bible
Fedora 6 and Red Hat Enterprise Linux Bible
ISBN: 047008278X
EAN: 2147483647
Year: 2007
Pages: 279

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net