Shulou(Shulou.com)06/01 Report--

The MySQL administrator should know how to set up the MySQL user account and indicate which user can connect to the server, where to connect, and what to do after connecting. MySQL 3.22.11 began by introducing two statements to make this easier: the GRANT statement creates the MySQL user and specifies its permissions, and the REVOKE statement removes the permissions. The two statements play the front-end role of the mysql database and provide another way to directly manipulate the contents of these tables. The CREATE and REVOKE statements affect four tables:

Authorization form content

Users who user 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 fifth authorization table (host), but it is not affected by GRANT and REVOKE.

When you issue a GRANT statement to a user, create a record for that user in the user table. 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 in the db, tables_priv, and columns_ private tables, respectively.

It is easier to use GRANT and REVOKE than to modify the authorization table 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 GRANT and REVOKE statements.

In the following sections, we will describe how to set up a MySQL user account and authorize it. We also cover how to revoke rights 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 requires DBI support to be installed.

1 create a user and authorize it

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 that can be used for GRANT statements:

Permission specifier

Actions allowed by permissions

ALTER modifies tables and indexes

CREATE creates databases and tables

DELETE deletes existing records from the table

DROP discards (deletes) databases and tables

INDEX creates or discards indexes

INSERT inserts a new row into the table

REFERENCE not used

Records in the SELECT search table

UPDATE modifies existing table records

FILE reads or writes files on the server

PROCESS views the thread information executed on the server or kills the thread

RELOAD reloads the authorization table or empties the log, host cache, or table cache.

SHUTDOWN shuts down the server

Owned by ALL; synonymous with ALL PRIVILEGES.

USAGE's special "No permission" permission

The permission specifiers shown in the first group in the above table apply to databases, tables and columns, and the second group manages permissions. In general, these are relatively strictly authorized because they allow users to influence the operation of the server. The third set of permissions is special, ALL means "all permissions" and UASGE means no permissions, that is, users are created but no permissions are granted.

Columns

The column in which permissions are applied, which is optional, and you can only set column-specific permissions. If the command has more than one column, you should separate them with a comma.

What

The level at which permissions are used. Permissions can be global (applicable to all databases and all tables), specific databases (applicable to all tables in a database), or specific tables. You can specify that a columns statement is column-specific.

User

The user to whom permission is granted, which consists of a user name and a host name. In MySQL, you specify not only who can connect, but also where to connect. This allows you to connect two users with the same name from different places. MySQL lets you distinguish between them and grant permissions independently of each other.

A user name in MySQL is the user name you specify when you connect to the server, and this name 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 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 use the nobody connection to perform operations that require superuser privileges.

Password

The password given to the user, it is optional. If you do not specify an IDENTIFIED BY clause for a new user, the user is not assigned a password (insecure). 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, when you use IDENTIFIED BY, the password string uses the literal meaning of the password, GRANT will code the password for you, do not use the password () function as you use SET PASSWORD.

The WITH GRANT OPTION clause is optional. If you include it, the user can grant permissions to other users through the GRANT statement. You can use this clause to authorize other users.

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

In general, you can identify the types of GRANT statements by asking a few simple questions:

Who can connect, from there?

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

Should users be allowed administrative privileges?

Here are some examples.

1.1 who can connect and connect from there?

You can allow a user to connect from a specific host or a series of hosts. At one extreme, if you know that you are demoted from a host connection, you can limit 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 "quartz"

(samp_db.* means "all tables in the samp_db database) at the other extreme, you may have a user max who travels a lot and needs to be able to connect from hosts around the world. In this case, you can allow him to connect from anywhere:

GRANT ALL ON samp_db.* TO max@% IDENTIFIED BY "diamond"

The'% 'character acts as a wildcard and has 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 users, but it is also the least secure.

Among them, you can allow a user to access from a restricted host collection. 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 prefer, the host portion 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 pattern characters, and from MySQL 3.23, you can also specify the IP number with a netmask that indicates the number of bits 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 to which the user can connect, the second specifies the IP mode for the Class C subnet 192.168.128, while in the third statement, 192.168.128.0 Universe 17 specifies a 17-bit network number and matches the IP address with 192.168.128 first 17 bits.

If MySQL complains about the user value you specify, you may need to use quotation marks (only separate quotation marks for the user name and host name).

GRANT ALL ON samp_db.president TO "my friend" @ "boa.snake.net"

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

You can grant 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 being able 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". For security reasons, we specify that ethel can only connect locally. It is usually wise to limit the hosts to which a superuser can connect, because it limits hosts that try to crack passwords.

Some permissions (FILE, PROCESS, RELOAD, and SHUTDOWN) are administrative rights and can only be authorized with the "ON *. *" global permission specifier. If you prefer, you can grant these permissions instead of database permissions. For example, the following statement sets up a flush user who can only issue flush statements. This may be useful when you need to execute administrative scripts such as emptying logs:

GRANT RELOAD ON *. * TO flushl@localhost IDENTIFIED BY "flushpass"

In general, you want to authorize administrative rights and be stingy, because the users who own them can affect the operation of your server.

Database-level permissions apply to all tables in a particular database and 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 all tables in the samp_db database, and the second creates a user ro_user (read-only user) with severely restricted access, which can only access all tables in the samp_db database, but only read, that is, the user can only issue SELECT statements.

You can list a series of permissions 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 new tables or delete tables, grant these permissions as follows:

GRANT SELECT,INSERT,DELETE,UPDATE ON samp_db TO bill@snake.net INDETIFIED BY "rock"

For more sophisticated access control, you can authorize it on each table, or even on each column of the table. Column-specific permissions are useful when you want to hide parts of a table from a user, or when you want a user 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 permission when only for the expiration column. There is no need to specify a password because the first statement has already been specified.

If you want to grant permissions to multiple columns, specify a list separated by commas. For example, to add UPDATE permissions to the address field of the member table for assistant users, use the following statement, and the new permissions will be added to the existing permissions of the user:

GRANT UPDATE (street,city,state,zip) ON samp_db TO assistant@localhost

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

GRANT ALL ON tmp.* TO "" @ mars.net

After you have done this, the user can create and reference the table in tmp in tmp.tbl_name form (create an anonymous user in the "" in the user specifier, any user matches a blank user name).

1.3 should users be allowed to manage permissions?

You can allow the owner of a database to control access to the database by granting permissions to all owners on the database, specifying WITH GRANT OPTION when authorizing. For example, if you want alicia to connect from any host in the big.corp.com domain and have administrator privileges for 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

In effect, the WITH GRANT OPTION clause allows you to grant access authorization to another user. Note that two users with GRANT privileges can authorize each other. If you only give the first user SELECT permission, and the other user has GRANT plus SELECT permission, then the second user can be more "powerful" than the first user.

2 remove power and delete user

To revoke a user's privileges, use the revoke statement. The syntax of REVOKE is very similar to the GRANT statement, except that TO is replaced by FROM and there are no INDETIFED BY and WITH GRANT OPTION clauses:

REVOKE privileges (columns) ON what FROM user

The user part must match the user part of the original GRANT statement for the user you want to revoke. The privileges part does not need to match, you can use the Grant statement to authorize, and then use the revoke statement to revoke only part of the permission.

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

% mysql-u root mysql

Mysql > DELETE FROM user

-> WHERE User= "user_name" and Host= "host_name"

Mysql > FLUSH PRIVILEGES;

The DELETE statement deletes the user record, while the FLUSH statement tells the server to reload the authorization table. When you use GRANT and REVOKE statements, the table is automatically overloaded, but not when you modify the authorization table directly. ) [@ more@]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.