User management and permissions and Settings--mysql

MySQL is one of the most popular database management systems in the world. Starting with the introduction of simple data retrieval, the book gradually delves into complex content, including the use of joins, subqueries, regular expressions, and full-text-based searches, stored procedures, cursors, triggers, table constraints, and so on. By focusing on the chapters, a clear, systematic and concise description of the knowledge that the reader should grasp, so that they inadvertently immediately increase the skill. This section focuses on MySQL user management and permissions settings related to the collation of commands.

User Management

Mysql>use MySQL;

View

Mysql>select Host,user,password from user;

Create

Mysql>create user zx_root;

Modify

Mysql>rename user Feng to NewUser; Can be used after MySQL 5, you need to update the user table using update

Delete

Mysql>drop user NewUser;   MySQL5 before you delete a user, you must first use revoke to remove the user right, and then delete the user, mysql5 the drop command can delete the user's associated permissions at the same time

Change Password

Mysql>set Password for zx_root =password (' xxxxxx '); mysql>update  mysql.user  set  Password=password (' xxxx ')  where user= ' OtherUser '

View User Permissions

Mysql>show grants for Zx_root;

granting permissions

Mysql>grant Select on dmc_db.* to  Zx_root;

Reclaim Permissions

Mysql>revoke  Select on dmc_db.*  from  zx_root;  Error if no permissions exist

The above command can also be granted and reclaimed using multiple permissions, separated by commas between permissions

Mysql>grant select,update,delete  , insert on  dmc_db.*  to  zx_root;

If you want to see the results immediately, use

Flush  privileges;

Command update

You must give a message when setting permissions

1, permissions to grant

2, the database or table that is granted access

3, User name

Grant and revoke can control access at several levels

1, entire server, using grant all and revoke all

2, entire database, using on database.*

3, Feature table, using on database.table

4, a specific column

5, a specific stored procedure

Meaning of the value of the host column in the user table

% matches all hosts

localhost localhost will not be parsed into an IP address and connected directly via Unixsocket

The 127.0.0.1 is connected via the TCP/IP protocol and can only be accessed natively

:: 1:: 1 is compatible with support IPv6, indicating the 127.0.0.1 with IPv4

Grant normal data user, the right to query, insert, UPDATE, delete all table data in the database.

Grant SELECT on testdb.* to common_user@ '% ' grant insert in testdb.* to common_user@ '% ' grant update on testdb.* to Common_u ser@ '% ' grant Delete on testdb.* to common_user@ '% '

Alternatively, replace it with a MySQL command:

Grant SELECT, INSERT, UPDATE, delete on testdb.* to common_user@ '% '

9>.grant database developers, 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 developer@ ' 192.168.0.% ', Grant alter on testdb.* to developer@ ' 192.168.0.% ', Grant drop on TESTD b.* to developer@ ' 192.168.0.% ';

Grant operates MySQL foreign key permissions.

Grant references on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL temp table permissions.

Grant create temporary tables on testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL index permissions.

Grant index on testdb.* to developer@ ' 192.168.0.% ';

Grant operates the MySQL view, viewing the view source code permissions.

Grant CREATE view on testdb.* to developer@ ' 192.168.0.% '; Grant show view in testdb.* to developer@ ' 192.168.0.% ';

Grant operates MySQL stored procedures, function permissions.

Grant create routine on testdb.* to developer@ ' 192.168.0.% '; -Now, can show procedure statusgrant alter routine on testdb.* to developer@ ' 192.168.0.% '; -Now, you can drop a proceduregrant-execute on testdb.* to developer@ ' 192.168.0.% ';

10>.grant the normal DBA to manage permissions for a MySQL database.

Grant all privileges on TestDB to dba@ ' localhost '

Where the keyword "privileges" can be omitted.

The 11>.grant advanced DBA manages permissions for all databases in MySQL.

Grant all on * * to dba@ ' localhost '

12>. MySQL grant permissions can be used on multiple levels, respectively.

1. Grant acts on the entire MySQL server:

Grant SELECT On *. * to Dba@localhost; --DBAs can query tables in all databases in MySQL. Grant all on *. * to Dba@localhost; --DBA can manage all databases in MySQL

2. Grant acts on a single database:

Grant SELECT on testdb.* to Dba@localhost; --DBAs can query the tables in TestDB.

3. Grant acts on a single data table:

Grant SELECT, INSERT, UPDATE, delete on testdb.orders to Dba@localhost;

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on Testdb.apache_log to Dba@localhost;

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 '

Note: Be sure to refresh the service after modifying the permissions, or restart the service, refresh the service by: Flush privileges.