以mysql.user中存在的用户登陆如,能够将语句缓存

2019-08-09 作者:数据库   |   浏览(100)

将数据库中存储时间的数据类型改为varchar(),这时最好让这些时间是数据库中自动生成的(一个没有格式的输入也可能会导致输出错误),因为存储类型为varchar(),所以获取到的值也就被认为是一个字符串,直接将数据库中的时间字符串进行转化(这时那些转化函数是能识别数据库中的时间函数的),客户端的时间格式不再影响转换过程。

 

Transact-SQL中的存储过程,非常类似于c#语言中的方法,它可以重复调用。当存储过程执行一次后,可以将语句缓存中,这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。

目录

不过数据库中存储时间的类型如果为字符型也会带来一些麻烦:

上周,以前公司的同事朋友找我帮忙,看看能否帮忙恢复一个MySQL 数据库,具体情况为:数据库版本为MySQL 5.6(具体版本不清楚),也不清楚具体的数据库引擎; 没有数据库备份,只剩下数据库下面的一些文件(frm、idb),具体原因是因为出现问题的时候,重装了MySQL,最要命的是ibdata1等文件也没有了,当然这中间细节过程如何,不清楚也不用去纠结了。大概就是这么一个情况。

Ø 存储过程的概念

  • MySQL 5.6不删空用户的影响
    • 问题
    • 分析
    • 测试
      • 启动mysqld时没有加上--skip-name-resolve
      • 启动mysqld时加上--skip-name-resolve
    • 结论

数据库中的时间仅仅是用来显示、查找的,那么影响还不算大,但如果对时间字段进行一些算法如计算星期、DateDiff、DateAdd等,那就麻烦了,尤其实在大型数据查询中转换类型是会影响效率的

 

    存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。

MySQL 5.6不删空用户的影响



总结

数据库中存储日期的字段类型到底应该用varchar还是datetime ?这两种方法各有优势,datetime可以借用sql函数库中运算函数,增加了时间在各种运算上的效率;而varchar类型则可以在字符编码上显出优势。在 存储的时间将来不需要进行大量计算 的前提下,可以考虑选择varchar类型,反之,选择datetime类型。

 

    存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

问题


MySQL5.6 新建本地可以登录的用户,但在本地始终登录不上,不存在的用户却能登录成功

mysql -uroot -p1234 -S /data/mysql/33562/mysql.sock  -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
 ----------- 
| version() |
 ----------- 
| 5.6.39    |
 ----------- 

创建了admin@'%' 用户,但在本地无法登陆,但可以远程登陆

sselect user,host,password from mysql.user;
 -------- ----------- ------------------------------------------- 
| user   | host      | password                                  |
 -------- ----------- ------------------------------------------- 
| root   | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root   | slave58   |                                           |
| root   | 127.0.0.1 |                                           |
| root   | ::1       |                                           |
|        | localhost |                                           |
|        | slave58   |                                           |
| admin  | %         | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| zabbix | 127.0.0.1 | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| super  | 127.0.0.1 | *F85A86E6F55A370C1A115F696A9AD71A7869DB81 |
| super  | slave58   | *F85A86E6F55A370C1A115F696A9AD71A7869DB81 |
 -------- ----------- ------------------------------------------- 


show grants for 'admin'@'%';

 --------------------------------------------------------------------------------------------------------------- 
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' |
 --------------------------------------------------------------------------------------------------------------- 

select password('admin');
 ------------------------------------------- 
| password('admin')                         |
 ------------------------------------------- 
| *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
 ------------------------------------------- 

以ip地址无法登陆
mysql -uadmin -padmin   -P33562 -h10.186.30.58


mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'slave58' (using password: YES)

以socket也无法登陆
mysql -uadmin -padmin   -P33562 -S /data/mysql/33562/mysql.sock
或者
mysql -uadmin -padmin   -P33562 -h127.0.0.1

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

不存在的用户却可以登录成功
mysql -utest  -S /data/mysql/33562/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 15
Server version: 5.6.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

因为数据库不大,将对应的文件拷贝到自己一台测试服务器的MySQL数据文件目录下后(下面实验测试,对数据库名等敏感信息做了一下混淆),如下所示,数据库名为test,show tables可以看到相关的表。

    由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。

分析


1、本是以ip地址进行登录的,错误信息报的是Access denied for user 'admin'@'slave58',自动进行了DNS解析,解析成了主机名,skip-name-resolve 可以禁用DNS解析
2、mysql在验证权限的时候,先验证host,在验证user,最后验证password,即
host->user->password
3、以host或者socket登陆,进行了DNS解析,host;socket被解析成了slave58;localhost, 而mysql.user 中host 为localhost和slave58 对应空用户,就报了访问拒绝错误

 

 

