MySQL user and permission management

Source: Internet
Author: User
Tags mysql view

========== Root User Password operation (Change User Password)======== For MySQL just installed, only one root user has a blank password and can only log on to the local machine! Add the password xxx123 to the root user:
./Bin/mysqladmin-u Root Password xxx123
Or write
./Bin/mysqladmin-uroot password xxx123 After the password is added, enter MySql in this tutorial:
./Bin/MySQL-uroot-P Change the root password from xxx123 to yy1234:
./Bin/mysqladmin-uroot-pxxx123 password yy1234 ====== Grant permission on database objects to users============
The simple format of commands granted to users by MySQL is as follows:
Grant permission on database objects to users Grant permission on database object to user identified by "password"========== User and permission management: The most common operation instance ========
(Username: dba1, password: dbapasswd, logon IP: 192.168.0.10) // Open the permission to manage all databases in MySQL
Grant all on *. * To dba1 @ '192. 168.0.10 'identified by "dbapasswd "; // Open the permission to manage a specific MySQL database (testdb)
Grant all privileges on testdb to dba1 @ '192. 168.0.10 'identified by "dbapasswd ";
Or
Grant all on testdb to dba1 @ '192. 168.0.10 'identified by "dbapasswd "; // Open the permission to manage tables (testdb. Table1) of a specific MySQL database
Grant all on testdb. teable1 to dba1 @ '192. 168.0.10 'identified by "dbapasswd "; // Open the permission to manage some columns of a table (testdb. Table1) of a specific database in MySQL
Grant select (ID, Se, rank) on testdb. Table1 to BA1 @ '192. 168.0.10 'identified by "dbapasswd "; // Open management operation commands
Grant select, insert, update, delete on testdb. * To dba1 @ '192. 168.0.10 'identified by "dbapasswd "; // Revoke permissions
Revoke all on *. * From dba1 @ localhost; // View MySQL user permissionsShow grants;
Show grants for dba1 @ localhost; ======== User and permission management: More detailed instances ======== The following is an example:  1. Grant normal data users (test1) the right to query, insert, update, and delete all table data in the database (test.Grant select on test. * To test1 @ '% ';
Grant insert on test. * To test1 @ '% ';
Grant update on test. * To test1 @ '% ';
Grant delete on test. * To test1 @ '%'; or use a MySQL command instead:
Grant select, insert, update, delete on test. * To test1 @ '% '; 2. Grant database developers (dusers) 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 duser @ '192. 192.% ';
Grant alter on testdb. * To duser @ '192. 192.% ';
Grant drop on testdb. * To duser @ '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 duser @ '192. 192.% ';
Grant Show view on testdb. * To duser @ '192. 192.% '; Grant permissions to operate MySQL stored procedures and functions.Grant create routine on testdb. * To duser @ '2017. 168.0.% '; grant alter routine on testdb. * To duser @ '2017. 168.0.% '; grant execute on testdb. * To duser @ '2017. 168.0.% '; 3. Grant common DBA permission to manage a MySQL database (test.Grant all privileges on test to DBA @ 'localhost', where the keyword "privileges" can be omitted. 4. Grant senior DBA permission to manage all databases in MySQL.Grant all on *. * to DBA @ 'localhost' 5. MySQL grant permissions can be applied to multiple levels.  1. Grant applies to the entire MySQL Server:Grant select on *. * to DBA @ localhost; -- DBA can query tables in all databases in MySQL.
Grant all on *. * to DBA @ localhost; -- DBA can manage all databases in MySQL 2. Grant applies to a single database:Grant select on testdb. * to DBA @ localhost; -- DBA can query tables in testdb. 3. Grant applies to a single data table:Grant select, insert, update, delete on testdb. Orders to DBA @ localhost; 4. Grant applies to columns in the table:Grant select (ID, Se, rank) on testdb. apache_log to DBA @ localhost; 5. Grant applies to stored procedures and functions:Grant execute on procedure testdb. pr_add to 'dba '@ 'localhost'
Grant execute on function testdb. fn_add to 'dba '@ 'localhost' Vi. View MySQL user permissionsView Current user (own) permissions: Show grants; view other MySQL user permissions: Show grants for DBA @ localhost; 7. revoke permissions granted to mysql users.The revoke syntax is similar to that of Grant. You only need to replace the keyword "to" with "from": grant all on *. * to DBA @ localhost;
Revoke all on *. * from DBA @ localhost; VIII. Considerations for MySQL Grant and revoke User Permissions1. After the grant and revoke permissions are granted, the permissions can only take effect after the user reconnects to the MySQL database. 2. if you want the authorized user to grant these permissions to other users, You need to select "grant option" Grant select on testdb. * To DBA @ localhost with grant option; this feature is generally unavailable. In practice, it is best for DBAs to manage database permissions in a unified manner.

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.