MySQL存款和储蓄进度的权能难题总计

by admin on 2019年10月4日

一、联结表

  数据仍使用前文中的数据。

   MySQL中以用户执行存储过程的权限为EXECUTE

MySQL的存储过程,没错,看起来好生僻的使用场景。问题源于一个开发同学提交了权限申请的工单,需要开通一些权限。

1. [代码]查看用户权限    

?

 
show grants for 你的用户;
show grants for root@'localhost';
show grants for webgametest@10.3.18.158;
show create database dbname;  这个可以看到创建数据库时用到的一些参数。
show create table tickets;    可以看到创建表时用到的一些参数

  1、子查询

  作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

-- 作为查询条件使用
-- 查看TNT2订单对应的客户ip(order表)
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');
-- 根据TNT2订单的客户ip查看客户信息(cust表)
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2'));
-- 作为计算字段使用
-- 计算每个客户的订单数据
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

  比如我们在名为configdb的数据库下创建了如下存储过程,存储过程的定义者为user_admin

 

本来是一个很正常的操作,但在我来看是比较着急且紧迫的,说来惭愧,忙着方向规划和开发的事情,这个基础的操作功能竟然给忽略了,所以看到目前的一些实现方式,还是希望能够做一些细小的事情把这些重复性的工作给解放了。

2. [代码][SQL]代码    

?

 
GRANT USAGE ON *.* TO 'discuz'@'localhost' IDENTIFIED BY PASSWORD '*C242DDD213BE9C6F8DA28D49245BF69FC79A86EB';
GRANT ALL PRIVILEGES ON `discuz`.* TO 'discuz'@'localhost';
 
我先按我的理解解释一下上面两句的意思
建立一个只可以在本地登陆的 不能操作的用用户名 discuz 密码为 ***** 已经加密了的
然后第二句的意思是 ,给这个discuz用户操作discuz数据库的所有权限
 
使用GRANT
 
GRANT命令用来建立新用户,指定用户口令并增加用户权限。其格式如下:
 
mysql> GRANT <privileges> ON <what>
-> TO <user> [IDENTIFIED BY "<password>"]
-> [WITH GRANT OPTION];
 
 
  正如你看到的,在这个命令中有许多待填的内容。让我们逐一地对它们进行介绍,并最终给出一些例子以让你对它们的协同工作有一个了解。
 
  <privileges>是一个用逗号分隔的你想要赋予的权限的列表。你可以指定的权限可以分为三种类型:
 
  数据库/数据表/数据列权限: Alter: 修改已存在的数据表(例如增加/删除列)和索引。
Create: 建立新的数据库或数据表。
Delete: 删除表的记录。
Drop: 删除数据表或数据库。
INDEX: 建立或删除索引。
Insert: 增加表的记录。
Select: 显示/搜索表的记录。
Update: 修改表中已存在的记录。
 
  全局管理权限:
 
file: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表,刷新日志等。
SHUTDOWN: 关闭MySQL服务。
 
  特别的权限:
 
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录--其它什么也不允许做。
 
 
  这些权限所涉及到的MySQL的特征,其中的一些我们至今还没看到,而其中的绝大部分是你所熟悉的。
 
<what> 定义了这些权限所作用的区域。*.*意味着权限对所有数据库和数据表有效。dbName.*意味着对名为dbName的数据库中的所有数据表有效。 dbName.tblName意味着仅对名为dbName中的名为tblName的数据表有效。你甚至还可以通过在赋予的权限后面使用圆括号中的数据列的列表以指定权限仅对这些列有效(在后面我们将看到这样的例子)。
 
  <user>指定可以应用这些权限的用户。在MySQL中,一个用户通过它登录的用户名和用户使用的计算机的主机名/IP地址来指定。这两个值都可以使用%通配符(例如kevin@%将允许使用用户名kevin从任何机器上登录以享有你指定的权限)。
 
  <password>指定了用户连接MySQL服务所用的口令。它被用方括号括起,说明IDENTIFIED BY "<password>"GRANT命令中是可选项。这里指定的口令会取代用户原来的密码。如果没有为一个新用户指定口令,当他进行连接时就不需要口令。
 
这个命令中可选的WITH GRANT OPTION部分指定了用户可以使用GRANT/REVOKE命令将他拥有的权限赋予其他用户。请小心使用这项功能--虽然这个问题可能不是那么明显!例如,两个都拥有这个功能的用户可能会相互共享他们的权限,这也许不是你当初想看到的。
 
  让我们来看两个例子。建立一个名为dbmanager的用户,他可以使用口令managedb从server.host.net连接 MySQL,并仅仅可以访问名为db的数据库的全部内容(并可以将此权限赋予其他用户),这可以使用下面的GRANT命令:
 
