Monday, September 15, 2008

How to create a MySQL database and associated USER with the right permissions, to be accessed externally - "Simple command" series

Purpose:
Many times we have to deal with databases other than Oracle and even being those DB simpler, they waste DBA's resources. In first place it happens because we have to seek reference manuals and regarded documentation. In other hand, those DB could have a different approach and behavior than Oracle.
In this simple case and like a first post of a similar "simple command" series, I'll add the necessary command order and description to create a "testdb" on MySQL with the right permissions to be accessed by our Apache frontends. We'll use a specially generated user to connect and not just *.* permissions on root.

Like you know, on MySQL there are many levels for granting privileges, we'll work only at global and table level. For a detailed guide, please visit the Mysql documentation
http://dev.mysql.com/doc/refman/5.0/en/grant.html

Versions: MySQL 4/5

Description:
Connecting locally to MySQL:
mysql -u -p (it asks password)
(see at the end, how to create an admin user other than root)

ie:
[root@host ~]# mysql -u admin -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9218
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
CREATE DATABASE testdb CHARACTER SET = utf8;

mysql> GRANT
USAGE ON *.* TO 'testdb'@'10.140.134.%' IDENTIFIED BY 'testdbpass';
Note: 10.140.134.% is the Apache frontend network and could be more than one server.

mysql> GRANT ALL PRIVILEGES on `testdb`.* to 'testdb'@'10.140.134.%';

or more grained ...

mysql>
GRANT SELECT,INSERT on `testdb`.* to 'testdb'@'10.140.134.%';

Command list:
___________________________________________________
>mysql -u admin -p
>CREATE DATABASE testdb CHARACTER SET = utf8;
>GRANT USAGE ON *.* TO 'testdb'@'10.140.134.%' IDENTIFIED BY 'testdbpass';
>GRANT ALL PRIVILEGES on `testdb`.* to 'testdb'@'10.140.134.%';
___________________________________________________

If we've made a mistake or we want to clear everything:
>DROP USER 'testdb'@'10.140.134.%';
>DROP DATABASE testdb;

Query results (mysql.user):
>SELECT USER, HOST FROM mysql.user ORDER BY 1, 2;
>SHOW DATABASES;

how to create an admin user other than root
Please, be careful on production environments, it must be reviewed an tested in advance.

At Global level:

GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL ON *.* TO 'admin'@'localhost';
GRANT GRANT OPTION ON *.* TO 'admin'@'localhost';


Note: "ALL" gives everything less than "GRANT OPTION"

Links, References:
http://dev.mysql.com/doc/refman/5.0/en/grant.html