数据库（六）

前言

本篇博客学习 mysql 的用户管理和使用 python 操作 mysql 的一个模块 pymysql。

mysql 用户管理

因为数据安全对于互联网公司来说是最重要的，不可能随便分配 root 账户，应该按照不同开发岗位分配不同的账户和权限。

用户管理相关表

mysql> use mysql;
mysql> show tables;

用户对数据库的权限顺序

mysql 将与用户相关的数据放在 mysql库中，

user->db->tables_priv->columns_priv

如果用户拥有对所有库的访问权，则存储在 user 中；

如果用户拥有对部分库的使用权，则存储在 db 中；

如果用户拥有对部分表的使用权，则存储在 tables_priv 中；

如果用户拥有对表中某些字段的使用权，则存储在 columns_priv 中。

用户授权设置

创建新账户

mysql> create user 用户名@ip identified by '密码';

mysql> create user musibii@127.0.0.1 identified by '123';

创建成功后会在 user 表中查看到：

mysql> select * from user\g;

这样创建的账户是没有任何权限的，可以看出关于权限字段的值全为 n，接下来需要给 musibii 账号分配权限了。

授予用户最高权限

mysql> grant all on *.* to musibii@127.0.0.1 identified by '123';

如果 musibii 账号不存在，那么会在授予权限的时候自动创建账号。

现在查看一下 musibii 账号的权限关系：

授予授予权限

现在除了 grant_priv 权限其他权限都有了，这个权限是授权权限。通过 root 账户授予：

mysql> grant all on *.* to musibii@127.0.0.1 identified by '123' with grant option;

现在可以说这个账号就是一个 root 账户，但是只能在本机登陆。

授予在任何地方登陆权限

mysql> grant all on *.* to musibii@'%' identified by '123';

现在 host 字段对应的值变为了%，意味着可以在任何主机登陆该数据库了。

设置只能访问某个库

mysql> grant all on day46 to eureka@'%' identified by '123';

这样设置的用户权限信息存储在 db 中，但是不知道为什么在 db 中没有账户信息。。。

设置能能访问某个库中的某个表

mysql> grant all on day46.emp to thales@'%' identified by '123';

设置只能增删查改权限

mysql> grant select(name) on day46.emp to thales@'%' identified by '123';

这样 thales 用户就只能对 emp 表中的 name 字段进行查询操作了。

用户收权设置

收回某个账号的所有权限

mysql> revoke all privileges [column] on db.table from user@'host';

mysql> revoke all privileges on *.* from musibii@'127.0.0.1';

查询 user 表中的权限信息，发现除了授权权限其他权限都没有了：

不过在修改权限之后需要刷新权限表：

mysql> flush privileges;

注意：如何授予权限就应该如何收回权限，因为不同的权限存储在不同的表中

删除用户

mysql> drop user 用户名@主机;

当你在云服务器部署了 mysql 环境时，你的程序无法直接连击服务器，需要授予在任意一台电脑登陆的权限

mysql> grant all on *.* to 用户名@'%' identified by 密码 with grant option;

pymysql

pymysql 连接数据库

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='day46',
    charset='utf8')

操作数据库

cursor 游标对象，负责执行 sql 语句，获取返回的数据

cursor = conn.cursor()
sql = 'select *from emp'
res = cursor.execute(sql) # 返回值是本次查询的记录条数

# 获取一条记录
print(cursor.fetchone())
# 接着获取
print(cursor.fetchone())
# 提取所有结果
print(cursor.fetchall())

print(fetchamany(3))

运行结果

((3, '关羽', '男', '市场', '员工', 4000.0), (4, '孙权', '男', '行政', '总监', 6000.0), (5, '周瑜', '男', '行政', '员工', 5000.0))

参数是指定获取的数据条数。

控制游标位置

cursor.scroll(1,mode='relative) # 游标从当前位置往后移动一条记录
cursor.scroll(1,mode='absolute') # 游标从开始位置往后移动一条记录

获取结果使用字典表示

指定使用字典类型的游标，默认是元祖类型

cursor = conn.cursor(pymysql.cursor.dictcursor)

在 python 使用代码执行了增删查改后只是在内存中修改了值，想要修改数据库中的值需要提交操作

conn.commit()

这样修改的值才会改变。

sql 注入

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='password',
    database='day46',
    charset='utf8')


cursor = conn.cursor(pymysql.cursors.dictcursor)

name = input('用户名')
pwd = input('密码')

sql = 'select * from user where name = "%s" and pwd = "%s"' % (name, password)

res = cursor.execute(sql)
if res:
    print('登陆成功')
else:
    print('登陆失败')

注入写法

避免方式：

1. 再输入时加上正则判断，不允许输入与 sql 相关的关键词。这种方式无法避免代理服务器发起的攻击；
2. 2.在服务端，执行 sql 前进行判断。

pymysql 中已经做了处理，只要将参数的拼接交给 mysql 来完成就能够避免攻击。

代码实现

name = input('用户名')
pwd = input('密码')

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

res = cursor.execute(sql, (name,pwd)) # 这样执行 sql 语句就可以避免攻击。