11-3 数据库的用户管理和pymysql模块的使用

比眉伴天荒 2023-08-17 15:24 81阅读 0赞

*  1 用户管理
    
     *  1.1 创建mysql用户
     *  1.2 权限管理
        
         *  1.2.1 授权
         *  1.2.2 取消权限
 *  2 pymysql 模块
    
     *  2.1 基本使用
     *  2.2 sql注入攻击
     *  2.3 修改数据
     *  2.4 调用存储过程

# 1 用户管理 #

对数据库的管理工作一般是由专门的DBA人员处理,但是小公司没有DBA的情况很常见,所以我们有时也需要承担数据库的维护.

数据库最重要的就是数据,所以对用户的权限管理就显得很重要.不然哪一天,就可能出现丢失数据和删库的情况.

## 1.1 创建mysql用户 ##

首先创建一个mysql用户,涉及到的3个数据:

用户名   密码    IP地址
    # 其中IP地址适用于限制某个账户只能在某台机器上登陆,也可以限制在某个IP段

创建用户的语法:

# 创建一个用户yaco,可以在本地localhost上登陆,密码为123.
    create user yaco@localhost identified by '123';
    
    # 注意 : 操作用户  只能由 root账户来进行
    # 如果想要让某个账户也可以创建账户,可以在授权的时候,指定
    # 这是一种创建用户的方式,还有一张方式可以同时创建账户和授权,建议使用接下来的方式.

**==删除用户: 将同时删除所有权限==**

drop user 用户名@主机地址;

## 1.2 权限管理 ##

### 1.2.1 授权 ###

# 语法:
    
    grant all on *.* to 用户名@主机地址 identified by '密码';
    
    flush privileges; # 刷新权限
    
    # all 表示的是对所有字段的增删改查
    # *.* 表示的是 所有库的所有表
    # 这是给这个用户 所有的库表的所有操作权限
    # 如果用户不存在,则自动创建这个新用户.
    # 如果 用户存在,后面的identified by '密码' 就可以不用写了
    
    
    # 我们也可以限制用户只能操作哪个数据库
    # 下面的是表示yaco用户只能操作day042这个数据库.通过show databases 可以发现,只显示对应的数据库.
    grant all on day042.* to yaco@localhost ;
    # 这样创建的用户,我们可以在 mysql库中的user表查看是否已经生成这个账户.同时,在db表中也会有该用户的信息.
    
    
    # 我们也就可以限制用户只能操作哪张表
    # 下面的是表示 yaco用户只能操作day041数据库下的table1表,其他表对这个用户不可见,也无权限查询.
    grant all on day041.table1 to yaco@localhost ;
    # 这样创建的用户,我们可以在user表总查看到用户信息,同时也可在table_priv 表中查看到用户的信息.
    
    
    # 甚至  我们也可以限制用户只能操作某张表的某个字段
    # 下面的yaco用户只能操作table1表的name字段的查询和更新操作.其他操作无权限
    grant select(name),update(name) on day042.table1 to yaco@localhost;
    # 这样创建的用户,我们可以在user表总查看到用户信息,同时也可以在columns_priv中查看
    
    
    # 如果想要让某个用户也可以创建用户或者授权,可以使用with grant option
    # 表示 可以将它拥有的权限授予其他的用户
    grant all on *.* to yaco@localhost identified by '123' with grant option;
    
    # 限制IP段访问
    # % 表示任意IP都可以连接到数据库
    grant all on *.* to yaco@'%' identified by '123';

### 1.2.2 取消权限 ###

取消权限可以使用revoke

# 语法:
    # 取消yaco的所有权限
    revoke all on *.*  from yaco@localhost
    
    # 取消yaco table1的权限
    revoke all on day42.table1 from yaco@localhost;

# 2 pymysql 模块 #

## 2.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'
    )
    # 2 通过连接拿到游标对象
    # 默认的游标返回的是元组类型  不方便使用,需要更换成字典类型的游标
    cur = conn.cursor(pymysql.cursor.DictCursor)
    
    # 3 执行sql语句
    # execute执行后返回的是受影响的行数,
    # 这个返回的行数一般没什么使用场景
    sql = 'select * from table1'
    cur.execute(sql)
    
    # 4 提取结果
    print(cur.fetchone()) # 获取1个记录
    print(cur.fetchmany(个数)) # 获取指定个数的记录
    print(cur.fetchall())# 获取全部记录
    
    # 5 关闭连接
    cur.close()
    conn.close()
    
    
    
    # 光标
    # 调整光标位置,可以改变我们读取的记录顺序
    # 参数1表示移动的位置,向后移一位,1,向前一位就是-1
    # mode 指定相对或者是 绝对
    cur.scroll(1,mode = 'absolutie')

## 2.2 sql注入攻击 ##

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

如何避免

1.  我们可以在服务器端执行sql之前做sql语句的验证
2.  pymysql已经封装了sql的验证,我们只需要将参数交给pymysql来做拼接即可

## 2.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()

## 2.4 调用存储过程 ##

# 我们先在数据库中创建一个add1过程
    delimiter |
    create procedure add1(in a int,in b int,out c int)
    begin
    set c = a + b;
    end |
    delimiter ;

# 在pymysql中调用这个add1过程
    import pymysql
    
    conn = pymysql.connect(
        host = '127.0.0.1',
        user = 'root',
        password = '123',
        port = 3306,
        database = 'day41'
    )
    
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cur.callproc('add1',(1,2,_))  
    # 调用存储过程时,传入参数,会自动定义成变量
    # 命名方式 @_过程名_索引  ,注意从0开始
    # @_add_0  @_add_1   @_add_2
    cur.execute('select @_add1_2')
    print(cur.fetchone())

转载于:https://www.cnblogs.com/chenych/p/11227794.html