zaptech.org - MySQL Notes

MySQL Notes

Contents

First Blush
Ok Now What
Database Users
Web Page Access

Elsewhere

HOWTOlabs.net: phpMyAdmin setup
phpMy Admin - server side tool to maintain databases from a web interface

First Blush

Installing MySQL

Important Concepts MySQL right after installation typically is quite insecure MySQL usernames having nothing to do with Unix usernames (Section 4.3.2). Database application usernames usually have nothing to do with MySQL usernames All data is typically stored in /var/lib/mysql/

Databases
MySQL installation will setup two databases: mysql & test . test is an empty database with unrestricted access - it can be deleted. mysql is a required database and is only accessible to root user, it contains information for all databases, users, and the restriction/security parameters. The show databases command (example shown below) is a good way to see the database manifest. The special mysql database contains critial information that dictates user privledges and allows administration of custom databases.

Users
Default MySQL installation will setup a special root user and a default user mode for when no user is specified. All users and their respective privledges are maintained in the user table of the mysql database (example user table shown below).

$ mysql -u root
mysql> show databases;
+-------------+
| Database    |
+-------------+
| mysql       |
| test        |
+-------------+

mysql> use mysql
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+

mysql> select * from user;
+--------------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| Host               | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
+--------------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| localhost          | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          |
| jabber.zaptech.com | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          |
| localhost          |      |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          |
| jabber.zaptech.com |      |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          |
+--------------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
More details about how this table controls access to databases available at 4.2.9 Access Control, Stage 1: Connection Verification

mysql> select * from db;
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| Host | Db      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+
| %    | test    |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          |
| %    | test\_% |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+

mysql> quit

Ok Now What - creating/dropping databases, creating/dropping tables, adding/dropping table fields

Database Users - securing database access by creating users with passwords

Give root user a password

$ mysql -u root
mysql> use mysql
mysql> update user set Password=PASSWORD("mypassword") where Host="localhost" and User="root";
mysql> update user set Password=PASSWORD("mypassword") where Host="jabber.zaptech.com" and User="root";
mysql> FLUSH PRIVILEGES;
mysql> \q

Now to log into MySQL as root you need to tell it to ask for a password (-p)

$ mysql -u root -p
[passwd]
mysql> \q

Create, update, and delete a MySQL user

$ mysql -u root -p
[passwd]
mysql> use mysql
mysql> insert into user (Host, User, Password, Select_priv)
    -> values ("localhost", "silly", PASSWORD("1234"), "Y");
mysql> update user set Password = PASSWORD("abcd") where User = "silly";
mysql> delete from user where User = "silly";
mysql> \q

Limit database access to a specific MySQL user. NOTE: mysql: user table OVERIDES settings in the mysql: db table. It is wise to never grant privledges to any user except root in the mysql: user table. Following assumes user silly and database foo already exist. The % value in the Host field means that MySQL user silly can access database foo from any IP address

$ mysql -u root -p
[passwd]
mysql> use mysql
mysql> insert into db (Host, Db, User, Select_priv)
    -> values ("%", "foo", "silly", "Y");
mysql> \q

Web Page Access

Use PHP server side HTML tags to dump database content to a web page

<HTML>
<BODY>MySQL Database foo Contents

<?php
$query = "select * from artists";
mysql_connect("localhost", "silly", "abcd") or die("mysql_connect() failed");
mysql_select_db("foo") or die("mysql_select() failed");
$result = mysql_query($query) or die(mysql_error());
$colqty = mysql_num_fields($result);

echo "<P>command: $query\n\n";

echo "<P><TABLE CELLPADDING=2 CELLSPACING=0 BORDER=4><TR>\n";
for ($i = 0; $i < $colqty; $i++) {
        echo "<TH>". mysql_field_name($result, $i). "</TH>\n";
        }
echo "\n\n</TR>";

while ($row = mysql_fetch_row($result)) {
        echo "<TR>";
        for ($i = 0; $i < $colqty; $i++) {
                echo "\n<TD>";
                if (isset($row[$i])) echo $row[$i];
                else echo "-";
                echo "</TD>";
                }
        echo "\n\n</TR>";
        }
echo "</TABLE>";
?>

</BODY>
</HTML>

Typical MqSQL 'commands'

SELECT * FROM `appointments` WHERE `costfix` > 0 ORDER BY `date`
SELECT * FROM `appointments` WHERE `time` IS NULL ORDER BY `date` DESC
SELECT * FROM `appointments` WHERE `time` IS NULL ORDER BY `appointments`.`date` ASC LIMIT 0 , 30