MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写 |
您所在的位置：网站首页 › mysql的加密函数md5 › MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写 |
|
MySQL数据库认证密码有两种方式:
1：MySQL 4.1版本之前是MySQL323加密
2：MySQL 4.1和之后的版本都是MySQLSHA1加密
还有函数:AES_ENCRYPT()加密函数和AES_DECRYPT()解密函数和MD5()加密。
MySQL数据库中自带old_password(str)和password(str)函数,前者是MySQL323加密,后者是MySQLSHA1方式加密;
MYSQL323加密中生成的是16位字符串,而在MySQLSHA1中生存的是41位字符串,其中*是不加入实际的密码运算中,就是说MySQLSHA1加密的密码的实际位数是40位;
MYSQL323对应于old_password(str)函数
MYSQLSHA1对应于password(str)函数
注：AES_ENCRYPT()加密函数和AES_DECRYPT()解密函数都需要一个“key”来协助加密。
eg:select aes_encrypt('kaka123','key');
select aes_decrypt(aes_encrypt('kaka123','key'),'key');
1.MYSQL323对应于old_password(str)函数加密：
mysql> select old_password('kaka123');
+-------------------------+
| old_password('kaka123') |
+-------------------------+
| 2ee53a20184c9267 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)
2.MYSQLSHA1对应于password(str)函数加密： mysql> select password('kaka123'); +-------------------------------------------+ | password('kaka123') | +-------------------------------------------+ | *90B3D884FB6092549F244125549B77C000A0F9C6 | +-------------------------------------------+ 1 row in set (0.00 sec)3.MYSQL MD5()加密： mysql> select md5('kaka123'); +----------------------------------+ | md5('kaka123') | +----------------------------------+ | 5d052f1e32af4e4ac2544a5fc2a9b992 | +----------------------------------+ 1 row in set (0.00 sec)4.AES_ENCRYPT()加密函数 mysql> select aes_encrypt('kaka123','key'); +------------------------------+ | aes_encrypt('kaka123','key') | +------------------------------+ | D#jA | +------------------------------+ 1 row in set (0.00 sec)5.AES_DECRYPT()解密函数 mysql> select aes_decrypt(aes_encrypt('kaka123','key'),'key'); +-------------------------------------------------+ | aes_decrypt(aes_encrypt('kaka123','key'),'key') | +-------------------------------------------------+ | kaka123 | +-------------------------------------------------+ 1 row in set (0.00 sec)4.AES_ENCRYPT()加密函数 mysql> select host,user,password from mysql.user; +----------------+------+-------------------------------------------+ | host | user | password | +----------------+------+-------------------------------------------+ | localhost | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | tony\_ts\_tian | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | 127.0.0.1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | ::1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | % | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | % | kaka | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +----------------+------+-------------------------------------------+ 6 rows in set (0.00 sec) mysql> update mysql.user set password=AES_ENCRYPT("kaka123",'key') where host='%' and user='kaka'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select host,user,password from mysql.user; +----------------+------+-------------------------------------------+ | host | user | password | +----------------+------+-------------------------------------------+ | localhost | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | tony\_ts\_tian | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | 127.0.0.1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | ::1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | % | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 | | % | kaka | œDŸ#jAÂ | ¯Ll +----------------+------+-------------------------------------------+ 6 rows in set (0.00 sec)5.AES_DECRYPT()解密函数 mysql> select host,user,AES_DECRYPT(password,'key') from mysql.user; +----------------+------+-----------------------------+ | host | user | AES_DECRYPT(password,'key') | +----------------+------+-----------------------------+ | localhost | root | NULL | | tony\_ts\_tian | root | NULL | | 127.0.0.1 | root | NULL | | ::1 | root | NULL | | % | root | NULL | | % | kaka | kaka123 | +----------------+------+-----------------------------+ 6 rows in set (0.00 sec)mysql不区分大小写: 修改MySql Server安装目录下的 my.ini 或my.conf文件 在mysqld节下加入下面一行 lower_case_table_names=0 （0：大小写敏感；1：大小写不敏感）最后重启一下MySql服务即可。 修改对于新建的表，老表无效#解决mysql字段不区分大小写: CREATE TABLE `user`( `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT'用户登录表主键id', `uname` VARCHAR(35) NOT NULL COMMENT'用户登录名', `upass` VARCHAR(32) NOT NULL COMMENT'用户登录密码', `ustatus` VARCHAR(2) NOT NULL DEFAULT '0' COMMENT'用户登录状态,0为正常，1为锁定', UNIQUE (`uname`), PRIMARY KEY(`id`) )ENGINE = INNODB COLLATE=utf8_bin COMMENT='用户登录表';注：COLLATE=utf8_bin |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |