Create a Database for Virtual Users
There are a lot of uses for MySQL. Here are the steps to creating a user database that could be used with a FTP server, mail server or web server.
Login and create the database.
mysql -p -u root
CREATE DATABASE virtual;
Whatever you choose to call the database does not matter except that you need to be consistent in all of the settings.
USE virtual;
This command will select the virtual database, or whatever you called it, so that the rest of the commands are executed on the database of choice.
This next series of commands will set up permissions for the user virtual and set a password for that user so that you can authenticate.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON virtual.* TO ‘virtual’@'localhost’ IDENTIFIED BY ‘Ut63trg44′;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON virtual.* TO ‘virtual’@'localhost.localdomain’ IDENTIFIED BY ‘Ut63trg44′;
FLUSH PRIVILEGES;
This next command sets up the accounts table with a username and password, note the command goes all the way to the semi-colon.
CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 30 ) NOT NULL ,
`pass` VARCHAR( 50 ) NOT NULL ,
UNIQUE (
`username`
)
) ENGINE = MYISAM ;
INSERT INTO accounts (username, pass) VALUES(‘tom’, PASSWORD(‘tomspassword’));
The next step will involve use of the Pluggable Authentication Module (PAM).
Now check to see if it created the user and password.
SHOW TABLES;
+——————+
| Tables_in_virtual |
+——————+
| accounts |
+——————+
1 row in set (0.00 sec)
mysql> select * from accounts;
+—-+———-+——————————————-+
| id | username | pass |
+—-+———-+——————————————-+
| 1 | tom | *1B8F8905CDA6675FA316A5D50D1B7EC4FBE838B9 |
+—-+———-+——————————————-+
1row in set (0.00 sec)
This confirms the user has been created.