MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写
来源:互联网 发布:淘宝店铺怎么修改分类 编辑:程序博客网 时间:2024/05/21 14:59
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: 0mysql> 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
0 0
- MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写
- MySQL修改配置 区分大小写和不区分大小写
- Mysql 不区分大小写
- mysql 不区分大小写
- mysql 不区分大小写
- mysql数据库设置不区分大小写
- mysql数据库设置不区分大小写
- mysql数据库设置不区分大小写
- mysql数据库设置不区分大小写
- 更改MYSQL数据库不区分大小写
- MySQL加密和解密
- MySQL加密和解密
- MySql解决不区分大小写和中文模糊查询问题
- à和a mysql大小写不敏感,也不区分这个
- mysql 表明不区分大小写
- mysql设置不区分大小写
- MySQL查询不区分大小写
- MYSQL查询不区分大小写
- 一个平庸程序员的自白
- 三维空间碰撞问题;空间中两直线的最短距离及最近点
- 3.1.12 WM_DESTROY 消息
- 自定义Maven archtype
- 使用同步代码块解决线程安全问题
- MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写
- Linux PPP 数据收发流程
- Codeforces Round #320 (Div. 2) C. A Problem about Polyline
- 总结-通过代码自定义cell(cell的高度不一致)
- java comparator 排序 定义比较器内部类的方式排序 通用排序方式 非常灵活
- ppp拨号脚本配置(zz)
- GNU
- iOS组件添加圆角、边框的方法
- 啊啊啊