MySQL changes User Permissions

MySQL changes User Permissions

This entry was posted by admin Monday, 26 then l, 2010

1. "grant all on *. * to root @ '%' identified by 'yourpassword';" -- the password can also be set along with the password.
2. "Flush privileges;" -- refresh to make the permission take effect.
For some other management of MySQL, you can use the mysqladmin command. It can be used to set a password or something.

For details about Grant, refer to the reposted below:
This example runs on MySQL 5.0 and later.

The simple format of commands granted to users by MySQL is as follows:

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. *
Developer @ '2014. 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. *
Developer @ '2014. 192.% ';

Grant permissions to operate MySQL stored procedures and functions.

Grant create routine on testdb. * To developer @ '2017. 192.% ';
-Now, can show procedure status
Grant alter routine on testdb. * To developer @ '2017. 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 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 MySQL
All tables in the database.
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 testdb
.

3. Grant applies to a single data table:

Grant select, insert, update, delete on testdb. Orders
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
'Dba '@ 'localhost'
Grant execute on function testdb. fn_add
'Dba '@ 'localhost'

Vi. View MySQL user permissions

View Current user (own) permissions:

Show grants;

View other MySQL user permissions:

Show grants for DBA @ localhost;

7. revoke permissions granted to mysql users.

The syntax of revoke 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 Permissions

1. 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 to grant the authorized users, you can 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.

Category: Post
You can follow any responses to this entry via RSS.
Comments are currently closed, but you can trackback from your own
Site.

========================================================== ======================================

1. Create and authorize a user

Syntax of the grant statement:

Grant
Privileges (columns) on what to user identified by "password"
Grant Option
To use this sentence pattern, make sure that the fields are:

Privileges permission specifies the operation permitted by the token
Alter
Modify tables and Indexes
Create
Create databases and tables
Delete
Delete existing records in the table
Drop
Discard (delete) databases and tables
Index
Create or discard an index
Insert
Insert a new row to the table
Reference
Unused
Select
Retrieve records in a table
Update
Modify existing table records
File
Read or write files on the server
Process
View the thread information executed on the server or kill the thread
Reload
Reload the authorization table or clear logs, host caches, or table caches.
Shutdown
Disable the server
All all; all
Synonym for privileges
Usage
Special "no permission" permission

The preceding permissions are divided into three groups:

Group 1: Applicable to databases, tables, and columns, such as alter create Delete drop Index insert select
Update

Second group: Number of Management permissions. They allow users to strictly authorize operations that affect the server, for example, file process reload shut *

Group 3: special permissions all means "All Permissions" uasge means no permissions, that is, creating users, but not granting permissions

Columns

(Optional) columns used by permissions. You can only set specific permissions for a column. If the command has more than one column, separate them with commas.

What

Permission usage level. The permission can be global, database, or specific table.

User

A user with the same name can be connected from different locations. The default value is "MySQL user password ".

Password (Optional). If you do not specify the identified by clause for the user, the password remains unchanged.

When identified by is used, the password string uses the literal meaning of the password, and grant will encode the password for you.

Note: Set Password uses the password () function
With grant option

Users can grant permissions to other users through the grant Statement (optional)

 

Example:

Grant all
On db_book. * To huaying@koowo.com identified
"Yeelion" can only be connected locally

Grant all
On db_book. * To huaying@vpn.koowo.com identified
By "yeeliong" allows connection from this domain

Grant all
On db_book. * To huaying @ % identified
By "yeelion" allows connection from any host

Note: The "%" character is used as a wildcard and matches the like pattern.

Grant all
On db_book. * To huaying @ % .koowo.com identified
"Yeelion ";

Allow huaying to be connected from any host in the koowo.com domain

Grant all
On db_book. * To huaying@192.168.1.189 identified
By "yeelion"

Grant all
On db_book. * To huaying@192.168.1. % identified
"Yeelion"

Grant all
On db_book. * To huaying@192.168.1.0/17
Identified by "yeelion"

Allow login from a single IP segment or a subnet IP Address

Note: Sometimes the user @ IP needs to use quotes such as "huaying@192.168.1.0/17"

Grant all
On *. * To huaying @ localhost
Identified by "yeelion" with grant option

You can add a superuser huaying to log on to the system to perform any operations.

Grant
Reload on *. * To huaying @ localhost identified
"Yeelion" only grants reload permission

Grant all
On db_book to huaying@koowo.com indetified
"Yeelion" All Permissions

Grant
Select on db_book to huaying @ %
Indetified by "yeelion" Read-Only permission

Grant
Select, insert, delete, update on db_book to huaying@koowo.com, indetified
"Yeelion"

Only select, insert, delete, and update Permissions

Grant
Select on db_book.storybook to huaying @ localhost indetified
"Yeelion" only tables

Grant
Update (name) on db_book.storybook to huaying @ localhost: the password of the table name column remains unchanged only.

Grant
Update (ID, name, author) on db_book.storybook to huaying @ localhost only multiple columns of the table

Grant all
On Book. * to "" @ koowo.com allow all users in the koowo.com domain to use the library book

 

Grant all
On Book. * To huaying @ % .koowo.com indetified
"Yeelion" with grant option

Allow huaying to authorize the Administrator of all tables in the library book.

 

2. revoke permissions and delete users

The revoke syntax is similar to the grant statement.

To is replaced by from, without the indetifed by and with grant option clauses:

Revoke
Privileges (columns) on what from user

User: it must match the user part of the user you want to revoke permission from the original grant statement.

Privileges: no matching is required. You can use the grant statement to grant permissions, and then use the revoke statement to revoke partial permissions.

The revoke statement only deletes permissions without deleting users. After all permissions are revoked, the user records in the User table are retained and the user can still connect to the server.

To completely delete a user, you must use a delete statement to explicitly delete user records from the user table:

Delete from user where user = "huaying"

Flush privileges; reload authorization table

Note: When the grant and revoke statements are used, the table is automatically reloaded, but when you directly modify the authorization table, it is not.