测试


 

    1、 存储过程的优点

启动mysqld时没有加上--skip-name-resolve

以mysql不存在的用户进行登录

mysql -utest -P33562 -h`hostname`
或者
mysql -utest -S /data/mysql/33562/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.39 MySQL Community Server (GPL)
.....
show grants;
 ------------------------------------ 
| Grants for @slave58                |
 ------------------------------------ 
| GRANT USAGE ON *.* TO ''@'slave58' |
 ------------------------------------ 

show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| test               |
 -------------------- 
create table test.test_t1(id int);
Query OK, 0 rows affected (0.05 sec)
drop table test.test_t1;
Query OK, 0 rows affected (0.05 sec)
drop database test;
Query OK, 8 rows affected (0.49 sec)


    具有USAGE  的权限的用户 有test 库的所有权限和information_schema 中表的select权限。
    不存在的用户还可以 drop datase test 这个“神操作”是因为 空用户具有test和test_*库的所有权限,而任何用户都能匹配空用户,mysql5.6初始化后默认就有这个,而mysql5.7默认没有,也没有空用户,空密码用户
    select * from mysql.db;

图片 1

以mysql.user中存在的用户登录如 “问题” 中所示,无法登录


在mysql.user 中存在的用户还不能登录,任意一个不存在的用户还可以登录成功 ,还可以drop database test,这有很大的安全隐患!开始怀疑,这mysql5.6是不是假的o_o


图片 2

        A、 存储过程允许标准组件式编程

启动mysqld时加上--skip-name-resolve

ps -ef | grep -w 33562 | grep -v grep  | awk '{print $2}' | xargs -l kill

su - mysql -c "/usr/local/mysql/5.6.39/bin/mysqld --no-defaults --basedir=/usr/local/mysql/5.6.39 --datadir=/data/mysql/33562 --pid-file=/data/mysql/33562/clone.pid --port=33562 --server-id=33562 --socket=/data/mysql/33562/mysql.sock --tmpdir=/data/mysql/33562 --skip-name-resolve &"

以mysql.user中不存在的用户进行登录

mysql -utest   -P33562 -h`hostname`
ERROR 1045 (28000): Access denied for user 'test'@'10.186.30.58' (using password: NO)

mysql -utest -S /data/mysql/33562/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.6.39 MySQL Community Server (GPL)

以`hostname`登录,host 转化成ip地址,在mysql.user中没有匹配到;
以socket登录 host 被转化成localhost,在mysql.user中匹配到了''@'localost'空用户,登录成功。可见,任意用户都能匹配mysql.user的user为空' '的用户

以mysql.user中存在的用户可以登录成功

mysql -uadmin -padmin   -P33562 -h10.186.30.58
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.6.39 MySQL Community Server (GPL)

 

        存储过程创建后可以在程序中被多次调用执行,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。

结论

1、在没有以 skip-name-resolve 启动启动的情况下,以mysql -uuser -ppassword登录会进行DNS解析,在mysql.user中匹配到错误的 host,即时没有限制主机的用户 ‘user’@'%'也无法登录
2、即时一个并不存在的用户在以`hostname` 或者socket进行登录,若匹配了mysql.user中的host,该host对应的user和password为空,并不存在的用户也能登录成功
3、为了避免进行DNS解析,在my.cnf配置文件中添加skip-name-resolve
4、在刚初始化mysql5.6后,就应该删除mysql.user; mysql.db中空用户,空密码账户(包括root空秘密账户),减少安全隐患
DELETE FROM mysql.user WHERE PASSWORD=' ';
DELETE FROM mysql.db WHERE USER=' '; FLUSH PRIVILEGES;
5、可用mysql5.6 自带mysql_secure_installation 脚本删除空用户,空密码账户,删除test库

参考
mysql空用户(user列为空)带来的影响

 

        B、 存储过程能够实现较快的执行速度

其中有几张表的存储引擎为MyISAM,那么这些表的数据是完全可以恢复的,但是大部分表的存储引擎为InnoDB,访问表或查看表都会提示“ERROR 1146 (42S02): Table 'xxxx' doesn't exist 不存在。

        如果某一操作包含大量的T-SQL语句代码,分别被多次执行,那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化,所以速度就要慢一些。

 

        C、 存储过程减轻网络流量

mysql> desc think_cache;

ERROR 1146 (42S02): Table 'test.think_cache' doesn't exist

mysql> show create table think_cache;

