Go MySQL User and Rights management

======== password action for root user (change user password)========Just installed MySQL, only one has a root user, password is empty, and can only be logged in the machine! add password xxx123 to root:
./bin/mysqladmin-u Root Password xxx123
or written
./bin/mysqladmin-uroot Password xxx123 after adding the password, proceed to MySQL in this entry:
./bin/mysql-uroot-p
change root password changed from xxx123 to yy1234:
./bin/mysqladmin-uroot-pxxx123 Password yy1234======= grant permissions on database object to user ==========
The simple format that MySQL gives to user rights commands can be summarized as:
grant permissions on database object to user grant permissions on database object to user identified by "password"======== User and Rights management: the most common operation example ========
(User name: DBA1, Password: dbapasswd, login ip:192.168.0.10) //Open Manage permissions for all databases in MySQL
Grant all on * * to [e-mail protected] ' 192.168.0.10 ' identified by "dbapasswd"; //Open Manage permissions for specific databases (TESTDB) in MySQL
Grant all privileges the TestDB to [e-mail protected] ' 192.168.0.10 ' identified by ' dbapasswd ';
Or
Grant all on TestDB to [e-mail protected] ' 192.168.0.10 ' identified by ' dbapasswd ';
//Open Manage permissions for database-specific tables (testdb.table1) in MySQL
Grant all on Testdb.teable1 to [e-mail protected] ' 192.168.0.10 ' identified by ' dbapasswd '; //Open Manage permissions for partial columns of a database-specific table (testdb.table1) in MySQL
Grant Select (ID, SE, rank) on Testdb.table1 to [e-mail protected] ' 192.168.0.10 ' identified by "dbapasswd"; //Open management operation instructions
Grant SELECT, INSERT, UPDATE, delete on testdb.* to [e-mail protected] ' 192.168.0.10 ' identified by "dbapasswd"; //Reclaim permissions
Revoke all on * * from [email protected]; //view MySQL user RightsShow grants;
Show grants for [email protected];======== User and Rights Management: more detailed examples ======== The following examples are used to illustrate: One, grant General data User (test1), the right to query, insert, UPDATE, delete all table data in the database (test). Grant Select on test.* to [email protected] '% ';
Grant insert on test.* to [email protected] '% ';
Grant update on test.* to [email protected] '% ';
Grant Delete on test.* to [email protected] '% '; Alternatively, replace it with a MySQL command:
Grant SELECT, INSERT, UPDATE, delete on test.* to [email protected] '% ';
ii. Grant Database Developer (duser), 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 [email protected] ' 192.168.0.% ';
Grant ALTER on testdb.* to [email protected] ' 192.168.0.% ';
Grant drop on testdb.* to [email protected] ' 192.168.0.% '; Grant operates MySQL foreign key permissions. Grant references on testdb.* to [email protected] ' 192.168.0.% '; Grant operates MySQL temp table permissions. Grant create temporary tables on testdb.* to [email protected] ' 192.168.0.% '; Grant operates MySQL index permissions. Grant index on testdb.* to [email protected] ' 192.168.0.% '; Grant operates the MySQL view, viewing the view source code permissions. Grant CREATE view on testdb.* to [email protected] ' 192.168.0.% ';
Grant Show view on testdb.* to [email protected] ' 192.168.0.% '; Grant operates MySQL stored procedures, function permissions. Grant create routine on testdb.* to [email protected] ' 192.168.0.% '; grant alter routine on TESTDB.* to [email protected] ' 19 2.168.0.% '; Grant execute on testdb.* to [email protected] ' 192.168.0.% '; Grant General DBA manages the permissions of a MySQL database (test). Grant all privileges on test to [email protected] ' localhost ' where the keyword "privileges" can be omitted.
Grant Advanced DBA manages permissions for all databases in MySQL. Grant all on * * to [email protected] ' localhost ' MySQL grant permissions can be used on multiple levels, respectively. 1. Grant acts on the entire MySQL server:Grant SELECT On *. * to [email protected]; --DBAs can query tables in all databases in MySQL.
Grant all on * * to [email protected]; --DBA can manage all databases in MySQL 2. Grant acts on a single database:Grant Select on testdb.* to [email protected]; --DBAs can query the tables in TestDB. 3. Grant acts on a single data table:Grant SELECT, INSERT, UPDATE, delete on testdb.orders to [email protected]; 4. Grant acts on the columns in the table:Grant Select (ID, SE, rank) on testdb.apache_log to [email protected]; 5. Grant acts on stored procedures, functions:Grant execute on procedure testdb.pr_add to ' dba ' @ ' localhost '
Grant execute on function testdb.fn_add to ' dba ' @ ' localhost ' vi. viewing MySQL user RightsView current user (Own) permissions: show grants; view other MySQL User rights: Show grants for [email protected]; 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 [email protected];
Revoke all on * * from [email protected]; Viii. MySQL Grant, REVOKE user rights considerations1. 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 [e-mail protected] with GRANT option; Not to be used.      In practice, database permissions are best managed centrally by DBAs. Add: You can create a user by creating users or GRANT, which also assigns relevant permissions. The REVOKE is used to remove user rights, and drop users to delete accounts.