mysql> GRANT ALL ON db.*
-> TO dbmanager@server.host.net
-> IDENTIFIED BY "managedb"
-> WITH GRANT OPTION;
 
 
现在改变这个用户的口令为funkychicken,命令格式如下:
   mysql> GRANT USAGE ON *.*
   -> TO dbmanager@server.host.net
   -> IDENTIFIED BY "funkychicken";
 
请注意我们没有赋予任何另外的权限(the USAGE权限只能允许用户登录),但是用户已经存在的权限不会被改变。
 
  现在让我们建立一个新的名为jessica的用户,他可以从host.net域的任意机器连接到MySQL。他可以更新数据库中用户的姓名和 email地址,但是不需要查阅其它数据库的信息。也就是说他对db数据库具有只读的权限(例如,Select),但是他可以对Users表的name列和email列执行Update操作。命令如下:
 
mysql> GRANT Select ON db.*
-> TO jessica@%.host.net
-> IDENTIFIED BY "jessrules";
mysql> GRANT Update (name,email) ON db.Users
-> TO jessica@%.host.net;
 
 
  请注意在第一个命令中我们在指定Jessica可以用来连接的主机名时使用了%(通配符)符号。此外,我们也没有给他向其他用户传递他的权限的能力,因为我们在命令的最后没有带上WITH GRANT OPTION。第二个命令示范了如何通过在赋予的权限后面的圆括号中用逗号分隔的列的列表对特定的数据列赋予权限。
 
-----------------------------------------------------
 
mysql> grant select,insert,update,delete on 111.* to hongdadbuser@"%" identified by "111";
 
 
 
 
测试环境:WIN32 mysql5.0.45 
 
 
首先要声明一下:一般情况下,修改MySQL密码,授权,是需要有mysql里的root权限的。
 
注:本操作是在WIN命令提示符下,phpMyAdmin同样适用。
    用户:phplamp  用户数据库:phplampDB
 
1.新建用户。
 
//登录MYSQL
@>mysql -u root -p
@>密码
//创建用户
mysql> insert into mysql.user(Host,User,Password) values("localhost","phplamp",password("1234"));
//刷新系统权限表
mysql>flush privileges;
这样就创建了一个名为:phplamp  密码为:1234  的用户。
 
然后登录一下。
 
mysql>exit;
@>mysql -u phplamp -p
@>输入密码
mysql>登录成功
 
2.为用户授权。
 
//登录MYSQL(有ROOT权限)。我里我以ROOT身份登录.
@>mysql -u root -p
@>密码
//首先为用户创建一个数据库(phplampDB)
mysql>create database phplampDB;
//授权phplamp用户拥有phplamp数据库的所有权限。
>grant all privileges on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表
mysql>flush privileges;
mysql>其它操作
 
/*
如果想指定部分权限给一用户,可以这样来写:
mysql>grant select,update on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表。
mysql>flush privileges;
*/
 
3.删除用户。
@>mysql -u root -p
@>密码
mysql>DELETE FROM user WHERE User="phplamp" and Host="localhost";
mysql>flush privileges;
//删除用户的数据库
mysql>drop database phplampDB;
 
4.修改指定用户密码。
@>mysql -u root -p
@>密码
mysql>update mysql.user set password=password('新密码') where User="phplamp" and Host="localhost";
mysql>flush privileges;
 
-------------------------------------------------
 
