MySQL数据库用户和权限管理

一、视图

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

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

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

?
1
2
3
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)

?
1
2
3
4
5
6
保存在mysql.proc表中
创建UDF
    CREATE [AGGREGATE] FUNCTION function_name(parameter_name
    type,[parameter_name type,...])
    RETURNS {STRING|INTEGER|REAL}
    runtime_body

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

创建函数
示例：无参UDF

?
1
2
3
4
5
6
7
8
9
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

?
1
2
3
4
5
6
7
8
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的第一行定义
 示例:

?
1
2
3
4
5
6
7
8
9
10
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
示例:

?
1
2
3
4
5
...
DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x;
RETURN x;
END//

三、存储过程

存储过程优势

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

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

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

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

?
1
SHOW PROCEDURE STATUS;

查看存储过程定义

?
1
SHOW CREATE PROCEDURE sp_name

调用存储过程

?
1
2
CALL sp_name ([ proc_parameter [,proc_parameter ...]])
CALL sp_name

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

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

删除存储过程

?
1
DROP PROCEDURE [IF EXISTS] sp_name

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

?
1
2
3
4
5
6
7
delimiter //
CREATE PROCEDURE showTime()
BEGIN
SELECT now();
END//
delimiter ;
CALL showTime;

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

?
1
2
3
4
5
6
7
delimiter //
CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
BEGIN
SELECT * FROM students WHERE stuid = uid;
END//
delimiter ;
call selectById(2);

示例：

?
1
2
3
4
5
6
7
8
9
10
11
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参数：

?
1
2
3
4
5
6
7
8
9
10
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;输出被影响行数。

四、触发器

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

?
1
2
3
4
5
6
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body

说明：

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

示例：

?
1
2
3
4
5
6
7
8
9
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学生时，学生数减少

?
1
2
3
4
5
6
7
8
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;

查看触发器

?
1
SHOW TRIGGERS

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

?
1
2
3
4
mysql> USE information_schema;
Database changed
mysql> SELECT * FROM triggers WHERE
trigger_name='trigger_student_count_insert';

删除触发器

?
1
DROP TRIGGER trigger_name;

五、MySQL用户和权限管理

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

?
1
2
db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv

用户账号：

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

2、用户管理

创建用户：CREATE USER
示例：

?
1
2
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‘

示例：

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

　破解数据库步骤：

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

?
1
2
3
4
vim /etc/my.cnf
[mysqld]
skip-grant-tables
skip-networking

（2）重启mysql服务

?
1
systemctl restart mariadb

（3）更新数据库密码：

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

（4）再将之前写入Mysql配置文件内容注释掉：

?
1
2
3
4
[root@centos7~]#vim /etc/my.cnf
 
[mysqld]
#skip-grant-tables

（5）重启Mysql服务

?
1
systemctl restart mariadb

（6）用修改后的密码就可以登录了

?
1
mysql  -pmagedu

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

1、管理类：

?
1
2
3
4
5
6
7
8
9
10
11
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS

2、程序类： FUNCTION、PROCEDURE、TRIGGER

?
1
2
3
4
CREATE
ALTER
DROP
EXCUTE

3、库和表级别：DATABASE、TABLE

?
1
2
3
4
5
6
7
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能将自己获得的权限转赠给其他用户

4、数据操作

?
1
2
3
4
SELECT
INSERT
DELETE
UPDATE

5、字段级别

?
1
2
3
SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)

6、所有权限

?
1
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
2
3
4
5
6
7
8
9
10
11
12
13
（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 ：指定库的存储过程和函数、触发器

示例：

?
1
2
3
4
5
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换行显示

回收授权：

?
1
2
3
4
5
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 权限

查看指定用户获得的授权

?
1
2
3
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];

注意：

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

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

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

  mysql> FLUSH PRIVILEGES;

 

　

 

posted @   一叶知秋~~  阅读(11465)  评论(0)  编辑  收藏  举报
编辑推荐：
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
阅读排行：
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源！
· 周边上新：园子的第一款马克杯温暖上架
点击右上角即可分享
微信分享提示
AI IDE Trae