$ mysql-u Root-p
Password

mysql> CREATE DATABASE test; # Create a database
Query OK, 1 row Affected (0.00 sec)

mysql> show databases; # See if the database was created successfully
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
+--------------------+
3 Rows in Set (0.00 sec)

Mysql> Grant all on test.* to [e-mail protected] '% ' identified by ' 123456 ' with GRANT option; # Create Privileged Administrative users
Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,host from Mysql.user; # See if user creation is successful
+------------------+-----------+
| user | Host |
+------------------+-----------+
| User1 | %         |
| Root | 127.0.0.1 |
| Debian-sys-maint | localhost |
| Root | localhost |
| Root | Server |
+------------------+-----------+
5 rows in Set (0.00 sec)

Mysql> show grants for User1; # View User Permissions
+--------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE on *. user1 ' @ '% ' identified by PASSWORD ' *6BB ... 2ca2ad9 ' |
| GRANT all privileges the ' test '. * to ' user1 ' @ '% ' with GRANT OPTION |
+--------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

GRANT Syntax:

GRANT Privileges (columns)
On what
To user identified by "password"
With GRANT OPTION

Permissions List:

• Alter: Modifies tables and indexes.
• Create: Creates a database and a table.
• Delete: Deletes records that are already in the table.
• Drop: Discards (deletes) the database and table.
• Index: Create or discard indexes.
• Insert: Inserts a new row into the table.
• REFERENCE: Not used.
• SELECT: Retrieves the records in the table.
• UPDATE: Modifies an existing table record.
• File: Reads or writes files on the server.
• PROCESS: View the thread information that is being executed on the server or kill the thread.
• RELOAD: Overloads the authorization table or empties the log, host cache, or table cache.
• SHUTDOWN: Shut down the server.
• All: All permissions, all privileges synonyms.
• USAGE: Special "No Permissions" permission.

User accounts include both "username" and "host", which indicates where the user is allowed to access. [email protected] '% ' means any address, which can be omitted by default. can also be "[Email protected]%", "[email protected]%.abc.com" and so on. The database format is [email protected], which can be "test.*" or "*. *", which represents all tables of the test database, which represents all tables for all databases.

The clause "with GRANT OPTION" means that the user can assign permissions to other users.

We use root to create several more users, and then the test database administrator User1 assign them permissions.

Mysql> create user User2 identified by ' 123456 ', User3 identified by ' ABCD ';
Query OK, 0 rows Affected (0.00 sec)

Mysql> Select User, host from Mysql.user;
+------------------+-----------+
| User             | host      |
+------------------+-----------+
| User1            |%         |
| User2            |%         |
| User3            |%         |
| Root             | 127.0.0.1 |
| Debian-sys-maint | localhost |
| Root             | localhost |
| Root             | server    |
+------------------+-----------+
7 Rows in Set (0.00 sec)

Okay, let's quit. Use User1 login and operate against the test database.

Mysql> quit # quit
Bye

$ mysql-u user1-p123456 Test # Sign in with a new user

Mysql> Select Database (); # Confirm Current Working database
+------------+
| Database () |
+------------+
| Test       |
+------------+
1 row in Set (0.00 sec)

Mysql> select Current_User (); # Confirm Current Work account
+----------------+
| Current_User () |
+----------------+
| [Email protected]%        |
+----------------+
1 row in Set (0.00 sec)

Continue, create a data table.

mysql> CREATE TABLE table1 # Creating tables
(
Name varchar (50),
-Age integer
);
Query OK, 0 rows affected (0.02 sec)

Mysql> Show tables; # See if the table was created successfully
+----------------+
| Tables_in_test |
+----------------+
| Table1 |
+----------------+
1 row in Set (0.00 sec)

Mysql> describe table1; # View Table Structure
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar (50) |     YES | |       NULL | |
| Age | Int (11) |     YES | |       NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in Set (0.00 sec)

mysql> INSERT INTO table1 values (' Tom ', 20); # Insert Record
Query OK, 1 row Affected (0.00 sec)

Mysql> select * FROM table1; # Query Records
+------+------+
| name | Age |
+------+------+
|   Tom | 20 |
+------+------+
1 row in Set (0.00 sec)

Next we assign permissions for User2, User3.

Mysql> Grant Select on test.* to User2; # Assign SELECT permissions for User2.
Query OK, 0 rows Affected (0.00 sec)

Mysql> Grant Select on test.* to User3; # Assign SELECT permissions for User3.
Query OK, 0 rows Affected (0.00 sec)

Mysql> grant INSERT, update on test.* to User2; # Add INSERT, UPDATE permissions for User2.
Query OK, 0 rows Affected (0.00 sec)

OK, we exit, switch to User2 operation to see.

$ mysql-u user2-p123456

