mysql用户管理和pymysql

mysql用户管理

为了使不同的人员访问到对应身份的数据库资源，每个人都有不同的权限。

mysql本质上是一款cs软件，它具备用户认证，那么如何实现呢？那就是写入文件，但是在mysql把文件称作表，只有把用户数据写到表中就可以了

权限相关表

自带的mysql数据库，四个表用于存储账户信息以及权限

user: 与用户相关的信息

db： 用户的数据库权限信息

table_peiv,： 用户的表权限

columns_priv： 用户的字段权限

权限优先级

user>db>table_priv>columns_priv

select * form user;
#通过查看对应表格来查看对应的数据
#由于字段会较多，以表格的形式展示，使用G来进行纵向的显示
select * form userG;

创建账号语句

create user 用户名@'ip地址' identified by 密码；
create user tom@'192.168.101' identified by '123';

#该语句表面tom只能在101机器上使用,别的机器就无法登录

#用%可以表示在任意机器可用
#操作用户 只能由root账户来进行

#删除 将同时删除所有权限
drop user 用户名@主机地址；

#该方式创建的账号没有任何权限，所有只要了解即可

授权语句

执行时如果账号不存在的话会自动创建账号，所以更加推荐使用

注意：默认只有root才能为其他账号授权

grant all on *.* to tom@'localhost' identified by '123';
#该语句中的all表示增删改查所有权限，但是不包含grant权限
#*.*表示任何数据库 任何表 存储在user表

grant all on *.* to tom@'%' identified by '123';
#host为%表示，该账户可以在任何主机上登录但是不包括localhost
grant all on *.* to tom@'localhost' identified by '123';
#继续执行，上述语句保证localhost也可以登录该账户


grant all on db.* to tom@'localhost' identified by '123';
#db.*表示该用户可以操作db数据库的任何表，存储在db表

grant all on db.t1 to tom@'localhost' identified by '123';
#db.*该用户可以操作db数据库的t1表 存储在table_privi表

grant select(id) on db.t1 to tom@'localhost' identified by '123'
#精确到字段 和 操作级别
#该用户只能查询 db下的t1表

grant all on *.* to tom@'localhost' identified by '123' with grant option;
#with grant option 表示该账户可以将权限授予其他用户

revoke all privileges [column] on db.table from user@'host';
revoke all on day42.table from rese2@localhost;
#收回权限

drop user@'host'
#删除用户

flush privileges;
#刷新权限表，一些时候权限信息可能会有所延迟 可以执行该语句立即刷新权限信息

pymysql模块

pymysql是python提供的一个mysql客户端模块，用于与mysql服务器建立连接，发送查询，并获取结果等。

基本使用

import pymysql
#1.连接服务器，获取连接对象（本质上就是封装好的socket）
conn=pymysql.connect(host='127.0.0.1',#如果是本机，可以忽略
                     port=3306,#如果没有改过 可以忽略
                     user='root',#必填
                     password='123',#必填
                     db='day46',)#必填
#2.通过连接拿到游标对象
#默认的游标返回的是元祖类型 不方便使用，需要更换字典类型的游标
c=conn.cursor(pymysql.cursors.DictCursor)

#3.执行sql
sql='select *from table1'
res=c.execute(sql)
#查询语句将返回查询的结果数量

#4.提取结果
print（res）#输出结果个数
print（c.fetchall())#输出全部的结果内容
#print(c.fetchone())
#print(c.fetchmany(1))


#5.关闭连接
c.close()
conn.close()

#移动光标 参数1位移动的位置，mode指定 相对或绝对
#游标移动到末尾后无法在读取数据，若需要重复读取数据，需要用scroll来移动游标
#c.scorll(1,mode='absolute')
#c.scorll(-1,mode='relative')
#print(c.fetchall())
#print(c.fetchmany(1))
print(c.fetchone())
print(c.fetchone())

默认开启了事务，如果执行了修改操作，一定记得用连接对象提交事务

sql注入攻击

指的是，一些程序员，在输入数据时，按照sql的语法规范，提交了用于攻击型目的的数据

例如：登录功能，需要用户输入用户名和密码

解决方案：

1. 客户端在发送sql给服务器前进行re判断

这样的问题在于一些程序可以模拟客户端直接发送请求给服务器

2. 在服务器端将sql交给mysql是作进一步处理,相关的代码其实pymysql已经做了封装

我们只要保证不要自己来拼接sql语句即可,将拼接参数操作交给pymysql.

正常的一个登录功能代码如下：

import pymysql
conn=pymysql.connect(
	host='127.0.0.1',
    port=3306,
    user='root',
    password='111'
    database='day42'
    #autocommit=False #开启自动提交，不常用
)
c=conn.cursor(pymysql.cursors.DictCursor)
name=input('name:')
pwd=input('pwd:')

sql='select *from user where name=%s'

if c.execute(sql,(name,)):#name参数交给模块
    print('用户名已存在')
else:
    sql2='insert into user values(%s,%s)'
    if c.execute(sql2,(name,pwd)):
        print('注册成功!')
        conn.commit() #调用连接对象的提交函数
    else:
        print('注册失败')
c.close()
conn.close()

注意：pymysql自动开启了事务，所以我们自己在合适的位置提交

调用存储过程

#创建名为add1的存储过程
delimiter |
create procedure add1(in a int,in b int,out c int)
begin
set c=a+b
end|
delimiter;

#pymysql中调用
import pymysql
conn = pymysql.connect(
    host = "127.0.0.1",  #如果是本机 可以忽略
    port = 3306,    # 如果没改过 可以忽略
    user = "root", #必填
    password = "111", #必填
    database = "day42", #必填,
    autocommit=True  # 开启自动提交  不常用....
)
c.conn.cursor(pymysql.cursors.DictCursor)
c.callproc('add1',(1,2,1212))#@_add1_0 @_add1_1 @add1_2
c.execute('selete @_add1_2')
print(c.fetchone())

#调用存储过程时，传入参数，会自动定义为变量
#命名方式@_过程的名称_参数的索引 从0开始