08- MySQL

Databases- MySQL

• CentOS 5.5 / MySQL 5.0.77
• Windows Server 2008 / MySQL 5.5.11 (Win32)
• OpenSuSE 11.3 / MySQL 5.1.46

Introduction

There are a number of reasonable databases that can be used in a variety of situations. In these notes we will focus our attention on the use of MySQL.

These notes assume that you are familiar with databases and SQL in general, and will focus their attention solely on the administration of a MySQL database, with an obvious focus on security.

These notes will carefully follow the MySQL Reference manual, available
at http://dev.mysql.com/doc/refman/5.5/en/index.html (for 5.5). The manuals for other versions are also available there. References to the manual will be to the manual for version 5.5; these sections may be in slightly different locations for other versions of MySQL.

Installation

CentOS 5.5
A fully functional MySQL database is set up on the CentOS virtual machine that was provided on labshare as a starting point for the snort system.

The firewall is set up to block the MySQL port (3306); this port can be opened in the usual fashion (System -> Security Level and Firewall -> Other Ports -> 3306 (TCP).)

Connect to your local MySQL server by running the command mysql from a command prompt as a root user.

Windows Server 2008
The windows installer for MySQL 5.5.11 (Win 32) is available on the labshare. It should function on all recent windows operating systems. It is simple to install using the “typical” settings. Once it is installed, MySQL will run the MySQL Server Instance Configuration Wizard. Select “Detailed Configuration”. For Server Type, either “Developer Machine” or “Server Machine” are appropriate for our class exercises. In this example, we select “Server Machine”.

MySQL allows for the use of different mechanisms for store the data- InnoDB and MyISAM. In general, InnoDB is preferred, as it has transaction support, while MyISAM does not. MyISAM om the other hand, is somewhat faster. MyISAM is the default for MySQL prior to 5.5, with InnoDB the default for 5.5+. In our example, we will set MySQL to use multifunctional databases. The InnoDB datafiles will be placed in the default installation path.

Given our lab environment, it is unlikely that your database will require more than 20 concurrent connections. The default port for MySQL is TCP 3306; you can open the
firewall at this point in the installation. Use the standard character set. Set MySQL to start as a windows service. You almost certainly want to include the MySQL bin directory in the Windows path.

Set the root password for the database. If you want to be able to administer your MySQL system remotely, then you will need to either enable root access from remote machines, or create non-root users with sufficient privileges. Why would you want to create an anonymous account?

Connect to your local MySQL server by either running Start -> Programs -> MySQL -> MySQL Server 5.5 -> MySQL Command Line Client, or from a command prompt, run
mysql -u root -p

OpenSuSE 11.3
The MySQL community server is already installed but not running on the default OpenSuSE machine from the labshare. To start it, run YaST -> System Services (Runlevel) and start the service.

The firewall blocks access to the default port (3306); it can be opened in the usual fashion (YasT -> Firewall -> Allowed Services -> MySQL Server -> Add). Connect to your local MySQL server by running mysql as root from a command prompt.

Connecting to MySQL

The primary multipurpose tool to connect to a database is the mysql client. When run without any options this sets:

• The hostname to be localhost. Note that if this is a linux client, this means more to MySQL than just the hostname.
• The user name is your linux user name (on linux) or “ODBC” on Windows
• No password is sent
• No default database is selected.

To set the host name of the server to which you want to connect use the -h option. You can specify the host by its DNS name, or by its IP address, or by specifying localhost.

To set the user name, use the -u option

To specify that you want to enter a password, pass the -p option. This will cause the client to prompt you for a password. If you want to include the password in the command (which you DO NOT WANT TO DO) you follow the -p option with the password, without spaces. As an example, the command

$ mysql -u user -ppass

specifies the user “user” and the password “pass”.

To specify the default database, simply specify that last. The command

$ mysql -u user -h db.towson.edu -p class

attempts to connect to the MySQL server with hostname db.towson.edu as the user “user” and load the database “class”; the client will prompt the user for the password.

Connections to MySQL can be made in a number of different ways.

• Via TCP/IP. This is used for most remote connections, and can be used for local connections.
• Via Unix sockets. Available only locally for linux hosts.

  If the host name is specified localhost, then by default the connection is via a Unix socket. If you want to connect to localhost via TCP/IP, specify the hostname as 127.0.0.1.

• Via a named pipe connection; available on windows only.
• Via a shared memory connection to a local server; available on windows only.

The particular method can be specified on the command line via the –protocol={TCP|SOCKET|PIPE|MEMORY} option.

To see more details about the connection that you have made to your server, you can use the command

mysql> status

or the abbreviation

mysql> \s

See also the MySQL manual, 4.2.2. Connecting to the MySQL Server for more details.

Users & Privileges

MySQL uses accounts to determine who can authenticate to the database. Though these accounts may share the same name(s) as accounts in the operating system (e.g. root) the MySQL accounts are unrelated to the operating system level accounts.

