| Search website: |
|
Advanced Server Software
|
MySQLAuth External Authentication Module for SurgeMailAn external authentication module that allows SurgeMail to do user lookups on a MySQL Database.
A step by step example of creating a MySQLdatabase on NTBelow is an unedited example of setting up a MySQL database to test on a Windows NT box.
1. Downloaded from a MySQL
mirror site (they like it when you use a mirror), 2. Edited example file, d:\mysql\my-example.cnf
that came with it, so that, all c:'s were changed to d:, and saved as,
3. In order to install as service on NT... d:\mysql\bin>mysqld-shareware --install NB: when I then tried to start service in ControlPanel|Services, it failed with a message saying that the process had stopped itself. so I entered... d:\mysql\bin>mysqld-shareware --debug NB: If I ran it from a command
line, it started and could not be stopped, even with a CTRL-C - I had
to kill it inprocess
list of task manager. In the bin dir was an exe mysqlshutdown, which just
popped up a window (Icon in system tray), but I could not get it to stop
the process - maybe it is for stopping the service? - no does not seem
to affect that either - kind of a cute icon though :-) 4. Creating root user. NB: our SQLAuth module makes you send a password. By default, the MySQL daemon has a root user with no password, so you need to set the root password to something. From the MySQL manual ... d:\mysql\bin\mysql mysql NB: I had problems getting the root password set after deleting it. I ended up entering the following to set the root password... D:\mysql\bin>mysqlc -u root mysql Welcome to the MySQL monitor.
Commands end with ; or \g. Type 'help' for help. mysql> UPDATE user SET Password=PASSWORD('qwerty') WHERE user='root';
mysql> FLUSH PRIVILEGES; Now in c:\my.cnf edit client
section and add, Now you should be able to connect with the client program as user root with the new password... 5. Now follow the examples in the manual. In section 8.4 of the manual
file, D:\mysql\bin>mysqlc -u root -pqwerty (can also do, (on non-local machine, (NB: you can also include a
database name on the end of the line to connect to a specific database,
eg: to connect to a database called, 'mydb' enter, every time you see 'mysql>' from now on, it indicates that you are at the prompt in the mysqlc client program... 6. Create test database and a table within it ... mysql> CREATE DATABASE maildb; mysql> CREATE TABLE maildb (username VARCHAR(20),password VARCHAR(20),forward
VARCHAR(20)); mysql> SHOW TABLES; +------------------+ | Tables in maildb | +------------------+ | maildb | +------------------+ 1 row in set (0.01 sec) mysql> DESCRIBE maildb; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(20) | YES | | NULL | | | password | varchar(20) | YES | | NULL | | | forward | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) 7. Loading users in from a text file ... I then made a file d:\mysql\users.txt which looked like, tam pass \N where \N stands for NULL meaning no entry for that field. and then tried to load it. You'll see I assumed a few things about file paths incorrectly. I remember reading in the manual somewhere about using / instead of \ (or using \\). mysql> LOAD DATA LOCAL INFILE "users.txt" INTO TABLE maildb; 8. Inserting a single record/updating a field/encrypting passwords ... (use 'NULL' where you don't have an entry for a field) mysql> INSERT INTO maildb VALUES ('bob','bob','NULL'); oops - should have encrypted the password ... mysql> UPDATE maildb SET password = PASSWORD('bob') WHERE username =
'bob'; so how do I add from text file and encrypt the passwords ...? I can't work out how to do this, so I had better encrypt all the other passwords ... mysql> UPDATE maildb SET passwd = PASSWORD('tam') WHERE username = 'tam';
mysql> UPDATE maildb SET passwd = PASSWORD('test') WHERE username = 'test';
Now, let's see what I have in my maildb database ... mysql> SELECT * FROM maildb; +----------+------------------+---------+ | username | password | forward | +----------+------------------+---------+ | tam | 6752d6483e543e43 | NULL | | test | 378b243e220ca493 | NULL | | bob | 7d67547927a4589e | NULL | +----------+------------------+---------+ 3 rows in set (0.01 sec) oops - I gave the wrong name to my table column 'password' ... 8. Changing a table column name ... mysql> ALTER TABLE maildb CHANGE 'password' 'passwd' VARCHAR(20); Now let's see what is in my maildb database again ... mysql> SELECT * FROM maildb; +----------+------------------+---------+ | username | passwd | forward | +----------+------------------+---------+ | tam | 6752d6483e543e43 | NULL | | test | 378b243e220ca493 | NULL | | bob | 7d67547927a4589e | NULL | +----------+------------------+---------+ 3 rows in set (0.01 sec) A step by step example of creating a MySQLdatabase on SolarisWent to Australian mirror of http://mysql.com , http://mirror.aarnet.edu.au/mysql and downloaded, mysql-3.23.32-sun-solaris2.7-sparc.tar.gzthen umpacked to /usr/local with, gzip -d mysql-3.23.32-sun-solaris2.7-sparc.tar.gz (I had already retrieved gzip for solaris from the sun freeware site, see instructions for getting GNU tar below for details) I tried to untar but failed, apparently because of a bug in solaris tar. So I upgraded to GNU tar by... # cd /usr/local/ # ftp sunfreeware.davnet.com.auUntared with, tar -xvf mysql-3.23.32-sun-solaris2.7-sparc.tarThen followed relevant instructions on page, http://mirror.aarnet.edu.au/mysql/doc/I/n/Installing_binary.html ie: shell> groupadd mysql (If you want to install support for the Perl DBI/DBD interface, see section 4.11 Perl Installation Comments. ) # bin/safe_mysqld --user=mysql & Hmmm, seems not to be working
when I do, ps -ef |grep mysql, so find manual section, /usr/local/mysql/data/servername.err which has, 010202 12:00:00 mysqld started So check permissions... # ls -land when su to the MySQL users, # cd databut, chown -R mysql mysqlshould have recursed all dirs - perhaps the solaris chown only goes one level deep?! so did, cd /usr/local/mysqlthen as root user I try again, bin/safe_mysqld --user=mysql &and get, # Starting mysqld daemon with databases from /usr/local/mysql-3.23.32-sun-solaris2.7-sparc/data (I pressed enter here) #Yay! Now did the tests from the manual, shell> bin/mysqladmin version In order to use MySQLAuth,
you have to have set the root password, so ... # bin/mysqladmin -u root -p password 'qwerty' Enter password: (I pressed enter here as there was no password currently set) # So root password should now
be 'qwerty' # bin/mysqladmin versionand it fails when it did not before, so try, # bin/mysqladmin -u root -p versionand it worked. Success. Test running SQL client, # bin/mysql -u root -pSuccess. Now test MySQLAuth from NT box with c:\winnt\system32\mysqlauth.ini of, # Welcome to MySQLAuth ini File. and enter, cd \surgemailand get error, -ERR: Cannot Connect to 10.0.0.2 as roottry, telnet 10.0.0.2 3306and get, 'server_name' is not allowed to connect to this MySQL server So we need to allow access... mysql> use mysql;Manual says to use commands rather than touching the table directly. Want to change host from, localhostto 10.0.0.*(or maybe the special char % to allow connections from anywhere) so, mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION;Oops I did not put in the correct password. so now I have, mysql> select * from user;where root connecting from anywhere can use password some_pass, and if connecting locally can use qwerty!+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+ | 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 | 009094026f11b5c7 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | fluffy | 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 | | fluffy | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | % | root | 0fc756bc026507b2 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | +-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+ 5 rows in set (0.01 sec) So now try adding a special user, SurgeMail password of mail, mysql> GRANT ALL PRIVILEGES ON *.* TO surgemail@"%" IDENTIFIED BY 'mail' WITH GRANT OPTION;NB: GRANT is implemented in MySQL Version 3.22.11 or later. For earlier MySQL versions, the GRANT statement does nothing. 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 | 009094026f11b5c7 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | fluffy | 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 | | fluffy | | | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | % | root | 0fc756bc026507b2 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | % | surgemail | 64d94ec17fb953a4 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | +-----------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+ 6 rows in set (0.00 sec) (and can revoke them again, mysql> REVOKE ALL PRIVILEGES ON *.* FROM surgemail@"%";) Let's try from local box, so on solaris box set /etc/mysqlauth.ini as above but change, mysql_server 10.0.0.2to mysql_server 127.0.0.1just to be sure connecting locally. we get, # ./mysqlauthso connection to the server was successfull, now we need to add an appropriate database and table as per the NT example above.
|
