 |
|
 |
| |
Developer on focus
Anton Zamov is a dipl. engineer with more than 6 years of active professional
experience and many awards ...
read more>>
|
|
 |
 |
 |
|
|
|
|
| |
|
MySQL: Creating and using your own password file
|
Creating and using your own password file.
This assumes MySQL has been configured with SSL support [ ./configure --with-openssl ]
CREATE TABLE password (
p_id int(11) NOT NULL auto_increment,
id varchar(20),
valid enum('y','n'),
password BLOB,
timeEnter timestamp,
PRIMARY KEY (p_id)
);
insert into password (id,password)
values ('bob',des_encrypt('secret','somekey'));
insert into password (id,password)
values ('tom',des_encrypt('password','somekey'));
insert into password (id,password)
values ('kate',des_encrypt('desmark','somekey'));
insert into password (id,password)
values ('tim',des_encrypt('tim','somekey'));
insert into password (id,password)
values ('sue',des_encrypt('SUM','somekey'));
insert into password (id,password)
values ('john',des_encrypt('dotgo86','somekey'));
insert into password (id)
values ('randal');
mysql> select id,des_decrypt(password,'somekey') from password;
+--------+---------------------------------+
| id | des_decrypt(password,'somekey') |
+--------+---------------------------------+
| bob | secret |
| tom | password |
| kate | desmark |
| tim | tim |
| sue | SUM |
| john | dotgo86 |
| randal | NULL |
+--------+---------------------------------+
7 rows in set (0.00 sec)
Note the bad passwords in the file ('secret','password', and
password is the same as the id.
The following update statement will fill in the
value for valid:
update password set valid =
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
),
substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
)
)
,'n');
Which gives the following:
mysql> select id,valid from password;
select id,valid from password;
+--------+-------+
| id | valid |
+--------+-------+
| bob | n |
| tom | n |
| kate | y |
| tim | n |
| sue | y |
| john | y |
| randal | n |
+--------+-------+
7 rows in set (0.00 sec)
To understand it, try taking the following select apart:
select
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
),
substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
)
)
,'n')
as valid
from password;
|
About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GPU Free Documentation License)
Last Updated: Tue Jul 20 12:14:51 EDT 2004
|
|
|
|
|
|
|
|