When authenticating a user, MySQL uses three factors:

• The user name
• The password
• The hostname (that is the source of the connection attempt).

It is better to think of the pair (user name,hostname) as a single object. It is possible to have two different accounts with the same user name, provided that they have different hostnames.

To create a user, one approach is to use the CREATE USER command. The command

mysql> CREATE USER 'bill'@'localhost' IDENTIFIED BY 'pass';

creates:

• user name: bill
• hostname: localhost
• password: pass

The command

mysql> CREATE USER 'bill'@'localhost';

creates:

• user name: bill
• hostname: localhost

but leaves the account with a blank password.

If you want to specify that an account can log in from any host then you can replace the host with the wildcard- ‘%’, so

mysql> CREATE USER 'ted'@'%' IDENTIFIED BY 'pass';

creates an account that can log in to the server from any host with the password “pass”.

The wildcard ‘%’ can be used for portions of a hostname, e.g. ted@’%.towson.edu’.

The existence of wilcards in hostnames can be a source of significant complication. Create the following three users:

mysql> CREATE USER frodo@'%' IDENTIFIED BY 'pass1';
mysql> CREATE USER frodo@'%.tu' IDENTIFIED BY 'pass2';
mysql> CREATE USER frodo@host.tu IDENTIFIED BY 'pass3';

If frodo connects from the machine host.tu, then which password is to be provided? After all, the hostname matches all three choices! The process is that the system will sort the hosts that apply to the user in order from most specific to most general, and will use the first (most-specific host) in that list.

You can always see what account was used for authentication, you can run the query

mysql> SELECT CURRENT_USER();

to obtain that information. See also the manual, section 5.4.4.

Only users with the global CREATE USER privilege can create a user. For details, see the MySQL manual, section 12.4.1.1.

To drop a user, use the DROP USER command. To drop the user bill@localhost that we created, we simply run

mysql> DROP USER 'bill'@'localhost'

If the hostname is not specified, the hostname is assumed to be ‘%’.

A user who is logged in will not have their session disrupted if their account is deleted. Once they leave their open session, they will be unable to subsequently log back in. Only users with the global CREATE USER privilege can drop a user. For details, see the MySQL manual, section 12.4.1.2

To rename a user, use the RENAME USER command. Suppose that we want to change our user ted@’%’ we created so that they can only log in from the host db.class; we can use the syntax

mysql> RENAME USER ted TO ted@'db.class'

Note that if the host is not specified, then the wildcard ‘%’ is assumed. Only users with the global CREATE USER privilege can drop a user. For details, see the MySQL manual, section 12.4.1.4

To change the password for a user, use the SET PASSWORD command. Suppose that we want to change the password for the account ted@db.class to something more complex- we can simply run the command

mysql> SET PASSWORD FOR ted@db.class = password('password1!');

and now the password has the value “password1!”. See the MySQL manual, section 12.4.1.6 for more details.

In a linux system, the history of commands entered into the mysql client is stored in the file ~/.mysql_history. All of the password and account commands noted above will be stored in this file. Access to this file should be strongly protected. See the MySQL manual, section 4.5.1.3.

MySQL provides a number of different privileges that can be assigned to users. To view the list of all such privileges, run the command

mysql> SHOW PRIVILEGES;

The precise list of privileges varies between versions of MySQL. See also 12.4.5.27 for the syntax, and 5.4.1 for the list of privileges.

Some of the important privileges include:

• ALL: This does not mean all, but does mean all privileges other than GRANT OPTIONS
• ALTER: Lets the user rename or change the structure of tables; it requires the INSERT and CREATE privileges. Note that the ALTER privilege should be carefully controlled, as it allows the user to bypass some privilege requirements if the user
  renames the table appropriately.
• CREATE: Lets the user create new databases.
• CREATE USER: Enables CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
• DELETE: Allows the user to delete rows from a database.
• DROP: Allows the user to delete a database, table, or view. Since MySQL stores privilege data in the database mysql, a user with DROP privileges on that database can eliminate all stored privileges.
• FILE: This allows the user to read and write files on the host system with the privileges of the MySQL server. For example, a user with FILE privileges can read the contents of /etc/passwd on the host. This can be bad if the user is connecting to the database remotely and is not meant to have this level of access.

  How to do this?

  $ mysql> USE DATABASE test;
  $ mysql> CREATE TABLE t1(d text);

  Here we create a temporary table to store the results of the file action; the table has one column (named d) of type text.

  $ mysql> LOAD DATA INFILE '/etc/passwd' INTO TABLE t1;
  $ mysql> SELECT * FROM t1;

  This will reproduce the contents of the file /etc/passwd. Try this on a remote server!

