Aus MHC-Wiki

The more restrictive permissions managemet is the more secure it is. This HowTo is written with security in mind.

Inhaltsverzeichnis 1 Permissions in MySQL 2 Enabeling network access 3 How to set Permissions 3.1 User and Database table structure 3.2 Granting users access to MySql 3.3 Granting access to databases 4 Omnis specific annotation

Permissions in MySQL

Permissions in MySQL can be set in two levels:

• User level
• Database level
• Host level
• Table level
• Column level

This HowTo does not cover host, table and column level, only user and databse level.

Easy but unsecure is to grant all permissions to a user. Better and more secure is to grant "connect" to the user and then grant privileges to the user database by database. The adavantage is: If a user account is copromised only the databese the user has access to have to be cosidderd compromised too.

An indepth discussion of the leveles you will find here: http://dev.mysql.com/doc/refman/5.1/en/privileges.html

Enabeling network access

Instead of "skip-networking" the default is now to listen only on localhost which is more compatible and is not less secure. On old setups comment "skip-networking" out to enable remote network access. On systemes with MySQL > 3 jut comment out the follwoing line in my.cnf

# bind-address          = 127.0.0.1

With this MySQL binds to "*" and listens on all IP's assigned to the Server. If you do not need connections form localhost you also can specify an other IP to bind to by e.g.

bind-address          = 192.168.1.1

But it's mot likely that you do NOT want this!

How to set Permissions

User and Database table structure

There are two tables (for this howto) in the "mysql" database where the permissiones are stored:

• user
• db

Both tables have a "host", "user" and "password" row and several permission columns. A common pitfall is to ignore the "host" row. MySQL decides by the value of this row form which host a user i allowed to connect from. An other pitfall is that "localhost" has to be granted seperately even if a wildcard in the hostname is used. The tables look like this:

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | user1    | ...
| localhost | root     | ...
| localhost | user1    | ...
+-----------+----------+-

"host" is used in the following "CREATE" and "GRANT" statemens. "host" can be a single host (name, IP), a subnet, "%" for any host or blank. When it is blank the "host" table is consultetd but this is not covered by this howto. When you can not connect form a remote host then check if you host matches the "host" restriction of this statement. Most common is to use "%".

For details on the "host" part of the following statements see: http://dev.mysql.com/doc/refman/5.1/en/connection-access.html

Granting users access to MySql

To create an unprivileged user just create it:

CREATE USER 'user'@'host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Keep in mind: This user is able to connect to mysql but he has NO privileges for any databse.

Granting access to databases

To make a database accessible to a user grant the desired privileges to him for the database. In most cases you will use "ALL" privileges.

GRANT ALL ON database.* to 'user'@'host';
FLUSH PRIVILEGES;

Omnis specific annotation

Omnis prior to version 4.3 requires access to the "mysql" table to be able to make cnnections to the "mysql" database. The problem is that Omnis always wants to connect to the "mysql" database and then changes to the database you want to connect to. This missbehaviour ended with Omnis 4.3. Just insert an apropriate record in the "user" table and flush privileges:

GRANT USAGE ON 'mysql' TO 'user'@'host';
FLUSH PRIVILEGES;