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

Below, I would like to tell you briefly about the specific operation flow of the basic authorization of the mysql database. Have you known about similar topics before? If you are interested, let's take a look at this article. I believe it is more or less helpful for everyone to read the specific operation flow of the basic authorization of the mysql database.

View the version of the mysql database

Log in to the database to view:

1. When a user logs in with a password

Mysql-uroot-p123456

2.mysql > status

3.mysql > select version ()

View when you are not logged into the database:

1.mysql-- help | grep Distrib

2.rpm-qa | grep mysql

Basic operation and authorization of mysql: (introduction 5.7 and 5.6 are different in many ways)

MySQL Server version: 5.7.23

The database test of this article

The table name of this article is test

User name test for this article

The show databases; command to see which databases have been created.

Show columns from test or desc test; get table structure command:

Shou tables views all the tables

Use database1; switch database

Show grants; view the permissions of the current user

Show grants for test@ "%"; view the permissions of other users. Test@ "%" represents the user and host fields in the user table.

Flush privileges; refresh system permissions

There is no setting like oracle set line 100pages 9999 in mysql. You can add\ G at the end.

For example: select * from user\ G

Mysql-h hostname-u user name-p

-p: password login, if the login user name password is empty, you can ignore this option.

-D: selected database name

Reset the database password for mysql

1. First stop the mysql database is usually installed in / etc/init.d/mysqld stop

2. Modify the configuration file / etc/my.cnf of mysql

Adding skip-grant-tables on the last line means you can skip permissions to log in.

3. Restart mysql database / etc/init.d/mysqld start

3. Use mysql-u root-p

4. Modify the root password.

Update user set password=PASSWORD ("123456") where user='root'

5. Modify the configuration file to delete or disable the line skip-grant-tables.

Note: the old version of mysql database uses the parameter authentication_string, and the new version uses the parameter password.

Update user set authentication_string=password ('123456') where user='root'

Update user set password=password ('123456') where user='root'

Flush privileges;-refresh the system permissions table

Create a database

Create database test character set gbk

Test and build table

Create table test (id int,name varchar (20), bianma varchar (20))

