MySQL Grant command detailed

Source: Internet
Author: User
Tags mysql index mysql view

Grant permissions on object to user

A grant general data user, the right to query, insert, UPDATE, delete all table data in the database.
Grant SELECT on testdb.* to
Grant insert on testdb.* to
Grant Update on testdb.* to
Grant Delete on testdb.* to

Alternatively, replace it with a MySQL command:
Grant SELECT, INSERT, UPDATE, delete on testdb.* to

II. Grant Database Developer, creating tables, indexes, views, stored procedures, functions ... and other permissions.
Grant creates, modifies, and deletes MySQL data table structure permissions.
Grant create on testdb.* to;
Grant alter on testdb.* to;
Grant drop on testdb.* to;

Grant operates MySQL foreign key permissions.
Grant references on testdb.* to;

Grant operates MySQL temp table permissions.
Grant create temporary tables on testdb.* to;

Grant operates MySQL index permissions.
Grant index on testdb.* to;

Grant operates the MySQL view, viewing the view source code permissions.
Grant CREATE view on testdb.* to;
Grant Show view on testdb.* to;

Grant operates MySQL stored procedures, function permissions.
Grant create routine on testdb.* to; --now, can show procedure status
Grant alter routine on TESTDB.* to; --Now, can drop aprocedure
Grant execute on testdb.* to;

Grant General DBA manages permissions for a MySQL database.
Grant all privileges the TestDB to

Where the keyword "privileges" can be omitted.
Grant Advanced DBA manages permissions for all databases in MySQL.
Grant all on * * to

MySQL grant permissions can be used on multiple levels, respectively.
1. Grant acts on the entire MySQL server:
Grant SELECT On *. * to; --DBAs can query tables in all databases in MySQL.
Grant all on * * to; --DBA can manage all databases in MySQL

2. Grant acts on a single database:
Grant select on testdb.* to; --DBAs can query the tables in TestDB.

3. Grant acts on a single data table:
Grant SELECT, INSERT, UPDATE, delete on testdb.orders to;

4. Grant acts on the columns in the table:
Grant Select (ID, SE, rank) on Testdb.apache_log to;

5. Grant acts on stored procedures, functions:
Grant execute on procedure testdb.pr_add to
Grant execute on function Testdb.fn_add to

Vi. Viewing MySQL user rights
To view the current user (own) permissions:
Show grants;

To view additional MySQL user rights:
Show grants for;

Vii. revoke permissions that have been given to MySQL users.
Revoke is similar to Grant's syntax, just replace the keyword "to" with "from":
Grant all on * * to;
Revoke all on * * FROM;

Viii. MySQL Grant, REVOKE user rights considerations
1. Grant, after revoke user rights, the user has to reconnect to the MySQL database for the permission to take effect. 2. If you want to allow authorized users, you can also grant these permissions to other users, you need the option "grant option"
Grant SELECT on testdb.* to with GRANT option;

This feature is generally not available. In practice, database permissions are best managed centrally by DBAs.


This article is from the "8655823" blog, please be sure to keep this source http://8665823.blog.51cto.com/8655823/1857805

MySQL Grant command detailed

Related Article

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.