• MySQL数据库用户和权限管理


    一、视图

    视图:VIEW,虚表,保存有实表的查询结果,在视图插入的内容都会存入表中。
    创建方法:

    CREATE VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

    查看视图定义:SHOW CREATE VIEW view_name
    删除视图:

    DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

    视图中的数据事实上存储于“基表”中,因此,其修改操作也会针对基表实现;其修改操作受基表限制

    (1) 创建一个视图:

     create view v_students as select stuid,name,age  from  students; 创建一个新的视图,起名为v_students

     create view v_old_students  as select   stuid,name,age  from  students where age > 50; 将视图大于50岁以上的名字进行显示。

     inster v_old_students  values(27,'li',20);此时可以在视图中添加一个20岁的内容,实际添加到表里边,而视图中不会显示。

    二、函数

    1、函数:系统函数和自定义函数
         系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
    2、自定义函数 (user-defined function UDF)

     保存在mysql.proc表中
     创建UDF
        CREATE [AGGREGATE] FUNCTION function_name(parameter_name
        type,[parameter_name type,...])
        RETURNS {STRING|INTEGER|REAL}
        runtime_body

     说明:参数可以有多个,也可以没有参数必须有且只有一个返回值

    创建函数
    示例:无参UDF

    CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!”;
    查看函数列表:
    SHOW FUNCTION STATUS;
    查看函数定义
    SHOW CREATE FUNCTION function_name
    删除UDF:
    DROP FUNCTION function_name
    调用自定义函数语法:
    SELECT function_name(parameter_value,...)
    

    示例:有参数UDF

    DELIMITER //
    CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS
    VARCHAR(20)
    BEGIN
    DELETE FROM students WHERE stuid = uid;
    RETURN (SELECT COUNT(stuid) FROM students);
    END//
    DELIMITER ;
    

    自定义函数中定义局部变量语法
    DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
     说明:局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义
     示例:

    DELIMITER //
    CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT
    UNSIGNED)
    RETURNS SMALLINT
    BEGIN
    DECLARE a, b SMALLINT UNSIGNED;
    SET a = x, b = y;
    RETURN a+b;
    END//
    DELIMITER ;
    

    为变量赋值语法
    SET parameter_name = value[,parameter_name = value...
    SELECT INTO parameter_name
    示例:

    ...
    DECLARE x int;
    SELECT COUNT(id) FROM tdb_name INTO x;
    RETURN x;
    END//
    

    三、存储过程

    存储过程优势

    存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程提高了运行速度,同时降低网络数据传输量
    存储过程与自定义函数的区别
    存储过程实现的过程要复杂一些,而函数的针对性较强
    存储过程可以有多个返回值,而自定义函数只有一个返回值
    存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用

    存储过程:存储过程保存在mysql.proc表中
    创建存储过程

    CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
    routime_body 
    proc_parameter : [IN|OUT|INOUT] parameter_name type

    其中IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型
    查看存储过程列表

    SHOW PROCEDURE STATUS;
    

    查看存储过程定义

    SHOW CREATE PROCEDURE sp_name

    调用存储过程

    CALL sp_name ([ proc_parameter [,proc_parameter ...]])
    CALL sp_name

    说明:当无参时,可以省略"()",当有参数时,不可省略"()”
    存储过程修改

    ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改
    存储过程体,所以要修改存储过程,方法就是删除重建

    删除存储过程

    DROP PROCEDURE [IF EXISTS] sp_name
    

    存储过程示例:
    创建无参存储过程

    delimiter //
    CREATE PROCEDURE showTime()
    BEGIN
    SELECT now();
    END//
    delimiter ;
    CALL showTime;
    

    创建含参存储过程:只有一个IN参数

    delimiter //
    CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
    BEGIN
    SELECT * FROM students WHERE stuid = uid;
    END//
    delimiter ;
    call selectById(2);
    

    示例:

    delimiter //
    CREATE PROCEDURE dorepeat(n INT)
    BEGIN
    SET @i = 0;
    SET @sum = 0;
    REPEAT SET @sum = @sum+@i; SET @i = @i + 1;
    UNTIL @i > n END REPEAT;
    END//
    delimiter ;
    CALL dorepeat(100);
    SELECT @sum;
    

    创建含参存储过程:包含IN参数和OUT参数:

    delimiter //
    CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num
    SMALLINT UNSIGNED)
    BEGIN
    DELETE FROM students WHERE stuid >= uid;
    SELECT row_count() into num;
    END//
    delimiter ;
    call deleteById(2,@Line);
    SELECT @Line;

    说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数。

    四、触发器

    触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行
    创建触发器

    CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

    说明:

    trigger_name:触发器的名称
    trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发
    trigger_event::{ INSERT |UPDATE | DELETE },触发的具体事件
    tbl_name:该触发器作用在表名
    

    示例:

    CREATE TABLE student_info (
    stu_id INT(11) NOT NULL AUTO_INCREMENT,
    stu_name VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (stu_id)
    );
    CREATE TABLE student_count (
    student_count INT(11) DEFAULT 0
    );
    INSERT INTO student_count VALUES(0);
    

    示例:创建触发器,在向学生表INSERT数据时,学生数增加,DELETE学生时,学生数减少

    CREATE TRIGGER trigger_student_count_insert
    AFTER INSERT
    ON student_info FOR EACH ROW
    UPDATE student_count SET student_count=student_count+1;
    CREATE TRIGGER trigger_student_count_delete
    AFTER DELETE
    ON student_info FOR EACH ROW
    UPDATE student_count SET student_count=student_count-1;
    

    查看触发器

    SHOW TRIGGERS

    查询系统表information_schema.triggers的方式指定查询条件,查看指定的触发器信息。

    mysql> USE information_schema;
    Database changed
    mysql> SELECT * FROM triggers WHERE
    trigger_name='trigger_student_count_insert';

    删除触发器

    DROP TRIGGER trigger_name;
    

    五、MySQL用户和权限管理

    1、元数据数据库:mysql
     系统授权表:

    db, host, user
    columns_priv, tables_priv, procs_priv, proxies_priv

    用户账号:

    'USERNAME'@'HOST'    允许用户通过哪些主机远程连接mysqld 服务
    @'HOST':
    主机名
    IP地址或Network
    通配符: % _
    示例:172.16.%.%

    2、用户管理

    创建用户:CREATE USER
    示例:

    create  user  test@'192.168.34.%'  identified  by 'centos';添加test账号在192.168.34这个网段,可以输centos密码连接
    select user,host from user; 在创建新数据库上查询哪些host主机登陆了当前的mysql数据库。

    默认权限:USAGE
    用户重命名:RENAME USER
    RENAME USER old_user_name TO new_user_name;
    删除用户:
    DROP USER 'USERNAME'@'HOST‘

    示例:

    drop  user ''@'localhost'; 删除空的匿名用户
    drop user ''@'centos7-1'; 空值部分要加单引号。

    示例:删除默认的空用户
    DROP USER ''@'localhost';

    (1)创建用户:

    create  user  test@'192.168.34.%'  identified  by 'centos';

    (2)在另一台主机上登陆mysql数据库:

    mysql -utest -pcentos  -h192.168.34.102;(IP地址是要远程连接到主机的IP地址)

     (3)查询当前登陆到mysql的账号信息:

          select  user,host from user;

     (4)删除空的匿名用户账号:

         drop  user ''@'localhost'; 删除空的匿名用户

         drop user ''@'centos7-1'; 空值部分要加单引号。

    修改密码:

    SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');

     示例:

    1、set password for test@'192.168.34.%'=password('biubiu'); 此方法立即生效
    2、UPDATE mysql.user SET password=PASSWORD('password')
     WHERE clause;
    此方法需要执行下面指令才能生效:
    FLUSH PRIVILEGES;

     #mysqladmin -u root -poldpass password ‘newpass’ 也可以改口令。
    忘记管理员密码的解决办法:

    1、启动mysqld进程时,为其使用如下选项:
       skip-grant-tables 忽略授权表,取消数据库的授权         skip-networking  数据库没有网络功能,避免其他用户连接不输入口令连接。
    2、使用UPDATE命令修改管理员密码
    3、关闭mysqld进程,移除上述两个选项,重启mysqld
    

     破解数据库步骤:

    (1)vim /etc/my.cnf  将mysql数据库配置文件打开

    vim /etc/my.cnf
    [mysqld]
    skip-grant-tables
    skip-networking
    

    (2)重启mysql服务

    systemctl restart mariadb
    

    (3)更新数据库密码:

     update mysql.user  set  password=password('magedu')  where  user='root';

    (4)再将之前写入Mysql配置文件内容注释掉:

    [root@centos7~]#vim /etc/my.cnf
    
    [mysqld]
    #skip-grant-tables
    

    (5)重启Mysql服务

    systemctl restart mariadb
    

    (6)用修改后的密码就可以登录了

    mysql  -pmagedu

    3、权限类别:
        管理类
        程序类
        数据库级别
        表级别
        字段级别

    1、管理类:

    CREATE TEMPORARY TABLES
    CREATE USER
    FILE
    SUPER
    SHOW DATABASES
    RELOAD
    SHUTDOWN
    REPLICATION SLAVE
    REPLICATION CLIENT
    LOCK TABLES
    PROCESS

    2、程序类: FUNCTION、PROCEDURE、TRIGGER

    CREATE
    ALTER
    DROP
    EXCUTE

    3、库和表级别:DATABASE、TABLE

    ALTER
    CREATE
    CREATE VIEW
    DROP
    INDEX
    SHOW VIEW
    GRANT OPTION:能将自己获得的权限转赠给其他用户
    

    4、数据操作

    SELECT
    INSERT
    DELETE
    UPDATE

    5、字段级别

    SELECT(col1,col2,...)
    UPDATE(col1,col2,...)
    INSERT(col1,col2,...)

    6、所有权限

    ALL PRIVILEGES 或 ALL
    

    4、授权和回收授权

    授权:

    参考:https://dev.mysql.com/doc/refman/5.7/en/grant.html
    GRANT   priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];  授权并创建账

    (1) priv_type: ALL [PRIVILEGES] 授权类型:
    
        insert增,delete删 , update改,select查,all所有权限
    
    (2) db_name.tb_name: 对哪个数据库的哪个表授权:
    
      *.*: 所有库的所有表
    
      db_name.*: 指定库的所有表
    
      db_name.tb_name: 指定库的指定表
    
      db_name.routine_name :指定库的存储过程和函数、触发器

    示例:

    grant  all on  hellodb.*  to  test2@'192.168.34.%' identified by 'centos'; 创建test2用户,允许其在所有主机通过centos密码登录,对hellodb库的所有表有所有权限
    
    grant  select(name,age) on  hellodb.students  to  test3@'192.168.34.%' identified by 'centos'; 创建test3用户,允许其在所有主机通过centos密码登录,对hellodb库的name和age有查看权限。
    
    show grants  for  test2@'192.168.34.%' G  可以查看当前用户的授权情况,G换行显示

    回收授权:

    REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host
    
    示例: revoke   select on   *.*  from  test3@'192.168.34.%'; 收回test3的select 权限
    
    revoke   delete  on   *.*  from  test3@'192.168.34.%';  收回test3  的delete 权限

    查看指定用户获得的授权

    Help SHOW GRANTS
    SHOW GRANTS FOR 'user'@'host';
    SHOW GRANTS FOR CURRENT_USER[()];

    注意:

    ① MariaDB 服务进程启动时会读取mysql 库中所有授权表至内存

    ② GRANT 或REVOKE 等执行权限操作会保存于系统表中,MariaDB 的服务进程通常会自动重读授权表,使之生效

    ③ 对于不能够或不能及时重读授权表的命令,可手动让MariaDB 的服务进程重读授权表:

      mysql> FLUSH PRIVILEGES;

     

  • 相关阅读:
    alpha冲刺9
    alpha冲刺8
    alpha冲刺7
    alpha冲刺6
    团队作业——随堂小测(同学录)
    alpha冲刺5
    【麻瓜制造者】团队项目测试报告与用户反馈
    Android基础学习
    学习博客之工具的学习、安装和使用
    学习博客之Java继承多态接口
  • 原文地址:https://www.cnblogs.com/struggle-1216/p/11850658.html
Copyright © 2020-2023  润新知