INSERT INTO test VALUES (1 recording tom1 recording 13 211')

INSERT INTO test VALUES (2 memorials, tom2 girls, 13 212')

INSERT INTO test VALUES (3 recordings tom3 minutes 13 13')

INSERT INTO test VALUES (4 recollection tom4)

Delete database

Drop database test_database

Delete tabl

Drop table test_tab

Create user commands:

GRANT USAGE ON. TO 'test'@'localhost' IDENTIFIED BY' 123456 'WITH GRANT OPTION

Or

Create user 'test1'@'%' identified by' 123456'

Either command is fine. The user name and test password is 123456.

Delete user command:

Mysql > drop user 'test1'@'%'

Mysql > drop user 'test'@'localhost'

Note to delete the format: 'user'@'host'

Mysql database authorization:

Grant permissions on database object to user

Permissions: add, delete, modify and check, insert,delete,update,select,create,alter,drop,all, etc.

References foreign key permissions, create temporary create temporary table permissions, index create index permissions, create view and show view create and view view permissions, create routine and alter routine create and modify stored procedures permissions, execute operation function permissions

Database objects: table name, database name. Table name, test.,.*, etc.

User: the user who wants to get permission, test,test@localhost,test@ "%", which means user and host in the user table

Authorized user test has permissions to view only two fields in the table test

Grant select (name,id) on test to test@'%'

The authorized test user has all permissions to the test database:

Grant all privileges on test. To test@localhost identified by '123456;-- privileges can be omitted

Grant all on test. To test@localhost

Authorize part of the permissions to users

Grant select,update on test. To test@localhost identified by '123456'

Grant select,update on test. To test@localhost

Authorized test users have certain permissions for all databases:

Grant select,delete,update,create,drop on. To test@ "%" identified by "123456"

Or

Grant all on test.* to test@localhost

Test users can authorize select permissions for all tables in the test database to other users

Grant select on test. To test with grant option

Note: the above example sentence will be replaced with the table name, and a table in the test database will be used as the database object

Cancellation of authorization, revocation of authorization, withdrawal of authorization

Revoke all on. From test

The statements of authorization and de-authorization are basically the same, grant and revoke are interchanged, from and to are interchangeable.

Cancel authorization

Revoke select on. From test

Authorization

Grant select on. To test

Mysql can set pagesize and linesize like Oracle's sqlplus

Select * from table_name\ G

Select can be applied after where in mysql delete

Delete test_user from test_user a, (select id from test_user where id

< 10) b where a.id = b.id 表数据太大只查看5行 select from gp_plat_user where rownum < 6; select from gp_plat_user limit 6; 错误集锦： 报错1.Ignoring query to other database mysql>

Show databases

Ignoring query to other database

Mysql > show user

Ignoring query to other database

Scheme:

Login usage parameter-u

Error 2.

ERROR 1130 (HY000): Host '10.1.1.10' is not allowed to connect to this MySQL server

[root@master logs] # mysql-uroot-p123456-h 10.1.1.10

Mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1130 (HY000): Host '10.1.1.10' is not allowed to connect to this MySQL server

Solution: skip password login and modify the host field in the database

Mysql > select host,user from user where user='root'

+-+ +

| | host | user |

+-+ +

| | localhost | root |

+-+ +

1 row in set (0.00 sec)

Mysql > update user set host ='% 'where user =' root'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > commit;-- this command can also be submitted

Query OK, 0 rows affected (0.00 sec)

Mysql > select host,user from user where user='root'

+-+ +

| | host | user |

+-+ +

| |% | root |

+-+ +

1 row in set (0.00 sec)

Error 3.

Error logging in the Times:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Scheme: mysql-uroot-p123456

Error 4. Query the database after login and report an error:

Mysql > show databases

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Prompt to change the password with the command ALTER USER before making the operation.

I use the following alter command to modify it, but it doesn't work well, so I use set to modify it, which can be used after modification.

Scheme: alter user 'root'@'localhost' identified by' 123456;-- it doesn't work

Set password=password ("123456");-- works

Error 5.

Mysql > insert into user (Host,User,authentication_string) values ("localhost", "test", password ("123456"))

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Solution: set global validate_password_policy=0

Set global validate_password_length=1

How to view it: select @ @ validate_password_policy

Select @ @ validate_password_length

For more information, please see the technology blogger https://www.cnblogs.com/ivictor/p/5142809.html.

Error 6.

Mysql > insert into user (Host,User,authentication_string) values ("localhost", "test", password ("123456"))

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

The error ssl_cipher field cannot be empty, and the mysql added user cannot be directly in the insert user table.

Solution: it is said on the Internet to modify the configuration file my.cnf by

Change sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES to: sql_mode=NO_ENGINE_SUBSTITUTION

For version 5.7 of mysql that doesn't work, use the following command to create a new user

GRANT USAGE ON. TO 'test'@'localhost' IDENTIFIED BY' 123456 'WITH GRANT OPTION

Mysql > GRANT USAGE ON. TO 'test'@'localhost' IDENTIFIED BY' 123456 'WITH GRANT OPTION

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > select user,host from user

+-+ +

| | user | host |

+-+ +

| | root |% | |

| | mysql.session | localhost |

| | mysql.sys | localhost |

| | test | localhost |

+-+ +

4 rows in set (0.00 sec)

Error 7:

[root@master logs] # mysql-utest-ptest

Mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)

Solution: same as error 2

Modify host:update user set host ='% 'where user =' test'

Keyboard shortcuts for navicat:

Ctrl+Q and Ctrl+N open the query window

Ctrl+/ comment sql statement

Ctrl+Shift + / uncomment

Ctrl+R runs the sql statement of the query window

F6 opens a mysql command line window

Ctrl+L deletes a row

Ctrl+W closes a query window

The data display page of the Ctrl+D table switches to the structural design page of the table, but the current row is copied when the query page writes sql

Several tables related to permissions

What do you think of the specific operation process of the basic authorization of the mysql database? what do you think of this article and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

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.