mysql> grant all privileges on webgame_test.* to webgametest@10.3.18.158 identified by 'gametestdb';
Query OK, 0 rows affected (0.01 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
 
mysql> exit;
 
show grants for webgametest@10.3.18.158;

url:

  2、创建链接

 图片示例  链接方式 关键字 语句示例
  内连接 INNER JOIN … ON SELECT <select_list> FROM A INNER JOIN B ON A.key = B.key
左外连接 LEFT JOIN … ON SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key
左外连接 LEFT JOIN … ON … WHERE B.key IS NULL SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key WHERE B.key IS NULL
右外连接 RIGHT JOIN … ON SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key
右外连接 RIGHT JOIN … ON … WHERE A.key IS NULL SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key WHERE A.key IS NULL
全外连接 UNION

SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key

UNION

SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key

全外连接 (WHER IS NULL) UNION … (WHER IS NULL)

SELECT <select_list> FROM A LEFT JOIN B ON A.key = B.key WHERE B.key IS NULL

UNION

SELECT <select_list> FROM A RIGHT JOIN B ON A.key = B.key WHERE A.key IS NULL

威尼斯人娱乐官网,   用例:

-- 笛卡尔积:n * m
-- SELECT * FROM vendors, products;
SELECT orders.cust_id, cust_name FROM orders CROSS JOIN customers;-- 没有WHERE子句
-- 内连接
-- SELECT * FROM vendors, products WHERE vendors.vend_id = products.vend_id;
SELECT * FROM vendors INNER JOIN products on vendors.vend_id = products.vend_id;
-- 左外连接(a),根据A表相应字段取列
SELECT orders.cust_id, cust_name FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id;
-- 左外连接b,只取A表中无B表相应字段的列
SELECT vendors.vend_id, vend_name, vend_country  FROM vendors LEFT JOIN products on vendors.vend_id = products.vend_id where products.vend_id IS NULL ORDER BY vend_id;
-- 右外连接(a),同左外连接(a)
SELECT orders.cust_id, cust_name FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id;
-- 右外连接(b),同左外连接(b)
SELECT customers.cust_id, cust_name FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL;

-- 全外连接,两者id相等的行和不相等的行都保留
SELECT customers.cust_id, orders.order_num FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id
UNION
SELECT customers.cust_id, orders.order_num FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id;
-- 全外连接,只保留两者id不相等的行,这里两个WHERE子句一样是因为customers的id字段完全包含orders中的字段
SELECT customers.cust_id, orders.order_num FROM orders LEFT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL
UNION
SELECT customers.cust_id, orders.order_num FROM orders RIGHT JOIN customers on customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL

  use configdb;

MySQL grant不能在on后面写多个对象

当然我决定把一些基础性的工作接过来,一方面是给同事减压,另一方面是在做一个完整的体验,因为很多需求和痛点通过实践是能够很容易捕捉到重点的,如果我觉得不合理,那么这个过程中势必会有一些改进的地方。比如部署安装,比如权限开通。数据库的权限开通就是一个相对典型的案例,而存储过程的权限开通甚至都有点让人怀疑人生了。

  3、视图

  视图是虚拟的表。它只包含使用时动态检索数据的查询,换言之,视图存储查询语句。保存查询语句可以使用视图。视图的一些规则:

  - 与表名一样,视图必须唯一命名。

  - 视图数目没有限制。

  - 为了创建视图,必须具有足够的访问权限。

  - 视图可以嵌套。

  - 视图查询中不要使用GROUP BY。

  - 视图不能索引,也不能有关联的触发器或默认值。

-- 创建视图
-- CREATE VIEW viewname AS SELECT *
CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;

CREATE VIEW orderitemsexpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;

-- 执行视图查询时会首先执行视图
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id ='FB';
SELECT * FROM orderitemsexpanded WHERE order_num = 20009;
-- 删除视图
-- DROP VIEW viewname;
DROP VIEW productcustomers;

  drop procedure if exists sp_dev_test_user_add;

问题的场景还是很基础的,开发同学需要开通一些基础的权限,在标记权限的时候声明需要增删改查的权限,还有DDL的权限,比如drop,alter,create等等。看到这里,我就感觉不太妥了,什么样的操作竟然需要这么大的权限呢。

二、存储过程

  delimiter $$

简单声明了下立场,开发同学的想法是能够方便管理,于是乎我就直接招过去了,简单沟通下,其实发现他们的需求场景还是很常规的,他们需要动态创建一些日表,那么需要create权限在评估之后是可以给与的,而对于一般的用户而言,create的权限是不建议开放的,主要的出发点就是能够对SQL进行一些基本的审核,哪怕是人工审核还是平台审核都是一个需要的过程。所以沟通了一圈发现,开通的权限就可以迅速裁剪,对他们而言,修改存储过程的逻辑也是需要的,因为在一些特定的场景下,他们对逻辑的控制希望能够更加灵活。

  1、存储过程

  CREATE DEFINER=`user_admin`@`%` PROCEDURE
`sp_dev_test_user_add`(

好了,基础的背景介绍完了。赋予基本的表的权限,赋予存储过程的权限,存储过程的这个地方需要注意一个重要的点是SQL
SECURITY,默认创建是definer,如果需要开放给其他的用户调用,则建议是设置为invoker.

  1.简介

  存储过程是存储在数据库目录中的一段声明性SQL语句。它像是编程语言中的函数或者可执行代码块。通过声明一段可执行的SQL语句,既可以避免一行行的输入SQL语句,又可以将这段SQL语句重复使用。

  存储过程的优点:

  - 通常存储过程有助于提高应用程序的性能。

  -
存储过程有助于减少应用程序和数据库服务器之间的流量,因为应用程序不必发送多个冗长的SQL语句,而只能发送存储过程的名称和参数。

  - 存储的程序对任何应用程序都是可重用的和透明的。

  - 存储的程序是安全的。

  存储过程的缺点:

  -
如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。

  - 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。

  - 很难调试存储过程。

  - 开发和维护存储过程并不容易。

  in var_user varchar(30),

所以很简单的一句:

  2.使用

-- 创建存储过程
DELIMITER // -- DELIMITER // 和DELIMITER;用于划分一块范围来声明存储过程
CREATE PROCEDURE GetAllProducts()-- CRAET PROCEDURE 创建一个存储过程
   BEGIN-- 存储过程的主体的开始
   DECLARE id INT(20) DEFAULT 1003;-- DECLARE variable datatype(size) DEFAULT value,声明局部变量;它只能在BEGIN和END之间生效
    SET id = 1001;-- 变量赋值
   SELECT * FROM products WHERE vend_id = id;-- SQL语句
   END //-- 存储过程的主体的结束,结尾用//
DELIMITER ;

-- 调用存储过程
CALL GetAllProducts();
-- 删除存储过程
DROP PROCEDURE IF EXISTS `GetAllProducts`;

-- 设置参数
DELIMITER //
CREATE PROCEDURE GetProductsByVendId(IN id INT(20), OUT outcome VARCHAR(20))-- 参数默认前缀是IN,即只允许调用函数时给参数传递值;OUT则表示将存储过程的运行结果传递出去,它是个单值参数;
    BEGIN
    SELECT count(*) INTO outcome FROM products WHERE vend_id = id;
    END //
DELIMITER ;
CALL GetProductsByVendId(1003, @outcome);-- 不直接打印结果,而是把结果传递给了全局变量@outcome
SELECT @outcome;-- 设置全局变量可以用SET variable = value;

-- 条件语句
/* IF condition THEN 
    sql
ELSEIF condition THEN
    sql
...
ELSE 
    sql
END IF */

DELIMITER //
CREATE PROCEDURE GetPriceLevel(IN prod_id CHAR(10), OUT price_level VARCHAR(20))
    BEGIN
    DECLARE price DECIMAL(8,2);
    SELECT prod_price INTO price FROM products WHERE products.prod_id = prod_id;

    IF price <= 5 THEN
        SET price_level = 'cheap';
    ELSEIF (price > 5 AND price <= 10) THEN
        SET price_level = 'ordinary';
    ELSE
        SET price_level = 'expensive';
    END IF;
    END //
DELIMITER ;
CALL GetPriceLevel('ANV01', @price_list);
SELECT @price_list;

-- 循环语句
/* WHILE condition DO
   statements
END WHILE */
-- 函数 略
-- 游标 https://www.yiibai.com/mysql/cursor.htm

  in var_ip varchar(15),

grant execute,alter procedure on xxx.xxx to xxx@'xxxx'; 

  2、事务处理

  in var_username varchar(30),

但是很不幸的,开发同学反馈,他们通过SQLyog或者是Navicator打开的时候,竟然看不到存储过程的内容。

  1.概念

  事务处理(transaction
processing)用于保证SQL操作的完整性。它提供一种处理机制来应对SQL或者其它环境因素可能造成的异常结果。事务处理中的几个术语:

  - 事务(transaction),指一组SQL语句。

  - 回退(rollback),指撤销指定SQL语句的过程。

  - 提交(commit),指将未存储的SQL语句结果些人数据库表。

  -
保留点(savepoint),指事务处理中设置的临时占位符(placeholder),它可以回退。

  事务处理用来管理(可以回退)INSERT、UPDATE和DELETE语句,不能回退SELECT语句,也不能回退CREATE或者DROP操作。

  在MySQL中,事务开始使用COMMIT或ROLLBACK语句开始工作和结束。开始和结束语句的SQL命令之间形成了大量的事务。

  in var_email varchar(30),

因为我们没有select procedure或者view
procedure的权限,所以我们几乎再无从干预了。

  2.ACID特性

  事务有以下四个标准属性的缩写ACID,通常被称为:

  - 原子性:
指事务是一个不可再分割的工作单元,事务中的操作要么都发生,要么都不发生。

  - 一致性:
在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

  - 隔离性:
多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。

  - 持久性:
在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。

  in var_orginfo varchar(30)

使用命令行的方式能够复现出这个问题:

  3.简单用例

-- MySQL默认出错自动回滚,没出错自动提交
BEGIN;
SAVEPOINT place;
INSERT INTO orders VALUES(20010, '2005-09-08 00:00:00', 10001);
ROLLBACK;-- ROLLBACK place
COMMIT

  在COMMIT提交之前,ROLLBACK语句来撤销事务中所做的每一项工作,即便工作是成功的也可以撤销,它可以一直撤销到事务的开始。在COMMIT提交之后,只能使用DELETE,INSERT或者UPDATE语句,ROLLBACK是不行的。

  )

威尼斯人娱乐官网 1

三、访问控制和用户权限管理

  内容摘自MySQL教程()

  BEGIN

没有存储过程的实质性内容。在那儿折腾了好一会,发现是个老问题了,10多年前的老问题了。

  1、访问控制

  当客户端连接到服务器时,MySQL访问控制有两个阶段:

  -
连接验证:连接到MySQL数据库服务器的客户端需要有一个有效的用户名和密码。此外,客户端连接的主机必须与MySQL授权表中的主机相匹配。

  -
请求验证:当连接成功建立后,对于客户端发出的每个语句,MySQL会检查客户端是否具有足够的权限来执行该特定语句。
MySQL能够检查数据库,表和字段级别的权限。

  MySQL安装程序自动创建一个名为mysql的数据库。
mysql数据库包含五个主要的授权表(user、db、host、tables_priv和columns_priv),它们存储了不同级别上对用户权限的设置。并可通过GRANT和REVOKE等语句间接操作这些表。

表名 权限
mysql.user  包含用户帐户和全局权限列。MySQL使用user表来接受或拒绝来自主机的连接。 在user表中授予的权限对MySQL服务器上的所有数据库都有效
mysql.db  包含数据库级权限。MySQL使用数据库表来确定用户可以访问哪个数据库以及哪个主机。在db表中的数据库级授予的特权适用于数据库,所有对象属于该数据库,例如表,触发器,视图,存储过程等
mysql.table_priv  包含表级别权限,权限适用于表中所有列
mysql.columns_priv  授予的权限只适用于字段
mysql.procs_priv  包含存储函数和存储过程的权限

  create temporary table errors (error varchar(500));

  2、用户权限管理

  MySQL8以上提供了role对象进行多用户权限管理。

/* GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]
*/

-- 创建单个用户账户
-- CREATE USER username@localhost IDENTIFIED BY passwd
CREATE USER dbadmin@localhost IDENTIFIED BY 'pwd123';
-- 查看该用户权限
SHOW GRANTS FOR dbadmin@localhost;
-- 赋予该用户操作权限
GRANT ALL PRIVILEGES ON test.customer TO dbadmin@localhost;-- 可以到mysql.table_priv授权表中查看设置
-- 更新
FLUSH PRIVILEGES;
SHOW GRANTS FOR dbadmin@localhost;
SELECT * FROM mysql.user;
-- 删除权限
REVOKE ALL PRIVILEGES ON test.customer FROM dbadmin@localhost;

  dbadmin@localhost用来指定账户名和主机地址。

  GRANT子句设置用户权限,包括对数据库级、表级、字段、查询语句等的设置。

权限 含义 全局 数据库 过程 代理
ALL [PRIVILEGES] 授予除了GRANT OPTION之外的指定访问级别的所有权限            
ALTER 允许用户使用ALTER TABLE语句 x x x      
ALTER ROUTINE 允许用户更改或删除存储程序 x x     x  
CREATE 允许用户创建数据库和表 x x x      
CREATE ROUTINE x x          
CREATE TABLESPACE 允许用户创建,更改或删除表空间和日志文件组 x          
CREATE TEMPORARY TABLES 允许用户使用CREATE TEMPORARY TABLE创建临时表 x x        
CREATE USER 允许用户使用CREATE USERDROP USERRENAME USERREVOKE ALL PRIVILEGES语句。 x          
CREATE VIEW 允许用户创建或修改视图 x x x      
DELETE 允许用户使用DELETE x x x      
DROP 允许用户删除数据库,表和视图 x x x      
EVENT 能够使用事件计划的事件 x x        
EXECUTE 允许用户执行存储过程/存储函数 x x        
FILE 允许用户读取数据库目录中的任何文件 x          
GRANT OPTION 允许用户有权授予或撤销其他帐户的权限 x x x   x x
INDEX 允许用户创建或删除索引 x x x      
INSERT 允许用户使用INSERT语句 x x x x    
LOCK TABLES 允许用户在具有SELECT权限的表上使用LOCK TABLES x x        
PROCESS 允许用户使用SHOW PROCESSLIST语句查看所有进程 x          
PROXY 启用用户代理            
REFERENCES 允许用户创建外键 x x x x    
RELOAD 允许用户使用FLUSH操作 x          
REPLICATION CLIENT 允许用户查询主服务器或从服务器的位置 x          
REPLICATION SLAVE 允许用户使用复制从站从主机读取二进制日志事件 x          
SELECT 允许用户使用SELECT语句 x x x x    
SHOW DATABASES 允许用户显示所有数据库 x          
SHOW VIEW 允许用户使用SHOW CREATE VIEW语句 x x x      
SHUTDOWN 允许用户使用mysqladmin shutdown命令 x          
SUPER 允许用户使用其他管理操作,如CHANGE MASTER TOKILLPURGE BINARY LOGSSET GLOBALmysqladmin命令 x          
TRIGGER 允许用户使用TRIGGER操作 x x x      
UPDATE 允许用户使用UPDATE语句 x x x x    
USAGE 相当于“无权限”          

  if exists ( select user from mysql.user where user=var_user) then

问题的解决其实很简单,就是需要这样一句:

  insert into errors values (concat(‘用户名 “‘,var_user,'”
已存在!’));

grant select on mysql.proc to xxxx@'xxxx'即可 

  end if;

所以细粒度的权限控制就是这么纠结,但是确实有效。

  if exists (select * from errors) then

比如我们举一反三一下,我们知道MySQL里的all
privileges算是一个很大的权限,但是里面包含多少种权限,可能我们没有清晰的概念。

  select error from errors;

我们就完全可以通过细粒度的权限控制来反推。

  else

比如创建一个用户,赋予all privileges的权限。

  set @user=concat(var_user,’@\”,var_ip,’\”);

mysql> grant all privileges on test.* to 'jeanron'@'%' identified by 'jeanron100'; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

  set @s=concat(‘create user ‘,@user,’ identified by ”12345”;’);

包含的权限如下:

  prepare cmd from @s;

mysql> show grants for jeanron; 
+---------------------------------------------------+ 
| Grants for jeanron@%        | 
+---------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'jeanron'@'%'    | 
| GRANT ALL PRIVILEGES ON `test`.* TO 'jeanron'@'%' | 
+---------------------------------------------------+ 
2 rows in set (0.00 sec) 

  execute cmd;

我们做一下收缩。

  set @s=concat(‘GRANT SELECT ON `mysql`.`func` TO ‘,@user,’;’);

mysql> revoke insert on test.* from jeanron@'%'; 
Query OK, 0 rows affected (0.00 sec) 
喏,all privileges的权限就现出原形了。
mysql> show grants for jeanron; 
| Grants for jeanron@% 
+------------------------------------------------------------------------------------------------------ 
| GRANT USAGE ON *.* TO 'jeanron'@'%' 
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'jeanron'@'%' | 

  prepare cmd from @s;

所以在上面的问题中,其实如果select on
*.*其实已经包含了我们需要的细粒度权限mysql.proc,如果要抽丝剥茧,基本就是这样的套路。

  execute cmd;

补充:下面看下mysql创建存储过程权限问题

  set @s=concat(‘GRANT SELECT ON `mysql`.`proc` TO ‘,@user,’;’);

首先,在mysql存储过程出现的同时,用户权限也增加了5种,其中和存储过程有关的权限有
三种:

  prepare cmd from @s;

  • ALTER ROUTINE 编辑或删除存储过程
  • CREATE ROUTINE 建立存储过程
  • EXECUTE 运行存储过程

  execute cmd;

在使用GRANT创建用户的时候分配这三种权限。
存储过程在运行的时候默认是使用建立者的权限运行的。

  replace into dev_test_userinfo values
(var_user,var_username,var_email,var_orginfo);

需要注意的是在一个用户拥有建立存储过程的权限时,如果其没有对于select、update或delete等权限的话,虽然操作数据的存储过程可以建立,但调用存储过程的话仍是无法成功的,会返回权限错误,就算拥有运行存储过程的权限也一样。所以,如果有人给你建立了一个没有select、update、delete权限只有CREATE
ROUTINE权限的用户,骂他吧,他是故意的。

  end if;

当然这样的用户建立的存储过程倒并不是完全不能使用,创建存储过程中有一个特征子句可以让存储过程使用运行者的权限,在建立存储过程后只要加上SQL
SECURITY INVOKER特征子句就可以了。

  drop temporary table errors;

如下。

  END

 CREATE PROCEDURE p() SQL SECURITY INVOKER 这样的话就可以分配两批人,一批给与创建存储过程的权限,作为开发者,一批给与运行存储过程和select、update、delete权限,作为测试者。(脑筋秀逗了)
有了这种权限分配,mysql的安全性完全不需要在功能层去保护了,我通过root用户建立的存储过程,但是在功能层用一个只拥有运行存储过程权限的用户来调用。那么,你就算从功能层上得到数据库的用户名和密码,并且模拟了ip,也不能得到你想要的任何东西。
有了权限,我们可以放心大胆的使用存储过程,不用担心安全问题了。

  $$

总结

  delimiter ;

以上所述是小编给大家介绍的MySQL存储过程的权限问题小结,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

  试着创建一个普通用户user_test1

您可能感兴趣的文章:

  • MySQL实现创建存储过程并循环添加记录的方法
  • Mybatis调用MySQL存储过程的简单实现
  • 浅谈MySQL存储过程中declare和set定义变量的区别

  mysql>create user user_test1 identified by ‘12345’;

  查看其权限

  mysql>show grants for user_test1;

  +———————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————–+

  | GRANT USAGE ON *.* TO ‘user_test1’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ |

  +———————————————————————————————————–+

  赋予其configdb上的select\insert\delete\update权限

  mysql>grant select,insert,delete,update on configdb.* to
‘user_test1’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’

  mysql> show grants for user_test1;

  +———————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————–+

  | GRANT USAGE ON *.* TO ‘user_test1’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ |

  | GRANT SELECT, INSERT, UPDATE, DELETE ON `configdb`.* TO
‘user_test1’@’%’ |

  +———————————————————————————————————–+

  使用此用户登录MySQL执行刚才定义的存储过程

  mysql>use configdb;

  mysql>call
sp_dev_test_user_add(‘uapp_yzz’,’172.16.%’,’yzz’,'[email protected]’,’MySQL
DBA’);

  ERROR 1370 (42000): execute command denied to user ‘user_test1’@’%’
for routine ‘configdb.sp_dev_test_user_add’

  看来是权限不足,继续赋予其configdb上的execute权限

  mysql> grant execute on configdb.* to ‘user_test1’@’%’
IDENTIFIED BY PASSWORD ‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’;

  mysql> show grants for user_test1;

  +———————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————–+

  | GRANT USAGE ON *.* TO ‘user_test1’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ |

  | GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `configdb`.*
TO ‘user_test1’@’%’ |

  +———————————————————————————————————–+

  重新使用此用户登录MySQL执行刚才定义的存储过程

  mysql>use configdb;

  mysql>call
sp_dev_test_user_add(‘uapp_yzz’,’172.16.%’,’yzz’,'[email protected]’,’MySQL
DBA’);

  ERROR 1449 (HY000): The user specified as a definer
(‘user_admin’@’%’) does not exist

  这次可以调用该存储过程了,但是提示存储过程定义中的definer不存在,原来仅仅是连接到MySQL服务器的用户具有执行存储过程的权限是远远不够的,最终要通过存储过程定义中指定的definer来执行存储过程。

  创建user_admin’@’%’这个用户,并赋予configdb上相应的权限

  mysql>create user user_admin identified by ‘12345’;

  mysql> grant select,insert,delete,update on configdb.* to
‘user_admin’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’;

  mysql> show grants for user_admin;

  +———————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————–+

  | GRANT USAGE ON *.* TO ‘user_admin’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ |

  | GRANT SELECT, INSERT, UPDATE, DELETE ON `configdb`.* TO
‘user_admin’@’%’ |

  +———————————————————————————————————–+

  重新使用’user_test1’@’%’用户登录MySQL执行刚才定义的存储过程

  mysql>use configdb;

  mysql> call
sp_dev_test_user_add(‘uapp_yzz’,’172.16.%’,’yzz’,'[email protected]’,’MySQL
DBA’);

  ERROR 1370 (42000): execute command denied to user ‘user_admin’@’%’
for routine ‘configdb.sp_dev_test_user_add’

  看来不仅仅是连接到MySQL服务器的用户需要具有存储过程上的执行权限,存储过程定义者同样需要该权限。

  mysql> grant execute on configdb.* to ‘user_admin’@’%’
IDENTIFIED BY PASSWORD ‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’;

  mysql> show grants for user_admin;

  +———————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————–+

  | GRANT USAGE ON *.* TO ‘user_admin’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ |

  | GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `configdb`.*
TO ‘user_admin’@’%’ |

  +———————————————————————————————————–+

  重新使用’user_test1’@’%’用户登录MySQL执行刚才定义的存储过程

  mysql>use configdb;

  mysql> call
sp_dev_test_user_add(‘uapp_yzz’,’172.16.%’,’yzz’,'[email protected]’,’MySQL
DBA’);

  ERROR 1044 (42000): Access denied for user ‘user_admin’@’%’ to
database ‘configdb’

  可以执行存储过程了,但是提示权限不足,仔细查看存储过程的定义可以看到,存储过程中包含创建用户和赋予权限的语句,而我们赋给’user_test1’@’%’用户和’user_admin’@’%’都不具有这样的权限。

  赋予’user_test1’@’%’创建用户的权限和赋权的权限,以及创建临时表的权限

  mysql> grant create user on *.* to ‘user_test1’@’%’ IDENTIFIED
BY PASSWORD ‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ with grant
option;

  mysql> grant create temporary tables on configdb.* to
‘user_test1’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’;

  mysql> show grants for ‘user_test1’@’%’;

  +———————————————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————————————–+

  | GRANT CREATE USER ON *.* TO ‘user_test1’@’%’ IDENTIFIED BY
PASSWORD ‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ WITH GRANT OPTION
|

  | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES,
EXECUTE ON `configdb`.* TO ‘user_test1’@’%’ |

  +———————————————————————————————————————————–+

  重新使用’user_test1’@’%’用户登录MySQL执行刚才定义的存储过程

  mysql>use configdb;

  mysql> call
sp_dev_test_user_add(‘uapp_yzz’,’172.16.%’,’yzz’,'[email protected]’,’MySQL
DBA’);

  ERROR 1044 (42000): Access denied for user ‘user_admin’@’%’ to
database ‘configdb’

  对了,不管你是以什么账户登录的MySQL,最后是使用存储过程的definer执行存储过程的,所以应当把创建用户和赋权的权限付给definer,这里为user_admin’@’%’这个账户。

  赋予’user_admin’@’%’创建用户的权限和赋权的权限

  mysql> grant create user on *.* to ‘user_admin’@’%’ IDENTIFIED
BY PASSWORD ‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ with grant
option;

  mysql> grant create temporary tables on configdb.* to
‘user_admin’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’;

  mysql> show grants for ‘user_admin’@’%’;

  +———————————————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————————————–+

  | GRANT CREATE USER ON *.* TO ‘user_admin’@’%’ IDENTIFIED BY
PASSWORD ‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ WITH GRANT OPTION
|

  | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES,
EXECUTE ON `configdb`.* TO ‘user_admin’@’%’ |

  +———————————————————————————————————————————–+

  重新使用’user_test1’@’%’用户登录MySQL执行刚才定义的存储过程

  mysql>use configdb;

  mysql> call
sp_dev_test_user_add(‘uapp_yzz’,’172.16.%’,’yzz’,'[email protected]’,’MySQL
DBA’);

  ERROR 1142 (42000): SELECT command denied to user ‘user_admin’@’%’
for table ‘user’

  哦,除了configdb库外还得有mysql库上user表的权限,给加上,看来权限问题还真是棘手,呵呵~

  mysql> grant select,insert,delete,update on mysql.* to
‘user_admin’@’%’ IDENTIFIED BY PASSWORD
‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’;

  mysql> show grants for ‘user_admin’@’%’;

  +———————————————————————————————————————————–+

  | Grants for
[email protected]%
|

  +———————————————————————————————————————————–+

  | GRANT CREATE USER ON *.* TO ‘user_admin’@’%’ IDENTIFIED BY
PASSWORD ‘*00A51F3F48415C7D4E8908980D443C29C69B60C9’ WITH GRANT OPTION
|

  | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY
TABLES, EXECUTE ON `configdb`.* TO ‘user_admin’@’%’ |

  | GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO
‘user_admin’@’%’ |

  +———————————————————————————————————————————–+

  重新使用’user_test1’@’%’用户登录MySQL执行刚才定义的存储过程

  mysql>use configdb;

  mysql> call
sp_dev_test_user_add(‘uapp_yzz’,’172.16.%’,’yzz’,'[email protected]’,’MySQL
DBA’);

  Query OK, 0 rows affected (0.05 sec)

  终于OK了,相信通过这一系列过程,大家应该能够很清楚的了解MySQL存储过程相关的执行权限了。另外,定义该存储过程还需要有CREATE
ROUTINE的权限、更该存储过程需要有ALTER
ROUTINE的权限(这里是用超级用户在configdb创建的存储过程,上述权限都是具备的),调用存储过程的用户需要有EXECUTE权限,最终执行存储过程的用户也即存储过程定义者要具备存储过程定义语句中相关的各种权限。

  MySQL的权限分的比较细,大致可分为表权限、列权限、过程权限具体可参考MySQL官方手册。

比如我们在名为configdb的数据库下创建了如下存储过程,存储过程的定义者为user_admin
use configdb; d…

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图