MySQL User management

mysql| user Management MySQL administrators should know how to set up a MySQL user account, which user can connect to the server, where to connect, what to do after the connection. MySQL 3.22.11 started introducing two statements to make this work easier: The GRANT statement creates the MySQL user and assigns its permissions, while the REVOKE statement deletes the permissions. The two statements act as the front-end role of the MySQL database and provide a different approach to the contents of the tables directly manipulated. Create and REVOKE statements affect 4 tables: Authorization table


Content


Users who can connect to the server and any global permissions they have


DB database-level permissions


Tables_priv table-level permissions


Columns_priv column-level permissions


There is also a 5th authorization table (host), but it is not affected by grant and revoke.


When you issue a grant statement to a user, create a record in the user table for that person. If the statement specifies any global permissions (administrative permissions or permissions that apply to all databases), these are also recorded in the user table. If you specify database, table, and column-level permissions, they are recorded separately in DB, Tables_priv, and Columns_priv tables.


It is easier to use grant and revoke than to modify the authorization form directly, however, it is recommended that you read the MySQL security Guide. These tables are extremely important, and as an administrator, you should understand how they go beyond the functional level of the grant and REVOKE statements.


In the following sections, we will describe how to set up a MySQL user account and authorize it. We also deal with how to withdraw and remove users from the authorization table.


You may also want to consider using mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution, which are Perl scripts that provide another option for grant statements to set up user accounts. Mysql_setpermission need to install DBI support.


1 Create a user and authorize


The syntax of the GRANT statement looks like this:


Grant privileges (columns) on what to user identified by ' password ' with Grant OPTION


To use this statement, you need to fill in the following sections:


Privileges


Permissions granted to the user, the following table lists the permission specifiers available for the GRANT statement:


Permission specifier


Actions allowed by permission


Alter modify table and index


Create creating databases and tables


Delete Deletes an existing record in a table


Drop Discard (delete) database and table


index Create or Discard indexes


Insert inserts a new row into the table


REFERENCE not used


SELECT to retrieve records from a table


Update Modify existing table record


File read or write files on the server


PROCESS to view the thread information executed in the server or kill the thread


RELOAD overload authorization table or empty log, host cache, or table cache.


SHUTDOWN Shutdown Server


All privileges synonyms


USAGE Special "no permission" permission


The table above shows the permissions specified in the first set of Fu Yi for databases, tables, and columns, and the second set of administrative permissions. Generally, these are relatively strictly authorized because they allow the user to affect the operation of the server. The third set of privileges is special, all means "All permissions", Uasge means no permissions, that is, create the user, but do not grant permissions.


Columns


Permission to use the column, which is optional, and you can only set the column-specific permissions. If the command has more than one column, you should separate them with commas.


What


The level at which permissions are applied. Permissions can be global (applicable to all databases and all tables), a specific database (for all tables in a database), or a specific table. You can specify a columns clause by specifying that the permission is column specific.


User


Permission granted to a user, which consists of a user name and a host name. In MySQL, you not only specify who can connect, but also where to connect. This allows you to have two users with the same name connected from different places. MySQL lets you differentiate them and give each other independent permission.


A username in MySQL is the user name you specify when you connect to the server, which does not have to be associated with your UNIX login or Windows name. By default, if you do not specify a name explicitly, the client program will use your login name as the MySQL user name. It's just a pact. You can change the name to nobody in the authorization table and then perform an operation that requires superuser privileges with a nobody connection.


Password


The password given to the user, which is optional. If you do not specify a identified by clause for a new user, the user is not assigned a password (unsafe). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains the same, and when you use identified by, the password string uses the literal meaning of the password, grant will encode the password for you, and do not use the PASSWORD () function as you did with set PASSWORD.


The WITH GRANT option clause is optional. If you include it, users can grant permissions to other users through grant statements. You can use this clause to give other users the ability to authorize them.


User names, passwords, databases, and table names are case-sensitive in authorization table records, and host and column names are not.

Generally, you can identify the type of grant statement by asking a few simple questions:


Who can connect and connect from there?


What level of permissions should users have and what do they apply to?


Should the user allow administrative permissions?


Some examples are discussed below.


1.1 Who can connect and connect from there?


You can allow a user to connect from a specific or a range of hosts. There's an extreme if you know the demotion from a host connection, you can limit the permissions to a single host:


GRANT all on samp_db.* to Boris@localhost identified by "Ruby" Grant all on samp_db.* to fred@res.mars.com identified by "Q Uartz "


(samp_db.* means "All tables of the SAMP_DB database") The other extreme is that you may have a user Max who travels frequently and needs to be able to connect with hosts from all over the world. In this case, you can allow him to connect from wherever:


GRANT all on samp_db.* to max@% identified by "Diamond"


The "%" character Fu Qi wildcard action, the same meaning as like pattern matching. In the above statement, it means "any host". So Max and max@% are equivalent. This is the easiest way to build a user, but it is also the least secure.


To take it, you can allow a user to access from a restricted set of hosts. For example, to allow Mary to connect from any host in the snake.net domain, use a%.snake.net host specifier:


GRANT all on samp_db.* to mary@.snake.net identified by "quartz";


If you like, the host part of the user identifier can be given with an IP address instead of a host name. You can specify an IP address or an address that contains a pattern character, and, from MySQL 3.23, you can also specify the IP number of the netmask that indicates the number of digits used for the network number:


GRANT all on samp_db.* to boris@192.168.128.3 identified by ' Ruby ' GRANT all ' on samp_db.* to fred@192.168.128.% identified By ' quartz ' GRANT all on samp_db.* to REX@192.168.128.0/17 identified by ' Ruby '


The first example indicates the specific host from which the user is connected, the second specifies the IP mode for the C-class subnet 192.168.128, and in the third statement, 192.168.128.0/17 specifies a 17-bit network number and matches an IP address with 192.168.128 first 17 digits.


If MySQL complains about the user value you specify, you may need to use quotes (only the user name and the hostname part are quoted separately).


GRANT all on samp_db.president to "my Friend" @ "Boa.snake.net"


1.2 What level of permissions should users have and what should they apply to?


You can authorize different levels of permissions, and global permissions are the most powerful because they apply to any database. To make Ethel a superuser who can do anything, including the ability to authorize other users, issue the following statement:


Grant all on *.* to ethel@localhost identified by ' Coffee ' with Grant OPTION


The *.* in the ON clause means "all databases, all tables." From security considerations, we specify that Ethel can only be connected locally. Restricting a host that a superuser can connect to is usually sensible because it restricts the host trying to crack the password.


Some permissions (FILE, PROCESS, Reload, and shutdown) are administrative permissions and can only be authorized with the "on *.*" global permissions specifier. If you wish, you can authorize these permissions without authorizing the database permissions. For example, the following statement sets a flush user who can only issue flush statements. This can be useful in administrative scripts that you need to perform such as emptying the log:


GRANT RELOAD on *.* to Flushl@localhost identified by "Flushpass"


Generally, you want to authorize administrative privileges, be stingy, because the user who owns them can affect the operation of your server.


Database-level permissions apply to all tables in a particular database, and they can be granted by using the on db_name.* clause:


GRANT all on samp_db to Bill@racer.snake.net indetified by ' Rock ' GRANT SELECT on samp_db to ro_user@% indetified by ' Rock "


The first statement authorizes bill to samp_db the permissions of all tables in the database, the second creates a strictly restricted access user Ro_user (read-only user), only accesses all tables in the SAMP_DB database, but only reads, that is, the user can only issue a SELECT statement.


You can list a series of permissions that are granted at the same time. For example, if you want users to be able to read and modify the contents of an existing database, but cannot create a new table or delete a table, grant these permissions as follows:


GRANT select,insert,delete,update on samp_db to Bill@snake.net indetified by ' Rock '


For finer access control, you can authorize on individual tables, or even on each column of a table. Column specific permissions are useful when you want to hide the part of a table from the user, or if you want a user to be able to modify only specific columns. Such as:


Grant SELECT on Samp_db.member to Bill@localhost indetified by ' Rock ' grant UPDATE (expiration) on samp_db. Member to Bill@localhost


The first statement grants read access to the entire member table and sets a password, and the second statement adds update permissions when it is only for expiration columns. There is no need to specify the password again, because the first statement has already been specified.


If you want to grant permissions to multiple columns, specify a comma-separated list. For example, for assistant users to increase the update permissions on the Address field of the member table, use the following statement, and the new permissions will be added to the user's existing permissions:


GRANT UPDATE (Street,city,state,zip) on samp_db to Assistant@localhost


Typically, you do not want to grant any permission that is wider than what the user really needs. However, when you want users to be able to create a temporary table to save intermediate results, but you don't want them to do so in a database that contains things they should not modify, a relatively loose permission to grant to a database occurs. You can do this by creating a separate database (such as TMP) and granting all the permissions on the open database. For example, if you want to have any user from a host in the mars.net domain use the TMP database, you can issue a grant statement like this:


GRANT all on tmp.* to "" @mars. Net


After you have finished, the user can create and refer to the table in TMP (in the user specifier, create an anonymous user, and any user will match the blank user name) in tmp.tbl_name form.


1.3 Should the user be allowed to administer permissions?


You can allow the owner of a database to control access to the database by granting all owner permissions on the database, specifying with Grant OPTION when authorized. For example, if you want Alicia to be able to connect from any host in the big.corp.com domain and have administrator privileges on all tables in the sales database, you can use the following grant statement:


Grant all on sales.* to alicia@%.big.corp.com indetified by ' Applejuice ' with Grant OPTION


On the effect with the GRANT OPTION clause allows you to grant access to another user. Be aware that two users with grant permissions can authorize each other. If you give only the first user select permission, and another user has grant plus select, the second user can be the first user more "powerful."


2 Withdraw and delete user


To cancel the permissions of one user, use the REVOKE statement. The syntax of the revoke is very similar to the grant statement, except to replace with and without the indetifed by and with GRANT option clauses:


REVOKE Privileges (columns) on what from user


The user section must match the users portion of the person you want to withdraw from the original grant statement. The privileges section does not need to match, you can authorize it with the grant statement, and then use the REVOKE statement to revoke only some of the permissions.


The REVOKE statement deletes only the permissions and does not delete the user. Even if you revoke all permissions, the user record in the users table remains, which means that the user can still connect to the server. To completely remove a user, you must explicitly delete the user record from the users table with a DELETE statement:


%mysql-u root mysqlmysql>delete from user->where user= "user_name" and host= "host_name"; Mysql>flush PRIVILEGES ;


The DELETE statement deletes the user record, and the flush statement tells the server to overload the authorization table. (When you use the GRANT and REVOKE statements, the table is overloaded automatically, and you do not modify the authorization form directly.) ）