mysql> use test; # Switch Working database
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> Select Database (); # Verify the current working database
+------------+
| Database () |
+------------+
| Test |
+------------+
1 row in Set (0.00 sec)

Mysql> Select User (); # Verify Current Account
+-----------------+
| User () |
+-----------------+
| [Email protected] |
+-----------------+
1 row in Set (0.00 sec)

Mysql> show grants for User2; # View Current user permissions, apparently later added INSERT, UPDATE was added.
+--------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE on *. user2 ' @ '% ' identified by PASSWORD ' *6bb837 .... 2C9 ' |
| GRANT SELECT, INSERT, UPDATE on ' test '. * to ' user2 ' @ '% ' |
+--------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

For operational testing.

mysql> INSERT INTO table1 values ("Jack", 21); # INSERT Operation succeeded
Query OK, 1 row Affected (0.00 sec)

mysql> Update table1 set age=22 where name= ' Jack '; # UPDATE Operation succeeded
Query OK, 1 row Affected (0.00 sec)
Rows matched:1  changed:1  warnings:0

Mysql> select * FROM table1; # SELECT Operation succeeded
+------+------+
| name | Age  |
+------+------+
| Tom  |   20 |
| Jack |   22 |
+------+------+
2 rows in Set (0.00 sec)

mysql> Delete from table1 where age=22; # DELETE operation has no permissions
ERROR 1142 (42000): DELETE command denied to user ' user2 ' @ ' localhost ' for table ' table1 '

We switch back to the User1 Admin account and remove the User2 UPDATE permissions to see.

$ mysql-u user1-p123456 Test

mysql> revoke update on test.* from User2; # Remove UPDATE Permissions
Query OK, 0 rows Affected (0.00 sec)

Switch back to User2 again.

$ mysql-u user2-p123456 Test

Mysql> show grants for User2; # UPDATE permissions are removed
+--------------------------------------------------------------------------------------------------+
| Grants for [email protected]%                                                                               |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE on *. user2 ' @ '% ' identified by PASSWORD ' *6b ... 2ad9 '                            |
| GRANT SELECT, INSERT on ' test '. * to ' user2 ' @ '% '                                                  |
+--------------------------------------------------------------------------------------------------+
2 rows in Set (0.00 sec)

mysql> Update table1 set age=23 where name= ' Jack '; # do not have UPDATE permissions
ERROR 1142 (42000): UPDATE command denied to user ' user2 ' @ ' localhost ' for table ' table1 '

Well, here we have basically completed the operation of creating the user and assigning permissions. Next, we go back to root to modify the user password and delete the user action.

$ mysql-u root-p123456

Mysql> Set password for User3=password (' abcabc '); # Modify User user3 password
Query OK, 0 rows Affected (0.00 sec)

Mysql>flush privileges; # Refresh the permissions table (usually only needed after modifying the relevant Management data table directly)
Query OK, 0 rows Affected (0.00 sec)


Query OK, 0 rows Affected (0.00 sec)

mysql> drop user user2; # Delete User2 Account
Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,host from Mysql.user; # Verify Delete Results
+------------------+-----------+
| User             | host      |
+------------------+-----------+
| User1            |%         |
| User3            |%         |
| Root             | 127.0.0.1 |
| Debian-sys-maint | localhost |
| Root             | localhost |
| Root             | server    |
+------------------+-----------+
6 rows in Set (0.00 sec)

User User2 cannot be used again.

$ mysql-u user2-p123456 Test

ERROR 1045 (28000): Access denied for user ' user2 ' @ ' localhost ' (using Password:yes)

Try User3.

$ mysql-u USER3-PABC Test # Connection Failed! Oh, yes, we changed the password.
ERROR 1045 (28000): Access denied for user ' user3 ' @ ' localhost ' (using Password:yes)

$ mysql-u USER3-PABCABC Test # New Password succeeded

Mysql> select * FROM table1; # SELECT Operation succeeded
+------+------+
| name | Age  |
+------+------+
| Tom  |   20 |
| Jack |   22 |
+------+------+
2 rows in Set (0.00 sec)

To modify your password directly execute "Set password = password (' new_password ');".

-------Summary--------------------------------------

To create a user:

GRANT Insert, Update on testdb.* to [email protected] '% ' identified by ' password ' with GRANT OPTION;
CREATE USER user2 identified by ' password ';

Assigning permissions:

GRANT Select on testdb.* to User2;

To view permissions:

SHOW GRANTS for User1;

Change Password:

SET PASSWORD for user1 = PASSWORD (' newpwd ');
SET PASSWORD = PASSWORD (' newpwd ');

Remove Permissions:

To delete a user:

DROP USER user1;

Database list:

SHOW DATABASES;

Data Table list:

SHOW TABLES;

Current database:

SELECT DATABASE ();

Current User:

SELECT USER ();

Data table structure:

DESCRIBE table1;

Refresh permissions:

FLUSH privileges;

"Turn from": blog.chinaunix.net/uid-10697776-id-2935586.html

Go MySQL User and Rights management