第六十三篇 用户权限、pymysql模块

目录

• 一、用户管理
  • 1.用户权限
  • 2.授权
    • 1.创建用户和删除用户
    • 2.权限管理
• 二、可视化MySQL软件(客户端)
• 三、pymysql模块
  • 1.简单的对库操作
  • 2.SQL注入攻击
  • 3.pymysql对于事务的应用
  • 4.pymysql对于存储过程的应用

一、用户管理

1.用户权限

1.方法：

• 将用户信息写入表中即可

2.权限相关表：

# 自带的MySQL库中，有以下四个表用于存储用户信息以及权限

user   # 所有用户信息都会存储在里面，无论权限有多少

db     # 存放拥有对库操作的权限的用户信息

table_priv   # 存放仅拥有对表操作的权限的用户信息

columns_priv  # 存储仅拥有对字段操作权限的用户信息

# 权限优先级
user -> db -> table_priv -> columns_priv

# 查看表中内容，由于字段太多，以表格形式展示会比较乱，可以添加\G来纵向显示
select * from user \G;  # 可以查看用户所拥有的权限

3.操作权限：

• 可以通过select/update/delete/insert/drop等等，设置操作权限

2.授权

1.创建用户和删除用户

# 创建用户时，需要三个信息：账户名、IP地址、密码

# 其中IP地址是用来限制该账户的登陆设备的，也就是让其只能在指定的机器上登陆
# 我们可以用通配符"%"来表示可以在任意IP地址端的计算机上登陆（不包括主机地址）
# 要想包括主机，我们任然需要加上：create user 用户名@'127.0.0.1' identified by '密码';
# 注意：主机IP可以用localhost代替

# 语法：
create user 用户名@IP地址 identified by '密码';

# 只有root账户可以创建用户(有其他方法可以让超级用户也获得该权限)
# 在下面会讲

# 删除用户（会同时删除其所有权限）
drop user 用户名@IP地址;

2.权限管理

1.授权给用户，可以自由选择对创建用户的权限设置
2.用这种方法时，其实也覆盖了创建用户的操作，因此一般直接用这种方法进行创建用户并授权

# 1.给创建用户授权：可以对所有库、所有表、所有字段进行增删改查操作
grant all on *.* to 用户名@IP地址 identified by '密码';

# all表示对所有字段拥有增删改查的权限
# *.*中第一个*表示所有库，第二个*表示所有表


# 2.创建拥有和root用户一样权限的超级用户,并且可以在任何主机上登陆
# with grant option表示可以将root所拥有的权限授予其他用户
grant all on *.* to 用户名@"%" identified by '密码' with grant option;
grant all on *.* to 用户名@localhost identified by '密码' with grant option;  # 用来保证在localhost也可以登陆该账户


# 3.设置相应的权限
# 3.1 授权可以操作指定库中的任何表的操作权限
grant all on 库名.* to 用户名@IP地址 identified by '密码';
grant all on mydb.* to 'king'@'%' identified by '123';
select * from db \G;

# 3.2 授权指定库中指定表的操作权限(可以精确到操作级别)
grant all on 库名.表名 to 用户名@IP地址 identified by '密码';
grant all on mydb.emp to 'jojo'@'127.0.0.1' identified by '222';

grant select on mydb.student to 'Timor'@'127.0.0.1' identified by '666' -- 只能对该表进行查看;
select * from tables_priv \G  -- 查看是否存在该用户; 

# 3.3 授权指定库中指定表中的某个字段的操作权限(精确到字段和操作级别，不能使用*来表示对所有字段的操作限制)
grant select(字段) on 库名.表名 to 用户名@IP地址 identified by  '密码';
grant update(name) on mydb.emp to 'liu'@'192.151.11.201' identified by '333' -- 只能更新name字段的信息;
select * from columns_priv \G;


# 4.刷新权限
flush privileges;


# 5.收回权限
revoke all privileges 字段名 on 库名.表名 from 用户名@IP地址;
revoke all on *.* from 用户名@IP地址;

二、可视化MySQL软件(客户端)

1.MySQL-workbench

三、pymysql模块

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

1.简单的对库操作

import pymysql

