New MySQL users and Database Access authorization, new mysql

Source: Internet
Author: User
Tags mysql view

New MySQL users and Database Access authorization, new mysql

# Mysql-u root-p # Allow local IP addresses to access localhost, 127.0.0.1 # insert into mysql. user (Host, User, Password) values ('localhost', 'zhouz', password ('000000'); # allow Internet IP Access # insert into mysql. user (Host, User, Password) values ('%', 'zhouz', password ('000000'); # refresh authorization # flush privileges; # create database zhouzdb default charset utf8 COLLATE utf8_general_ci; # grant all permissions to the database through the Internet IP address # grant all privileges on 'zhouzdb '. * to 'zhouz' @ '%' identified by '000000'; # grant all permissions to the database on the local server # grant all privileges on 'zhouzdb '. * to 'zhouz' @ 'localhost' identified by '000000'; # refresh permissions # flush privileges; # log out of root and log on again # \ q # log on to zhouz with a new account, because % arbitrary IP address is used for connection, you need to specify the external access IP address # mysql-u zhouz-h 192.168.1.168-p #1234

In normal times, it is rare to use the command line mode of mysql to operate. Today, we will create a new user authorization, and we will have some problems in this article.

NOTE: If only the Internet access permission is enabled, local access is not allowed. If you need to allow access from the local server to mysql, you need to increase the local access permission.


How does MySQL authorize a self-created user such as daitest to create a new database? Command

Take a look
In mysql, you can grant one or more permissions to a user, such as select, insert, update, and delete. The grant command is used in the following format:
Grant permission on database objects to users
1. grant normal data users the right to query, insert, update, and delete all table data in the database.
Grant select on testdb. * to common_user @ '%'
Grant insert on testdb. * to common_user @ '%'
Grant update on testdb. * to common_user @ '%'
Grant delete on testdb. * to common_user @ '%'
Alternatively, replace the following with a mysql command:
Grant select, insert, update, delete on testdb. * to common_user @ '%'

2. grant database developers to create tables, indexes, views, stored procedures, and functions... .
Grant permissions to create, modify, and delete mysql Data Table structures.
Grant create on testdb. * to developer @ '192. 192.% ';
Grant alter on testdb. * to developer @ '192. 192.% ';
Grant drop on testdb. * to developer @ '192. 192.% ';
Grant the mysql foreign key operation permission.
Grant references on testdb. * to developer @ '192. 192.% ';
Grant the permission to operate mysql temporary tables.
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';
Grant the permission to operate mysql indexes.
Grant index on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate the mysql view and view the source code.
Grant create view on testdb. * to developer @ '192. 192.% ';
Grant show view on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate mysql stored procedures and functions.
Grant create routine on testdb. * to developer @ '192. 192.% ';-now, can show procedure status
Grant alter routine on testdb. * to developer @ '192. 192.% ';-now, you can drop a procedure
Grant execute on testdb. * to developer @ '192. 192.% ';

3. grant common dba permission to manage a mysql database.
Grant all privileges on testdb to dba @ 'localhost'
The keyword "privileges" can be omitted.

4. grant senior dba permission to manage all databases in mysql.
Grant... the remaining full text>

How can I grant a remote IP address access permission to a MYSQL database?

Your MYSQL database contains a database named mysql, which contains a table named user. You can check the structure and existing data of the table, you will know what to do. For example, you can add a piece of data, and the IP address specified by the Host for you, the user can be %, and the password doesn't matter, the corresponding permissions (all can be Y) are set later, so that the machine can connect to the database regardless of the user and password, and has the permissions you specified.

Be patient. I believe you will understand it at a glance.
Reference: blog.chinaunix.net/u2/73743/showart_1094545.html

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.