Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the authorization method of each level of mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the authorization method at each level of mysql"?

The tables for recording permissions are user, db, tables_priv, columns_priv

The user table records the permissions of all databases

The db table records permissions on a database

The tables_priv table records the permissions on the table.

The column_priv table records permissions on a column of a table, which must be enclosed in () parentheses

Mysql does not have connect create session permissions like oracle. Mysql automatically has connect create session permissions as long as it creates a user.

Authorization executed by grant does not require flush privileges to take effect. Authorization executed by update mysql.user table requires flush privileges to take effect.

1. Full database authorization

Mysql > grant select on *. * to user1 identified by '123456'

At this point, the select_priv field value of the user in the user table is Y-- select * from mysql.user where user='user1'

Note:

Grant all on *. * to user gives' user'@'%' all the permissions, including the permission to create create database. Without adding anything after user, it means' user'@'%''.

Grant create on *. * to user gives' user'@'%' permission to create databases and create all tables separately.

2. Authorize a database

Mysql > grant update on test1.* to user1

At this point, the update_priv field value of the user in the db table is Y-- select * from mysql.db where user='user1'

3. Authorization of a table

Mysql > grant insert on test1.t1 to user1

At this point, the table_priv field value of the user in the tables_priv table is insert-- select * from mysql.tables_priv where user='user1'

4. Authorization of a column of a table

Mysql > grant update (ssl_type) on table test1.t2 to user1

At this point, the column_priv field value of the user in the tables_priv table is update-- select * from mysql.tables_priv where user='user1'

At this point, the column_priv field value of the user in the columns_priv table is update-- select * from mysql.columns_priv where user='user1'

5. Authorization of stored procedures

Mysql > grant create routine on test1.* to user1

Mysql > grant alter routine on test1.* to user1

Mysql > grant execute on test1.* to user1

For the authorization of stored procedures, it is not found that a single stored procedure can be authorized, but the whole database is authorized. All recorded in create_routine_priv, alter_routine_priv, execute_prive of db table

At this point, I believe you have a deeper understanding of "what is the authorization method at each level of mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.