文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql权限管理

2024-04-02 19:55

关注

2019/5/20 星期一 //这是mysql的权限问题 具体的详情见 第三章 《mysql用户及赋予用户权限grant》
现在有一个需求,就是指定一个用户对一个数据库中的所有表只有只读权限
mysql用户及赋予用户权限grant
我们在我们的dev环境的 192.168.0.36 上进行试验

@(none)> select user,host from mysql.user; 
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
首先创建一个 试验的表
create database university; //create database university character set utf8mb4;
use university;
创建一个学生表
create table student(
Sno char(9) PRIMARY KEY,
Sname char(20) unique,
Ssex char(2),
Sage int,
Sdept char(20)
);

插入试验数据
insert into student values(200215121,'李勇','男',20,'CS');
insert into student values(100215122,'刘晨','女',19,'CS');
insert into student values(100215123,'王敏','女',18,'MA');
insert into student values(100215124,'张立','男',19,'IS');

@university> use university;
Database changed
@university> show tables;
+----------------------+
| Tables_in_university |
+----------------------+
| student              |
+----------------------+
1 row in set (0.00 sec)

@university> select * from student;
+-----------+--------+------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 100215122 | 刘晨   | 女   |   19 | CS    |
| 100215123 | 王敏   | 女   |   18 | MA    |
| 100215124 | 张立   | 男   |   19 | IS    |
| 200215121 | 李勇   | 男   |   20 | CS    |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)

接下来,我们要创建一个用户majihui 只对university 库下的所有表有权限。
mysql> help grant;
Name: 'GRANT'
Description:
Syntax:
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 ...]

object_type:
TABLE
| FUNCTION
| PROCEDURE

priv_level:

user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']

ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count 每小时查询几次
| MAX_UPDATES_PER_HOUR count 每小时更新几次
| MAX_CONNECTIONS_PER_HOUR count 每小时链接几次
| MAX_USER_CONNECTIONS count 每小时使用几次

The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.

Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1. TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON
.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

However, if an account named in a GRANT statement does not already
exist, GRANT may create it under the conditions described later in the
discussion of the NO_AUTO_CREATE_USER SQL mode.

The REVOKE statement is related to GRANT and enables administrators to
remove account privileges. See [HELP REVOKE].

When successfully executed from the mysql program, GRANT responds with
Query OK, 0 rows affected. To determine what privileges result from the
operation, use SHOW GRANTS. See [HELP SHOW GRANTS].

URL: http://dev.mysql.com/doc/refman/5.1/en/grant.html

首先方法1 
方法1:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
mysql> create user oldboy@'localhost' identified by 'oldboy123';  创建一个用户名为oldboy密码为oldboy123的用户
Query OK, 0 rows affected (0.00 sec)
//此部分在dba第三 章有详细解释,请转至第三章 创建mysql用户及赋予用户权限 *****

我们具体的操作如下:
首先,我们查看用户

@university> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

没有我们想要的majihui用户

我们接下来在192.168.83.181 这台服务器上用root用户去连0.36上的mysql 看能否链接创建数据
[root@hadoop01-181 ~]# mysql -uroot -pxxxxx -h292.168.0.36 -P3306
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| freeswitch |
| gpush |
| ivargo_pay |
| mysql |
| openfire |
| performance_schema |
| sys |

23 rows in set (0.01 sec)
我们可以查看
我们接下来试着插入一个数据

insert into student values(20090001,'马吉辉','男',26,'CS');
mysql> insert into student values(20090001,'马吉辉','男',26,'CS');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 100215122 | 刘晨      | 女   |   19 | CS    |
| 100215123 | 王敏      | 女   |   18 | MA    |
| 100215124 | 张立      | 男   |   19 | IS    |
| 200215121 | 李勇      | 男   |   20 | CS    |
| 20090001  | 马吉辉    | 男   |   26 | CS    |
+-----------+-----------+------+------+-------+
5 rows in set (0.00 sec)

接下来,我们的实际操作
接下来,我们要创建一个用户majihui 只对university 库下的所有表有权限。
方法1:
create user majihui@'localhost' identified by 'majihui123'; 创建一个用户名为majihui密码为majihui123的用户
grant all on university.* to majihui@'localhost'; (把majihui库中的所有表的权限 给majihui用户)


