mysql权限管理

淡淡的烟草味﹌ 2022-01-17 10:09 188阅读 0赞

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

插入试验数据  
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][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用户及赋予用户权限 *****

我们具体的操作如下：  
首先，我们查看用户

没有我们想要的majihui用户

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 -h192.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)> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

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

再执行这个就可以链接了  
\[root@hadoop01-181 ~\]\# mysql -umajihui -pmajihui123 -h192.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)

@(none)> delete from mysql.user where user='majihui';  
Query OK, 1 row affected (0.01 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 -h192.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'

成功了//

转载于:https://blog.51cto.com/12445535/2397256

[http_dev.mysql.com_doc_refman_5.1_en_grant.html]: http://dev.mysql.com/doc/refman/5.1/en/grant.html