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

This article mainly introduces the relevant knowledge of "mysql security, access control and permissions configuration". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "mysql security, access control and permissions configuration" can help you solve the problem.

After creating the mysql database, the system automatically creates the test library and the mysql library.

There are test data in test.

The mysql library is equivalent to the system tablespace of oracle and records the related contents of the library.

The current version of three thousand miles of the bright moon is 5.0.18.

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.0.18-nt |

+-+

1 row in set (0.00 sec)

The system table under version 5.0.18 includes the following tables

Mysql > show tables from mysql

+-+

| | Tables_in_mysql |

+-+

| | columns_priv |

| | db |

| | func |

| | help_category |

| | help_keyword |

| | help_relation |

| | help_topic |

| | host |

| | proc |

| | procs_priv |

| | tables_priv |

| | time_zone |

| | time_zone_leap_second |

| | time_zone_name |

| | time_zone_transition |

| | time_zone_transition_type |

| | user |

+-+

17 rows in set (0.00 sec)

-

User table

| | user | CREATE TABLE `user` (

`Host`char (60) collate utf8_bin NOT NULL default''

`User`char (16) collate utf8_bin NOT NULL default''

`Password` char (41) character set latin1 collate latin1_bin NOT NULL default''

`Select_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Insert_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Update_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Delete_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Create_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Drop_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Reload_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Shutdown_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Process_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`File_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Grant_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`References_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Index_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Alter_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Show_db_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Super_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Create_tmp_table_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Lock_tables_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Execute_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Repl_slave_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Repl_client_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Create_view_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Show_view_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Create_routine_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Alter_routine_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`Create_user_ priv`enum ('Numeric Magazine Y') character set utf8 NOT NULL default' N'

`ssl_ type`enum ('', 'ANY','X509','SPECIFIED') character set utf8 NOT NULL default''

`ssl_ cipher` blob NOT NULL

`x509 _ issuer` blob NOT NULL

`x509 _ roomt` blob NOT NULL

`max_ peos`int (11) unsigned NOT NULL default'0'

`max_ updates`int (11) unsigned NOT NULL default'0'

`max_ connections` int (11) unsigned NOT NULL default'0'

`max_user_ connections` int (11) unsigned NOT NULL default'0'

PRIMARY KEY (`Host`, `User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |

The three fields `Host`, `User` and `Password` record the user's machine password that allows access to the database and the hosts that can be connected.

Mysql > select host,user,password from user

+-- +

| | host | user | password | |

+-- +

| | localhost | root | * B22DF64775852C409540CACB81399E4D2E7F93AC |

+-- +

1 row in set (0.00 sec)

Other fields include information such as permission control, whether to encrypt passwords, etc.

-

Db table and host table

Two tables are used together to record what the user can do with those databases

Mysql > desc db

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | Host | char (60) | NO | PRI | |

| | Db | char (64) | NO | PRI | |

| | User | char (16) | NO | PRI | |

| | Select_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Insert_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Update_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Delete_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Drop_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Grant_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | References_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Index_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_tmp_table_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Lock_tables_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Show_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Execute_priv | enum ('Nickel Magazine Y') | NO | | N | |

+-+ +

20 rows in set (0.00 sec)

Mysql > desc host

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | Host | char (60) | NO | PRI | |

| | Db | char (64) | NO | PRI | |

| | Select_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Insert_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Update_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Delete_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Drop_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Grant_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | References_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Index_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_tmp_table_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Lock_tables_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Show_view_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Create_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Alter_routine_priv | enum ('Nickel Magazine Y') | NO | | N | |

| | Execute_priv | enum ('Nickel Magazine Y') | NO | | N | |

+-+ +

19 rows in set (0.01 sec)

-

Tables_ privy table and columns_ privy table

Record permissions assigned and restricted by dba

Corresponding to the permissions and fields at the table level respectively.

When obtaining permissions, read the user table first, and make sure that the user has link permissions before further verifying whether there are other permissions.

So then access the db table and the host table.

Finally, there are the tables_ private table and the column_ private table.

Special circumstances:

Local user localhost can connect to all databases in the system in root situation

Windows allows local connections to access all databases, and unix allows local full access to test databases

Access is denied by default to users from other hosts

-

Authorization and abrogation of kernel browsing user rights

Local connection

Mysql-u root-p

Full table authorization:

Mysql > grant select on an.a to test@localhost identified by 'anbaisheng'

Field authorization:

Mysql > grant select (id,name) on an.a to test@localhost

Reclaim permissions:

Mysql > revoke select on an.a from test@localhost

Mysql also provides all permission levels as a shortcut to all permissions

To grant full permissions to the an user to test:

Mysql > grant all on an.* to test@localhost

Mysql also provides the usage permission level, which only has the creation user but does not grant any other permissions

Mysql > grant usage on an.* to test@localhost

Query OK, 0 rows affected (0.00 sec)

-

Restrict the use of resources

Fields in the user table

Max_questions

Max_updates

Max_connections

Used to limit the number of queries, tables, or record updates and new connections per hour for a specific user

These restrictions can be written in grant statements, using the with keyword

You can also insert data directly into the user table for permission management

After inserting data, you need flush privileges to make it effective.

-

Query permission

Show grants for user@server

Mysql > show grants for test@localhost

+-+

| | Grants for test@localhost |

+-+

| | GRANT USAGE ON *. * TO 'test'@'localhost' IDENTIFIED BY PASSWORD' * B22DF64775852C409540CACB81399E4D2E7F93AC' |

| | GRANT ALL PRIVILEGES ON `an`.* TO 'test'@'localhost' |

| | GRANT SELECT (name, id) ON `an`.`a`TO 'test'@'localhost' |

+-+

3 rows in set (0.00 sec)

-

Reload the authorization table

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

/ usr/local/mysql/bin/mysqladmin-u root reload

-

Reset Authorization Table

1/usr/local/mysql/support-files/mysql.server stop

2rm-rf / usr/local/mysql/data/mysql

3/usr/local/mysql/scripts/mysql_install_db

4\ chown-R mysql:mysql / usr/local/mysql/data/mysql

5/usr/local/mysql/support-files/mysql.server start

--

Change user password

Mysql-h localhost-u logger-p

The following syntax can be used in the script

Mysql-h localhost-u logger-ptimber

If you change the password directly in the user table, you must use the password function to convert the plaintext password.

Update user set password = password ('anbaisheng')

Flush privileges

Mysql > set password for test@localhost=password ('anbaisheng')

-

Set root password

Effective immediately

/ usr/local/mysql/bin/mysqladmin-u root password' new_password'

You can also use regular changes to modify

-

Reset root password

1 、 / usr/local/mysql/support-files/mysql.server stop

2. / usr/local/mysql/bin/mysqld_safe-skip-grant-tables-skip-networking

3 、 mysql

Use mysql

Update user set password = password ('new-password') where user='root'

4 、 / usr/local/mysql/support-files/mysql.server stop

/ usr/local/mysql/support-files/mysql.server start

This is the end of the introduction to "how to configure mysql Security, access Control and permissions". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.