MySQL Database administration user Rights

http://blog.itpub.net/7607759/viewspace-675079/

2.2 Granting permissions

With the grant command mentioned earlier, Grant's syntax looks quite complicated:

GRANT

Priv_type [(column_list)]

[, Priv_type [(column_list)] ...

On [object_type] Priv_level

to user [identified by [PASSWORD] ' PASSWORD ']

[, user [identified by [PASSWORD] ' PASSWORD '] ...

[REQUIRE {NONE | ssl_option [[and] ssl_option] ...}]

[With With_option ...]

A few of the bolded subkeys are described below:

L Object_type:

TABLE

| FUNCTION

| PROCEDURE

L Priv_level:

*

| *.*

| Db_name.*

| Db_name.tbl_name

| Tbl_name

| Db_name.routine_name

L Ssl_option:

Ssl

| X509

| CIPHER ' CIPHER '

| ISSUER ' ISSUER '

| SUBJECT ' SUBJECT '

L With_option:

GRANT OPTION

| Max_queries_per_hour Count

| Max_updates_per_hour Count

| Max_connections_per_hour Count

  |

max_user_connections Count

Priv_type is the simplest, but with the most options available, the official documentation specifically lists the tables very clearly:

Permission types	Brief description
all [privileges]	Grant all privileges at specified access level except Grant OPTION
Alter	Enable Use of ALTER TABLE
ALTER ROUTINE	Enable stored routines to be altered or dropped
CREATE	Enable Database and table creation
CREATE ROUTINE	Enable Stored routine creation
CREATE Temporary TABLES	Enable use of CREATE temporary TABLE
CREATE USER	Enable use of the CREATE user, DROP user, RENAME user, and REVOKE all privileges
CREATE VIEW	Enable created or altered
DELETE	Enable Use of DELETE
DROP	Enable databases, tables, and views to be dropped
EVENT	Enable use of events for the Event Scheduler
EXECUTE	Enable the user to execute stored routines
FILE	Enable the user to cause the server to read or write files
GRANT OPTION	Enable privileges to is granted to or removed from other accounts
INDEX	Enable indexes to be created or dropped
INSERT	Enable Use of INSERT
LOCK TABLES	Enable use of LOCK TABLES on TABLES for which you have the SELECT privilege
PROCESS	Enable the user to see all processes with SHOW processlist
REFERENCES	Not implemented
RELOAD	Enable use of FLUSH operations
REPLICATION CLIENT	Enable the user to ask where master or slave servers is
REPLICATION SLAVE	Enable replication Slaves to read binary log events from the Master
SELECT	Enable Use of SELECT
SHOW DATABASES	Enable show DATABASES to show all DATABASES
SHOW VIEW	Enable use of SHOW CREATE VIEW
SHUTDOWN	Enable use of mysqladmin shutdown
SUPER	Enable use of other adminstrative operations such as change MASTER to, KILL, PURGE BINARY LOGS, SET GLOBAL, and Mysqladmin Debug command
TRIGGER	Enable triggers to be created or dropped
UPDATE	Enable Use of UPDATE
USAGE	Synonym for "no privileges

Brackets, permission description of the English grammar is very simple, I steal a lazy, do not translate into Chinese.

The above paragraphs plus a block is basically the syntax of the GRANT statement, it seems to be a little more complex, but do not understand it is OK, and even if you understand it is not necessarily remember to live, as everyone knows to first thank the country, but everyone also understand but say it ( However, millions of naked officials in overseas relatives said this should be true, the key is to get serious, you have to use it, think twice to get a few more examples, let the big guys understand this thing.

Tips:

It is also a reliable way to modify a dictionary table by insert,update without using the GRANT statement!

Focus on a few options for with_option:

• Grant OPTION: Allows the user to grant this permission to another user;
• Max_queries_per_hour: The number of query statements allowed to be executed per hour by the user;
• Max_updates_per_hour: The number of update statements allowed to be performed by the user per hour;
• Max_connections_per_hour: The number of times a user is allowed to connect per hour;
• Max_user_connections: Allows the user to simultaneously connect the number of servers;

This piece of content a look is to set limits for the user to make, I personally think the meaning is not big, but a simple understanding is also necessary, in case one day on a user looked uncomfortable, the DBA should understand, or there is a way to limit the resources that users can use.

The other part of the first said, not to mention that the matter can not be said too fine, the main thing is too small to think and understand, pretend this thing I have a thick skin, but do this kind of thing when the expression is always not natural, but please be assured that the children's shoes, I will continue to strive for the success of the early success, With my sincerity to the others, blindfolded all the people ~ ~ ~

2.3 Revoke Permissions

To reclaim user rights, the corresponding command is revoke, and its syntax is defined in two ways:

• REVOKE Priv_type [(column_list)] [, Priv_type [(column_list)]] ... On [object_type] priv_level from user [, user] ...
• REVOKE all privileges, GRANT OPTION from user [, user] ...

The former is used to handle the specified permission, which removes all permissions. The clause definition of the REVOKE syntax is exactly the same as the clause definition in grant, which is not repeated here.

If you want to determine what permissions the user currently has, you can use the show grants statement, for example, to see what permissions the user [email protected] has, and execute the following statement:

Mysql> Show grants for [email protected];

+-------------------------------------------------------------------------------------------------------------- ----+

| Grants for [email protected] |

+-------------------------------------------------------------------------------------------------------------- ----+

| GRANT USAGE on *. jss_grant ' @ ' 10.0.0.99 ' identified by PASSWORD ' *284578888014774cc4ef4c5c292f694cedbb5457 ' |

| GRANT SELECT on ' MySQL '. ' User ' to ' jss_grant ' @ ' 10.0.0.99 ' |

+-------------------------------------------------------------------------------------------------------------- ----+

2 rows in Set (0.00 sec)

From the results returned above can be seen, the user [email protected] has two permissions, Query Mysql.user table query, and login MySQL database permissions. As long as the general user creates, will have the usage on the *. * permission, the permission cannot be retracted through the REVOKE statement, remember.

Try to retract the Jss_grant user's SELECT permission to Mysql.user, as follows:

Mysql> Revoke select on Mysql.user from [email protected];

Query OK, 0 rows Affected (0.00 sec)

2.4 Deleting a user

Removing the user's syntax is straightforward:

DROP User User [, user]
...

Syntactically it's a little bit different from Oracle, which I feel it's necessary to mention, for example, a deleted user (or other object such as a tablespace) in Oracle, if the user is under a different object, then the default delete operation fails, and Oracle prompts to clean up the object under that user first. If forced to delete, it provides a cascade clause that is no longer prompted after attaching the clause, even if there is an associated object that is automatically deleted.

MySQL's delete user syntax does not exist in the CASCADE clause, why does not exist, not MySQL to protect the data is not as good as Oracle, but because the most important one with the different mechanism of Oracle decision, the MySQL database is not dependent on the object to save the user , but relies on the library (DB), the user is deleted without any relationship, the object is still in its stored database, so the user in the MySQL database is deleted and deleted, if the external application does not use the user, then we can assume that the user is deleted without impact. Even if the discovery is really deleted wrong, the user has long been determined to dedicate his life to the sickle axe help, and the style is perfect, the party and the country unlimited loyalty, party let bite who bite who. If you want to restore your identity, it is also very simple, this is not an organizational sentence, as long as re-insert the record to the Mysql.user table (registration), and grant the required permissions (grant rank), as for the underlying data views that is completely negligible.

Tips:

The
DROP user does not automatically abort connected users ' sessions, that is, if the deleted user has already connected to the server before the deletion and has not yet been interrupted, it can continue to perform certain operations at this time, but its identity has become heihu.

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

MySQL Database administration user Rights