Shulou(Shulou.com)06/01 Report--

Monday, 2019-5-20 / / for more information on the permissions of mysql, see Chapter 3, "mysql users and entitling users to grant".

Now there is a requirement to specify that a user has read-only access to all tables in a database.

Mysql user and user privilege grant

We tested it on 192.168.0.36 of our dev environment.

@ (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) first create an experimental table create database university / / create database university character set utf8mb4;use university; create a student table create table student (Sno char (9) PRIMARY KEY,Sname char (20) unique,Ssex char (2), Sage int,Sdept char (20))

Insert test data

Insert into student values (200215121,'Li Yong', 'male', 20 'female CS')

Insert into student values (100215122, 'Liu Chen', 'female', 19recorder CS')

Insert into student values (100215123 'Wang Min', 'female', 18 'recordma')

Insert into student values (100215124 'Zhang Li', 'male', 19 'recorder 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 | Liu Chen | female | 19 | CS | | 100215123 | Wang Min | | female | 18 | MA | | 100215124 | Zhang Li | male | 19 | IS | | 200215121 | Li Yong | male | 20 | CS | +-+ 4 rows in set (0.00 sec) |

Next, we will create a user majihui that only has permissions on all tables under the university library.

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:

All

|. All libraries, all tables.

| | db_name.* specifies all tables of the library |

| | db_name.tbl_name specifies the pointing table of the library |

| | tbl_name specifies a table |

| | db_name.routine_name specifies the stored procedure of a library |

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 queries several times per hour |

| | MAX_UPDATES_PER_HOUR count is updated several times per hour |

| | MAX_CONNECTIONS_PER_HOUR count links several times per hour |

| | MAX_USER_CONNECTIONS count is used several times per hour |

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

First of all, method 1 method 1:CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY' mypass';GRANT ALL ON db1.* TO 'jeffrey'@'localhost';mysql > create user oldboy@'localhost' identified by' oldboy123'; to create a user Query OK whose username is oldboy password is oldboy123. 0 rows affected (0.00 sec) / / this section is explained in detail in Chapter 3 of dba. Please go to Chapter 3 to create mysql users and grant user rights *

Our specific operations are as follows:

First, let's look at the users.

@ 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)

There are no majihui users we want.

Next, we use root users to connect to the mysql on 0.36 on the server 192.168.83.181 to see if we can link to create data.

[root@hadoop01-181a] # 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)

We can check

Let's try to insert a data next.

Insert into student values (20090001'Ma Jihui', 'male', 26 'recorder CS'); mysql > insert into student values (20090001' Ma Jihui', 'male', 26 'recorder CS'); Query OK, 1 row affected (0.01sec) mysql > select * from student +-+ | Sno | Sname | Ssex | Sage | Sdept | +-+ | 100215122 | Liu Chen | female | 19 | CS | | 100215123 | Wang Min | female | 18 | MA | | 100215124 | Zhang Li | male | 19 | IS | | 200215121 | Li Yong | male | 20 | CS | | 20090001 | Ma Jihui | male | 26 | CS | +-+ 5 rows in set (0.00 sec)

Next, our actual operation

Next, we will create a user majihui that only has permissions on all tables under the university library.

Method 1:

Create user majihui@'localhost' identified by 'majihui123'; creates a user whose username is majihui and password is majihui123

Grant all on university.* to majihui@'localhost'; (give permissions to majihui users for all tables in the majihui library)

Method 2: get it done with one command

Mysql > grant all on oldboy. To oldboy@'localhost' identified by 'oldboy123'

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Tip: equivalent to

Useradd oldboy & & echo "oldboy123" | password-stdin oldboy

The list is as follows

Grant all on oldboy. To oldboy@'localhost' identified by 'oldboy123'

Authorization commands for permission targets: library and table usernames and host user passwords

/ / our actual operation is as follows: / / We used majihui@'localhost' to change it to 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-181a] # 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-181a] # 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)

We need to change localhost to%.

UPDATE mysql.user SET host='%' WHERE user='majihui'

@ (none) > UPDATE mysql.user SET host='%' WHERE user='majihui'

Query OK, 1 row affected (0.01sec)

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@'%'

Execute this again and you can link.

[root@hadoop01-181a] # 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=' Ma Jihui'

Query OK, 1 row affected (0.00 sec)

Mysql > select * from student

+-+

| | Sno | Sname | Ssex | Sage | Sdept | |

+-+

| | 100215122 | Liu Chen | female | 19 | CS |

| | 100215123 | Wang Min | female | 18 | MA |

| | 100215124 | Zhang Li | male | 19 | IS |

| | 200215121 | Li Yong | male | 20 | CS |

+-+

4 rows in set (0.00 sec)

/ / successful. Majihui has the permission to add, delete, modify and query all the libraries under university.*.

Next, take back the authority.

REVOKE INSERT ON university. FROM 'majihui'@'%'; Recycling permissions

@ (none) > REVOKE INSERT ON university. FROM 'majihui'@'%'

Query OK, 0 rows affected (0.00 sec)

Delete majihui as a user

@ (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.01sec)

@ (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 vargo as a user, independently to university. All tables below have only readable permissions. That is, only select has permission.

The specific operations are as follows

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)

Then we log in.

[root@hadoop01-181a] # mysql-uvargo-pvargo123-h292.168.0.36-P3306

Mysql > select * from student

+-+

| | Sno | Sname | Ssex | Sage | Sdept | |

+-+

| | 100215122 | Liu Chen | female | 19 | CS |

| | 100215123 | Wang Min | female | 18 | MA |

| | 100215124 | Zhang Li | male | 19 | IS |

+-+

3 rows in set (0.00 sec)

Mysql > delete from student where Sname=' Zhang Li'

ERROR 1142 (42000): DELETE command denied to user 'vargo'@'192.168.83.181' for table' student'

Mysql > insert into student values (200215121,'Li Yong', 'male', 20 minutes' CS')

ERROR 1142 (42000): INSERT command denied to user 'vargo'@'192.168.83.181' for table' student'

It succeeded / /

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.