背景：没有划分数据库权限，所有人共用一个账号
本人公司现有的数据库账号分布情况：
所有人用一个账号（包括程序里面访问数据库的的配置文件里面的账号），该账号除删库权限，其他权限大部分都有。
这样非数据库管理员也可修改数据库，非常不安全。所以想划分权限。
思路：创建3个账号，不同的人用不同账号
一个管理员账号（DBA用，拥有最高权限）
一个账号程序上用（只有增删查改以及调用存储过程函数等权限）
一个账号给程序员用（只有查看权限）
环境：基于阿里云上的RDS实例 (MYSQL)
上面提到的账号是没有创建用户以及删库等权限的，如果需要创建用户等，则需要“创建高权限账号”
步骤：创建“高级权限账号”、程序上访问数据库的账号、只读账号
创建高级权限账号zhangm（相当于管理员账号，但是权限也还是有限制）
登录该“高权限账号”zhangm，创建只读权限用户user1
mysql> create user user1 IDENTIFIED by '123456'; Query OK, 0 rows affected (0.00 sec)
登录用户user1账号
[root@zhangmeng ~]# mysql -u user1 -p 123456
查看数据库，发现只能看到information_schema库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.01 sec)
并且登录这个库，不能看到其他库的对象信息，只能看到该库的对象信息。
mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select * from information_schema.`TABLES`where TABLE_SCHEMA='mofiman'; Empty set (0.01 sec) mysql> mysql> select * from information_schema.`TABLES`where TABLE_SCHEMA='mysql'; Empty set (0.01 sec) mysql> mysql> select distinct TABLE_SCHEMA from information_schema.`TABLES`; +--------------------+ | TABLE_SCHEMA | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.01 sec)
如果我们想给用户user1修改密码，该“高权限用户”没有直接修改的权限，只能通过删除用户，再新建用户
mysql> set password for user1 =password('abc123'); ERROR 1044 (42000): Access denied for user 'zhangm1'@'%' to database 'mysql' mysql> mysql> drop user user1; Query OK, 0 rows affected (0.00 sec)
mysql> mysql> create user user1 IDENTIFIED by 'abc123'; Query OK, 0 rows affected (0.00 sec)
查看user1用户的权限，我们没有给该用户分配任何权限，可以看到，该用户默认拥有USAGE权限（登录数据库权限）
mysql> mysql> show grants for user1 G; *************************** 1. row *************************** Grants for user1@%: GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*6691484EA6B50DDDE1926A220DA01FA9E575C18A' 1 row in set (0.01 sec)
我准备给user1用户授予查询权限，首先创建一个测试库test，创建一个临时表叫tmp_1，插入几条测试数据
mysql> use test; Database changed mysql> create table tmp_1(id int,name varchar(20)); Query OK, 0 rows affected (0.01 sec)
mysql> insert into tmp_1 values(1,'zm'); Query OK, 1 row affected (0.01 sec) mysql> insert into tmp_1 values(2,'lly'); Query OK, 1 row affected (0.01 sec) mysql> insert into tmp_1 values(3,'jzj'); Query OK, 1 row affected (0.01 sec) mysql> insert into tmp_1 values(4,'fw'); Query OK, 1 row affected (0.00 sec)
mysql> select * from tmp_1; +------+------+ | id | name | +------+------+ | 1 | zm | | 2 | lly | | 3 | jzj | | 4 | fw | +------+------+ 4 rows in set (0.01 sec)
我们登录user1用户访问数据库test的tmp_1表（以下ip已做处理），提示没有权限
mysql> select * from test.tmp_1; ERROR 1142 (42000): SELECT command denied to user 'user1'@'192.168.1.1' for table 'tmp_1'
登录“高权限账号”zhangm给user1用户授权查询数据库test所有表的权限（若想精确只授予某个表可以把数据库后面的*改成指定表名）
mysql> grant select on test.* to user1; Query OK, 0 rows affected (0.01 sec)
登录user1账号，访问test数据库，发现可以看到test库，并且可以访问表tmp_1，但是如果执行删除操作，会提示没有权限
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.01 sec) mysql> select * from test.tmp_1; +------+------+ | id | name | +------+------+ | 1 | zm | | 2 | lly | | 3 | jzj | | 4 | fw | +------+------+ 4 rows in set (0.00 sec) mysql> delete from test.tmp_1 where id=1; ERROR 1142 (42000): DELETE command denied to user 'user1'@'192.168.1.1' for table 'tmp_1' mysql>
如果想撤回该权限用 revoke，至此，我们已完成只读用户user1的创建。
至此，只读用户user1的权限分配完成！
mysql> revoke select on test.* from user1; Query OK, 0 rows affected (0.01 sec)
接下来我们创建程序上用的账号user2（增删查改权限，如有必要添加执行函数和存储过程的权限）
mysql> grant select,update,delete,insert on test.* to user2; Query OK, 0 rows affected (0.01 sec)
登录账号user2可以查询数据库test的表tmp_1，并且可以插入数据
mysql> select * from test.tmp_1; +------+------+ | id | name | +------+------+ | 1 | zm | | 2 | lly | | 3 | jzj | | 4 | fw | +------+------+ 4 rows in set (0.00 sec) mysql> mysql> insert into test.tmp_1 values(5,'yzw'); Query OK, 1 row affected (0.01 sec)
以上相同部分不在撰述，主要讲一下执行存储过程和函数的权限，所以我们需要分别创建一个简单的存储过程和函数来做测试
在登录“高权限账号”zhangm数据库test创建一个简单的存储过程，发现declare names varchar(20);后面报错，原因是后面带了分号，mysql以为这个语句结束了
mysql> drop PROCEDURE if EXISTS sp_tmp1; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> CREATE PROCEDURE sp_tmp1() -> BEGIN -> declare names varchar(20); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 mysql> set @names=''; Query OK, 0 rows affected (0.01 sec)
正确的创建方法是用分隔符 DELIMITER //..... //，
mysql> DELIMITER // mysql> drop PROCEDURE if EXISTS sp_tmp1; -> CREATE PROCEDURE sp_tmp1() -> BEGIN -> declare names varchar(20); -> set @names='zm'; -> select @names; -> END -> // Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec)
函数的创建也是一样的
mysql> DELIMITER // mysql> drop FUNCTION if EXISTS fc_tmp1; -> CREATE FUNCTION fc_tmp1 (num INT ) -> RETURNS VARCHAR(20) -> BEGIN -> RETURN (SELECT name -> FROM tmp_1 -> WHERE id=num ); -> END -> // Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec)
登录user2的账号，调用存储过程和函数，提示没有权限
mysql> call test.sp_tmp1(); ERROR 1370 (42000): execute command denied to user 'user2'@'%' for routine 'test.sp_tmp1' mysql> mysql> select test.fc_tmp1(1); ERROR 1370 (42000): execute command denied to user 'user2'@'%' for routine 'test.fc_tmp1' mysql>
授予user2用户调用存储过程和函数的权限
mysql> grant execute on test.* to user2; Query OK, 0 rows affected (0.00 sec)
登录user2账号，执行存储过程和函数
至此，程序上访问数据库的账号user2的权限分配完成！
mysql> call test.sp_tmp1(); +--------+ | @names | +--------+ | zm | +--------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql> select test.fc_tmp1(1); +-----------------+ | test.fc_tmp1(1) | +-----------------+ | zm | +-----------------+ 1 row in set (0.01 sec)
说明：本文为本人原创，部分内容可能参考网上的资料，转载请注明出处，可能有写的不够准确的地方，欢迎指正。