MySQL存储过程及用户权限管理

  1. 云栖社区>
  2. 博客列表>
  3. 正文

MySQL存储过程及用户权限管理

sr_zhang 2018-10-12 09:27:11 浏览69 评论0

摘要: MySQL存储过程及函数: 创建存储过程:delimiter //create procedure 存储过程名称--begin--sql语句1--语句2.....--end//delimiter ;其中:参数为: [IN|OUT|INOUT] parameter_name type 其中IN表示输...

MySQL存储过程及函数:

创建存储过程:
delimiter //
create procedure 存储过程名称
--begin
--sql语句1
--语句2
.....
--end//
delimiter ;
其中:参数为: [IN|OUT|INOUT] parameter_name type
其中IN表示输入参数,
OUT表示输出参数,
INOUT表示既可以输入也可以输出;
param_name表示参数名称;type表示参数的类型

具体应用:创建无参数存储过程
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure dropdatabase()

-> begin
-> drop database testdb;
-> end//

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;

具体应用2:有参数存储过程
MariaDB [testdb]> create procedure text(in name varchar(20))

-> begin
-> insert into testdb.students set name=name;
-> end//

Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]> delimiter ;
MariaDB [testdb]> call text('jim');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> select * from testdb.students;
name
name
jim

2 rows in set (0.00 sec)

查看存储过程的定义:
show create procedure 存储过程名;

调用存储过程:call [存储过程名称];

查看存储过程列表:
show procedure status;

删除存储过程:
drop procedure [存储过程名称];

注意事项:
在哪个库中创建的存储过程就在哪个库中使用,如果
跨库调用需要使用[存储过程所在库].[存储过程名],
例如hellodb.test这种方式,删除时,同样要这样删除
MySQL触发器:

创建触发器:
创建触发器
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:该触发器作用在表名

具体应用:
模拟场景:转账过程!
两张表分别记录jim和tom原有的钱
jim最开始有1万,tom有2千,现在模拟jim给tom转账1
千元.....
MariaDB [textdb]> create trigger text

-> after update
-> on user1 for each row
-> update user2 set money = money+1000;

Query OK, 0 rows affected (0.30 sec)

MariaDB [textdb]> update user1 set money = 9000;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [textdb]> select * from user1;
name money
jim 9000

1 row in set (0.00 sec)

MariaDB [textdb]> select * from user2;
name money
tom 3000

1 row in set (0.00 sec)

注意事项:
before:表示在这个事件之前触发,也就是说,如果
用户执行一个insert动作,在用户还没有执行insert之
前就会被触发器中设定的动作所替换!

查看触发器:
show triggers;
删除触发器:
drop trigger [触发器名称];
MySQL用户及权限管理:

1 创建用户:仅能创建一个受限制的用户,并支持赋予密码!
create user username@hostname identified by '密码'
具体用法:
MariaDB [textdb]> create user 'yl'@'172.20.%.%' identified by '密码';

2 修改用户名:对以登录的用户不起作用
rename user
用法格式:rename user [旧名称] to [新名称];
具体用法:
mysql> rename user yl@'172.20.%.%' to gw@'172.20.%.%';

3 修改用户密码:
使用格式:
set password for username = password('密码');
具体用法:
set password for root = password('密码');

4 删除用户:
DROP USER username@hostIP --删除用户
在线连接的时候也可以删除用户!
具体用法:
MariaDB [mysql]> drop user gw@'172.20.%.%';

忘记管理员密码后的解决办法:
启动mysqld进程时,为其使用如下选项:
--skip-grant-tables --skip-networking[不允许网络连接]
使用UPDATE命令修改管理员密码
关闭mysqld进程,移除上述两个选项,重启mysqld

编辑/etc/my.cnf
[mysqld]
skip-grant-tables

重启服务后登录mysql直接更改管理员密码即可!
MySQL权限分类:

库级别权限/表级别:
alter ----修改表
alter routing ---修改存储过程和函数
create ---是否可以创建表和库
create routing ---是否可以创建存储过程和函数
create view --是否可以创建视图
drop ---是否可以删除表和库
execute ---是否可以执行存储过程和函数
grant option ---是否可以把自己获取的权限转给他人
index ----是否可以创建和删除索引
show view --是否可以查看视图的创建过程

数据操作权限(表级别):
delete ---是否可以删除表中的行
insert ---是否可以插入数据
select ---是否可以查询数据
update ---是否可以修改数据

字段级别权限
select(字段1....)
ipdate(字段1....)
insert(字段1....)

管理类权限
create temporary tables ---创建临时表权限
create user ---能否创建用户
file ---在mysql服务器上读取和写入文件
lock tables ---可以手动显式施加表锁
process ---查看线程列表
具体命令:
show processlist
reload ----可以使用flush和resrt
主从复制:
replication client ---向服务器查询所有复制客户端
replication slave ---授权客户端有复制权限
show databases ---是否可以查看表
shutdown ---是否可以关闭服务器
super ----杂项命令

所有权限:all

权限的授予和收回:

grant [权限] on 修饰符,默认是表 to [用户]@[主机IP] identified by '密码',{多个用户之间逗号隔开} [require ssl];
object_type:修饰符

TABLE  ---默认是表

| FUNCTION ---存储函数
| PROCEDURE ---存储过程

priv_level:

*  ---所有库,表,函数,过程

| . ---所有库的所有对象
| db_name.* ---某一个库的所有对象
| db_name.tbl_name ---某一个库的一个表
| tbl_name ---某张表
| db_name.routine_name ---指定库的存储过程

with_option:

GRANT OPTION ----把自己的权限授权给它人

| MAX_QUERIES_PER_HOUR count ---每小时允许最大执行查询的次数
| MAX_UPDATES_PER_HOUR count ---每小时允许最大执行修改的次数
| MAX_CONNECTIONS_PER_HOUR count ---每小时允许最大建立连接的次数
| MAX_USER_CONNECTIONS count ---指定使用同一个账号,最多并发连接几次!

grant命令使用时,如果没有该用户,就会自动创建!

具体用法:
grant all on testdb.* to 'yl'@'172.20.%.%' identified by '密码';

回收权利:revoke
具体用法:
revoke insert on testdb.* from 'yl'@'172.20.%.%';
对正在登录的用户不起作用,用户退出再次登录后生效

收回全部权利:
revoke all on [某库或某表] from [用户@主机IP];

查看用户能够使用的权限:
show grants for username@'hostname';
具体用法:
MariaDB [testdb]> show grants for yl@'172.20.%.%';

查看当前用户权限:
show grants for current_user();

几个跟用户授权相关的表:
db:库级别权限
host:主机级别权限,已废弃
tables_priv:表级别权限
colomns_priv:列级别的权限
procs_priv:存储过程和存储函数相关的权限
proxies_priv:代理用户权限

用云栖社区APP,舒服~

【云栖快讯】诚邀你用自己的技术能力来用心回答每一个问题,通过回答传承技术知识、经验、心得,问答专家期待你加入!  详情请点击

网友评论

sr_zhang
文章17篇 | 关注0
关注
MySQL 是全球最受欢迎的开源数据库,阿里云MySQL版 通过深度的内核优化和独享实例提供... 查看详情
一个稳定可靠的集中式访问控制服务。您可以通过访问控制将阿里云资源的访问及管理权限分配给您的企... 查看详情
支持 PB 级数据存储的海量分布式关系型数据库。它支持 MySQL 数据库接口,采用可扩展的... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航