try:
	# 1.建立连接，并获得连接对象
	conn = pymysql.Connect(
		host = '127.0.0.1',
		port = 3306,
		user = 'root',
		password = '',
		db = 'mydb',
	)
	print('连接服务器成功')
	
	# 2.获取游标对象
	cursor = conn.cursor(pymysql.cursors.DictCursor)  # 如果不加任何参数，则提取结果显示的是元组类型，使用pymysql.cursors.DictCursor，可以更换为字典类型的游标
	
	# 3.定制SQL语句
	sql = 'select * from student'  # 里面不用加分号
	
	# 4.发送SQL语句，并得到搜索结果的个数
	count = cursor.execute(sql)  
	print(f'结果个数：{count}')
	
	# 5.提取结果(通过游标对象)
	print(cursor.fetchall())  # 提取全部结果
	print(cursor.fetchmany(2))  # 通过参数，提取指定个数的结果
	print(cursor.fetchone())  # 仅提取一个结果
	
	# 6.移动游标，可以多次提取结果
	# 使用相对位置
	# 游标当前所在位置的值的索引是0，可以正负数字来移动游标
	cursor.scroll(-1, 'relative')  # 游标当前所在位置向前（左）移动一位
	print(cursor.fetchone())
	cursor.scroll(1, 'realative') # 游标向当前位置的后面（右）移动一位
	print(cursor.fetchone())
	
	# 使用绝对位置
	# 规定了所有值的索引，第一个值的索引是0，依次累加，游标根据索引来查找值的位置
	cursor.scroll(1, 'absolute')  # 游标指向索引为1的值
	print(cursor.fetchone())

# 7.捕获异常
except Exception as e:
	print('连接服务器失败')
	print(e)

finally:     # 无论如何后面的代码都会执行

	# 8.关闭游标
	if cursor:
		cursor.close()
		print('关闭游标')
		
	# 9.关闭连接
	if conn:
		conn.close()
		print('关闭连接')

2.SQL注入攻击

1.通过按照SQL语法来编写带有攻击目的的SQL语句作为参数，插入到原始语句中，让数据库执行，进而导致数据库中的数据不安全

2.案例：

• 2.1 存在的问题

# 登陆示例：
# 1.连接服务器
# 2.用户认证（在数据库中进行）
# 3.发送指令
# 4.提取结果
import pymysql

try:
	conn = pymysql.Connect(
		host = '127.0.0.1',  # 如果是本机，则可以忽略
		port = 3306,    # 如果没改过，可以忽略
		user = 'root',
		password = '123',
		database = 'mydb'
	)
	
	cursor = conn.cursor(pymysql.cursor.DictCursor)
	
	login_username = input('username>>>')
	login_password = input('password>>>')
	
	sql = "select * from login where name = '%s' and password = '%s'" %(login_username, login_password)
	
	count = cursor.execute(sql)
	
	if count:
		print('ok')
	else:
		print('error')
		
except Exception as e:
	print(e)

finally:
	if cursor: cursor.close()
	
	if conn: conn.close()
	
	'''尝试在用户名中输入以下内容,密码随意
 		jerry' — ass 
		或者连用户名都不用写
		' or 1 = 1 -- asaa
	'''

• 2.2 解决方案：

# 1.客户端在发送sql给服务器前进行re判断,这样的问题在于一些程序可以模拟客户端直接发送请求给服务器

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

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

try:
    conn = pymysql.connect(host="127.0.0.1",port=3306,user="root",password="",db="day46",)
    print("连接服务器成功!")
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    user = input("username:")
    password = input("password:")

    sql = "select *from user where name = %s and password = %s"
    print(sql)
    count = cursor.execute(sql,(user,password)) # 参数交给模块
    if count:
        print("登录成功!")
    else:
        print("登录失败!")
except Exception as e:
    print(type(e),e)
finally:
    if cursor: cursor.close()
    if conn: conn.close()

3.编写注册：

# 注意: 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,)):
    print("用户名已存在!")
else:
    sql2 = "insert  into user values(%s,%s)"
    if c.execute(sql2,(name,pwd)):
        print("注册成功!")
        conn.commit() # 调用连接对象的提交函数
    else:
        print("注册失败!")

c.close()
conn.close()

3.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)

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()

4.pymysql对于存储过程的应用

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

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)

res = c.callproc("add1",(1,2,1212)) # @_add1_0  @_add1_1  @_add1_2
c.execute("select @_add1_2")
print(c.fetchone())
# 调用存储过程时,传入参数,会自动定义成变量,

# 命名方式 @_过程的名称_参数的索引 从0开始