方法2:一条命令搞定
mysql> grant all on oldboy. to oldboy@'localhost' identified by 'oldboy123';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
提示:相当于
useradd oldboy && echo "oldboy123"|password -stdin oldboy
列表说明如下
grant all on oldboy.
to oldboy@'localhost' identified by 'oldboy123';
授权命令 对于权限 目标:库和表 用户名和主机 用户密码


//我们的实际操作如下: //我们之前用的是majihui@'localhost' 要改成 majihui@'%'
@(none)> create user majihui@'%' identified by 'majihui123';
Query OK, 0 rows affected (0.01 sec)
@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| majihui | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)
@(none)> grant all on university. to majihui@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)
@(none)> show grants for majihui@'%';
+---------------------------------------------------------+
| Grants for majihui@% |
+---------------------------------------------------------+
| GRANT USAGE ON
. TO 'majihui'@'%' |
| GRANT ALL PRIVILEGES ON university.
TO 'majihui'@'%' |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

[root@hadoop01-181 ~]# mysql -umajihui -pmajihui123 -h292.168.0.36 -P3306
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'majihui'@'192.168.83.181' (using password: YES)
[root@hadoop01-181 ~]# mysql -umajihui -pmajihui123 -hlocalhost -P3306
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'majihui'@'localhost' (using password: YES)

我们要把localhost改成 %
UPDATE mysql.user SET host='%' WHERE user='majihui';
@(none)> UPDATE mysql.user SET host='%' WHERE user='majihui';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| majihui | % |
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

@(none)> flush privileges;
Query OK, 0 rows affected (0.00 sec)

grant all on university.* to majihui@'%';

再执行这个就可以链接了
[root@hadoop01-181 ~]# mysql -umajihui -pmajihui123 -h292.168.0.36 -P3306
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| university |
+--------------------+
2 rows in set (0.00 sec)

mysql> delete from student where Sname='马吉辉';
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 100215122 | 刘晨 | 女 | 19 | CS |
| 100215123 | 王敏 | 女 | 18 | MA |
| 100215124 | 张立 | 男 | 19 | IS |
| 200215121 | 李勇 | 男 | 20 | CS |
+-----------+--------+------+------+-------+
4 rows in set (0.00 sec)

//成功了,majihui这个用户对university.*下的所有库有增删改查的权限

接下来收回权限
REVOKE INSERT ON university. FROM 'majihui'@'%'; 回收权限
@(none)> REVOKE INSERT ON university.
FROM 'majihui'@'%';
Query OK, 0 rows affected (0.00 sec)

删除majihui这个用户
@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| majihui | % |
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

@(none)> delete from mysql.user where user='majihui';
Query OK, 1 row affected (0.01 sec)

@(none)> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)


创建vargo这个用户,单独的对university.下所有表 只有可读的权限。也就是只有select的权限
具体操作如下
create user vargo@'%' identified by 'vargo123';
grant select on university.
to vargo@'%';
@(none)> grant select on university.* to vargo@'%';
Query OK, 0 rows affected (0.01 sec)

@(none)> show grants for vargo@'%'
-> ;
+-----------------------------------------------+
| Grants for vargo@% |
+-----------------------------------------------+
| GRANT USAGE ON . TO 'vargo'@'%' |
| GRANT SELECT ON university.* TO 'vargo'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

然后我们登录
[root@hadoop01-181 ~]# mysql -uvargo -pvargo123 -h292.168.0.36 -P3306
mysql> select * from student;
+-----------+--------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 100215122 | 刘晨 | 女 | 19 | CS |
| 100215123 | 王敏 | 女 | 18 | MA |
| 100215124 | 张立 | 男 | 19 | IS |
+-----------+--------+------+------+-------+
3 rows in set (0.00 sec)

mysql> delete from student where Sname='张立';
ERROR 1142 (42000): DELETE command denied to user 'vargo'@'192.168.83.181' for table 'student'
mysql> insert into student values(200215121,'李勇','男',20,'CS');
ERROR 1142 (42000): INSERT command denied to user 'vargo'@'192.168.83.181' for table 'student'

成功了//

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