MySQL之用户权限及pymysql

一.用户管理

1.创建mysql账户

账户中涉及的三个数据

账户名 密码 ip地址

ip是用于限制某个账户只能在那些机器上登录

 

create user 用户名@主机地址 identified by "密码";

# 注意:操作用户 只能由root账户来进行

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

2.权限管理

涉及到的表,等级依次降低:

 

user 与用户相关信息
db 用户的数据库权限信息
tables_priv 用户的表权限
columns_priv 用户的字段权限

 

 

语法:

 

grant all on *.* to 用户名@主机地址 identified by "密码";

 

# 如果用户不存在则自动创建新用户,推荐使用

 

授予所有库所有表权限,信息在user表中:
grant all on *.* to rose@localhost identified by "123";

 

授予某一数据库的所有表的权限,信息在db表中:
grant all on day42.* to rose1@localhost identified by "123";

 

授予某一数据库的某一表的权限,信息在tables_priv 表中:
grant all on day42.table1 to rose2@localhost identified by "123";

 

授予某一数据库的某一表的某些字段的增删改查权限,信息在columns_priv  表中:
grant select(name),update(name) on day42.table1 to rose3@localhost identified by "123";

 

 

 

all表示的是对所有字段的增删改查
*.* 所有库的所有表

 

收回权限
revoke all on *.* from 用户名@主机地址;

 

revoke all on day42.table1 from rose2@localhost;

 

# 刷新权限
flush privileges;

 

 

 

#with grant option 表示 可以将他拥有的权限授予其它的用户
grant all on *.* to root1@localhost identified by "123" with grant option;

 

 

 

# 授予某个用户 可以在任何主机上登录
grant all on *.* to jack10@"%" identified by "123";
grant all on *.* to jack10@localhost identified by "123";

 

 

 

二.pymysql

     pymysql 是一个第三方模块,帮我们封装了,建立连接,用户认证,sql语句的执行以及结果的获取

1.基本使用

 

import pymysql

 

"""
1.连接服务器
2.用户认证
3.发送指令
4.提取结果
"""
# 1. 连接服务器 获取连接对象(本质上就是封装号的socket)
conn = pymysql.connect(
host = "127.0.0.1", #如果是本机 可以忽略
port = 3306, # 如果没改过 可以忽略
user = "root", #必填
password = "111", #必填
database = "day42" #必填

autocommit = True (默认为False,默认开启事务,改为True后自动提交,增删改的记录才会写入数据库)
)

 

# 2.通过连接拿到游标对象
# 默认的游标返回的是元组类型 不方便使用,需要更换字典类型的游标
c = conn.cursor(pymysql.cursors.DictCursor)

 

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

 

# 4.提取结果
# print(res)                   条数
# print(c.fetchall())       所有的结果, [{},{},........]

 

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

 

 

 

# 移动光标 参数1位移动的位置 mode 指定 相对或绝对
# c.scroll(1,mode="absolute")

 

 print(c.fetchall())

 

 print(c.fetchmany(1))
 print(c.fetchone())

2.注入问题

 

 

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

 

比如sql = "select * from user where username = '%s'  and password='%s' "%(username,password)

在使用 Tom'--  ***** 或者 xxx' or 1=1 --  ******时都会查到数据,因为--加空格是sql语句里面的注释,后面的内容全部被注释了

 

 

如何避免这个问题?

 

在服务器端执行sql以前做sql的验证

 

我们只需要将参数交给pymysql来做拼接即可,如 c.execute(sql,(参数1,参数2))

 

 

3.修改数据

 

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,)):
    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默认开了事务,可以自己找合适的位置提交

 

4.调用存储过程

 

# 创建名为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("select @_add1_2")
print(c.fetchone())

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

 

5.调用事务

 

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)

try:
    c.execute("start transaction;")
    sql1 = "update user set money = money - 1000 where name = 'jack'"
    c.execute(sql1)
    sql2 = "update user set moneys = money + 1000 where name = 'rose'"
    c.execute(sql2)
    c.execute("commit;")
except:
    c.execute("rollback;")


c.close()
conn.close()