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 that 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.html) 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 may 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 that 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 Red Hat Linux 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, you could 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, you can 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 should either make a very wide Terminal window to view the output or learn how to read 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, you could 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, you can select mysql as your current database, they 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.)




Part IV: Red Hat Linux Network and Server Setup