ERROR 1146 (42S02): Table 'test.think_cache' doesn't exist

mysql> 

        对于同一个针对数据库对象的操作,如果这一操作所涉及到的T-SQL语句被组织成一存储过程,那么当在客户机上调用该存储过程时,网络中传递的只是该调用语句,否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负载。

 

        D、 存储过程可被作为一种安全机制来充分利用

由于共享表空间的ibdata1数据文件不存在了,加之有没有备份,所以我武断的判断这个数据库真的无法恢复了,但是过后一天,这个朋友跟我说找了一家数据恢复公司将这个数据库恢复了。 听到这个消息颇有点学艺不精的尴尬(其实谈不上尴尬吧,本来还在学习MySQL的路上,有些知识点不清楚也很正常。经验是需要慢慢积累的),不过更多的是好奇别人是如何恢复数据的,既然别人能够恢复,那么自己下一次遇到这种情况也要能搞定。下面就来复盘一下别人是如何恢复数据的(其实只要稍稍做点功课,发现这个其实挺简单的)

        系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。

 

 

首先,我们来了解一下MySQL 表空间数据文件idbdat1文件相关概念和知识点:

Ø 系统存储过程

 

    系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

    InnoDB采用按表空间(tablespace)的方式进行存储数据, 默认配置情况下会有一个初始大小为10MB, 名字为ibdata1的文件, 该文件就是默认的表空间文件(tablespce file),用户可以通过参数innodb_data_file_path对其进行设置,可以有多个数据文件,如果没有设置innodb_file_per_table的话, 那些Innodb存储类型的表的数据都放在这个共享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存储引擎类型的表就会产生一个独立表空间,独立表空间的命名规则为:表名.idb. 这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息,其它信息还是存放在共享表空间中。

    常用系统存储过程有:

 

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助,查询数据库信息
exec sp_helpdb master;

    其实当时主要是对这个概念有点模糊了,以为这个系统变量innodb_file_per_table默认是关闭的,数据都会存储在共享表空间中,那么这些文件删除了,数据就无法恢复。所以武断的下结论,其实从MySQL 5.6.6开始, 系统变量innodb_file_per_table默认是启用的。只要再多了解一点或者说更深入了解一点的话,情况就会立马就会反转。也就是说如果开启了独立表空间,可从ibd文件中恢复数据。即使共享表空间的数据文件idbdata1丢失也不要紧,反之,如果未开启独立表空间时,idbdat1被删除了,数据也会被删除,只能从备份中恢复,真的没有其他办法。

    系统存储过程示例:

 

--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';

--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

 

 

那么我们接下来看看,如何从idb文件中恢复数据吧,我们需要用到mysqlfrm工具, 需要安装MySQL Utilities,下面是安装MySQL Utilities 1.5.5

Ø 用户自定义存储过程

 

   1、 创建语法

# tar -xvf mysql-utilities-1.5.5.tar.gz

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements

# cd mysql-utilities-1.5.5

 

# python ./setup.py build

   2、 创建不带参数存储过程

# python ./setup.py install

--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
    drop proc proc_get_student
go
create proc proc_get_student
as
    select * from student;

--调用、执行存储过程
exec proc_get_student;

 

   3、 修改存储过程

 

--修改存储过程
alter proc proc_get_student
as
select * from student;

提取frm文件的表结构信息

   4、 带参存储过程

 

--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
    select * from student where id between @startId and @endId
go

exec proc_find_stu 2, 4;

mysqlfrm 是一个恢复性质的工具,用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句。此处不对mysqlfrm工具做过多介绍,我们使用msqlfrm来生成该数据库的表的CREATE语句

   5、 带通配符参数存储过程

 

--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
    drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from student where name like @name and name like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
[root@DB-Server ~]# service mysql stop

Shutting down MySQL.... SUCCESS! 

[root@DB-Server ~]# /usr/local/bin/mysqlfrm --basedir=/usr --port=3306 --user=root /data/mysql/test/ > test_frm.sql

[root@DB-Server ~]# 

   6、 带输出参数存储过程

 

if (object_id('proc_getStudentRecord', 'P') is not null)
    drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @id int, --默认输入参数
    @name varchar(20) out, --输出参数
    @age varchar(20) output--输入输出参数
)
as
    select @name = name, @age = age  from student where id = @id and sex = @age;
go

-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 7; 
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name   '#'   @temp;

检查导出的SQL语句,发现都是“ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode”这类错误:

 

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:以mysql.user中存在的用户登陆如,能够将语句缓存

关键词: 小鱼儿玄机30码