• GRANT OPTION: This allows the user to grant and revoke privileges for other users; its use is limited to privileges possessed by the grantor.
• INSERT: Allows the creation of additional rows in a database.
• PROXY: Available only in MySQL >= 5.5.7. Allows one user to impersonate another. It is described in the manual, section 5.5.7.
• RELOAD: Allows the use of the FLUSH commands, most notably the FLUSH PRIVILEGES statement.
• SELECT: Allows the user to retrieve rows from a database. This is slightly different than allowing a user to run a SELECT query, as some SELECT queries do not obtain their data from a table; consider the query

  mysql> SELECT curdate();

  which returns the current date; this can be run by users without SELECT privileges as no rows are returned from any database.

• SHOW DATABASES: Allows the user to see all databases on the system. Without this, a user will only see the databases for which they have privlieges.
• UPDATE: Allows rows in a database to be updated.
• USAGE: Equivalent to no privileges. If you create a new user the only privilege they initially receive is the USAGE privilege, which does not grant them the ability to do anything.

To view the set of privileges for an account, you can run the command

mysql> SHOW GRANTS FOR ted@'%.class';

The syntax for the SHOW GRANTS statement is documented in 12.4.5.22. There is no simple way to list the grants for all users at once.

Privileges are assigned to a user via the use of the GRANT command. This is a complex command; read the documentation at 12.4.1.3. Any user can use the GRANT command, provided their account has the GRANT_OPTION set. A user can only grant privileges that they themselves possess.

To assign global privileges, use * as the wildcard for both the database and the table name:

mysql> GRANT ALL ON *.* TO ted@'%.towson.edu'

These privileges are stored in the mysql database, in the table mysql.user. If the user does not exist, the GRANT command will create the user. A password can be specified at the same time the GRANT command is used:

mysql> GRANT ALL on *.* to frodo@'%' IDENTIFIED BY 'onering';

If the account frodo@’%’ does not exist, it will be created with the password ‘onering’. If the account does exist, then the password will be changed.

To assign privileges to just a single database, only use the wildcard for the table name.

To see this process in action, let us first create a database:

mysql> CREATE DATABASE sales;

Let us also add some tables:

mysql> create table customers(
-> id INT NOT NULL AUTO_INCREMENT,
-> first_name VARCHAR(20),
-> last_name VARCHAR(20),
-> street_address VARCHAR(60),
-> city VARCHAR(20),
-> state_abrv CHAR(2),
-> zip UNSIGNED DECIMAL(5),
-> PRIMARY KEY(id));

mysql> create table creditcards(
-> id INT NOT NULL AUTO_INCREMENT,
-> number varchar(16),
-> userid INT,
-> PRIMARY KEY(id),
-> KEY(userid),
-> FOREIGN KEY(userid) REFERENCES customers(id));

Now we can assign SELECT privileges to ted as follows:

mysql> GRANT SELECT on sales.* TO ted@'%.towson.edu'

These privileges are stored in the mysql database, in the table mysql.db

To assign privileges to just a single table within a database, specify both the database and the table:

mysql> GRANT INSERT,UPDATE on sales.customers<
-> TO ted@'%.towson.edu';

This data is stored in mysql.tables_priv

To assign privileges to a column in a database, follow the privilege with a list of columns within parantheses:

mysql> GRANT SELECT (id,userid) ON sales.creditcards
-> TO bill@'%.class';

If you want to provide a user with the ability to grant privileges, use WITH GRANT OPTION.

To remove privileges from a user, use the REVOKE command. This has the same general syntax as the GRANT command; see 12.4.1.5 for details. The revoked privilege must be the same as the granted privilege.

The mysql database

The default installation of MySQL keeps information about the users and the system in a database called mysql. To select this database, execute the command

mysql> USE mysql;

View the set of tables in this database with the command

mysql> SHOW TABLES;

The precise collection of tables varies with the version of MySQL.

The first table of interest to us is the user table; view its structure my running

mysql> DESCRIBE user;

The precise structure of the table varies with the version of MySQL. It contains details about user accounts, global privileges, and other per-user admiistrative data for SSL.

To see all of the users on your system, run the command

mysql> SELECT user,host,password FROM user;

The precise collection of default accounts depends on how MySQL was installed. Notice that the OpenSuSE and the CentOS versions contain a number of accounts. Accounts without a user name allow any user to use that account. Accounts without passwords allow access without passwords. The password data is a hash of the password; different versions of MySQL use different hasing algorithms. See section 5.3.2.3.

Would it be helpful to know the password hash of some of the commonly used passwords? Try

mysql> SELECT PASSWORD('whateveryouwant');

If your system has users that you feel are unecessary, then they should be deleted using the DROP USER syntax described above. To drop the anonymous user on localhost, you can use the syntax

mysql> DROP USER ''@localhost;

Changes in the privileges of users can be made by directly editing the appropriate table in the mysql database. Changes made in this fashion do not take effect immediately; you need to run the command FLUSH PRIVILEGES for these changes to
take effect. See also 12.4.6.3.

The table mysql.db contains database level privileges.

The table mysql.tables_priv contains the per-table and the per-column privilege assignments.