Basic knowledge of database user account management

MySQL administrators should know how to set up a MySQL user account by specifying which users can connect to the server, where to connect, and what to do when they connect. MYSQL3.22.11 introduces two more statements that make this work easier: The GRANT statement creates a MySQL user and assigns its permissions, REVOKE the statement delete permission. These two statements serve as the front end of the authorization table in the MySQL database and provide an alternative way to manipulate the contents of these tables directly. The GRANT and REVOKE statements affect the following four tables:

Authorization table Content
User can connect to the server and any global privileges they have
DB Database-level privileges
Tables _ Priv Table-level privileges
c o L um n s _ priv Column level privilege
There is also a fifth authorization table (host), but it is not affected by grant or revoke.

When you publish a grant statement for a user, you create an item in the user table for that person. If the statement specifies all global privileges (administrative permissions or permissions for all databases), those designations are also recorded in the user table. If you specify permissions for a database, table, or column, they are recorded in the DB, Tables_priv, and Columns_priv tables.
It is easier to use GRANT and REVOKE statements than to modify authorization tables directly. However, it is recommended that you supplement this chapter by reading chapter 12th, and the authorization table is discussed in detail in chapter 12th. These tables are important, as an administrator should understand how these tables work at the GRANT and revoke statement level.
The following sections of this section discuss how to set up MySQL users ' accounts and authorizations, also describe how to revoke permissions and remove all users from the authorization table, and consider a challenge that bothers many new MySQL administrators.
You will also consider using mysqlaccess and mysql_setpermission scripts, which are part of the MySQL distribution package. These are Perl scripts that provide a substitute for a grant statement that sets up a user account. Mysql_setpermission need to have a DBI support environment.

Create new users and authorizations

The syntax for the GRANT statement is as follows:

GRANT Privileges (columns)

On what

To user Identifiedby "password"

With GRANT OPTION

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

Privileges the permissions assigned to the user. The following table lists the permission specifiers that can be used in the GRANT statement:

Permission Descriptor permission actions allowed

The first set of permission descriptions shown in the table above Fu Yi for databases, tables, and columns. The second set of descriptors is administrative privilege. Typically, these permissions are granted fairly conservatively because they affect the operation of the server (for example, the SHUTDOWN privilege is not the right to distribute on a daily basis). The third set of descriptors is special. All means "All permissions" and usage means "No permissions"-that is, create a user without granting any permissions.

The column to which the columns permission applies. This is optional, just to set the permissions for the column to be exclusive. If you name more than one column, separate them with commas.

The level at which the what permission is applied. Permissions can be global (applicable to all databases and all tables), database-specific (applicable to all tables in a database), or table-specific. You can grant permissions to a specific column by specifying a c O L U-N s clause.
User who is using permissions. It consists of a user name and a host name. In MySQL, you specify not only who is connected, but where to connect. It allows you to have two users with the same name that are connected from different locations. MySQL allows you to differentiate between them and assign permissions to each other independently.

The user name for MySQL is the name you specify when you connect to the server. This name is not necessarily connected to your UNIX registration name or Windows name. By default, the client program uses your registered name as the user name for MySQL (if you do not specify a name), but this is only a convention. This is also the case with Root as the Superuser name that can manipulate everything from MySQL to a convention. You can also modify this name to nobody in the authorization table and then connect as a nobody user to perform an operation that requires superuser privileges.

Password the password assigned to the user. This is optional. If you do not specify a identifiedby clause for a new user, the user does not assign a password (unsafe). For an existing user, any specified password replaces the old password. If you do not specify a new password, the user's old password remains unchanged. When you are sure that you want to use the ID e N T i F i E DBY, the password string should be a direct amount, and GRANT will encode the password. Do not use the password () function when you use the Set PA s W O R D statement.

The WITH GRANT OPTION clause is optional. If this clause is included, the user can grant any of the permissions granted by the GRANT statement to another user. You can use this clause to grant the authorized ability to other users.

User names, passwords, and the names of databases and tables are case-sensitive in authorization table entries, and host and column names are not.

By querying some questions, you can usually infer the type of the required GRANT statement:
Who can connect, where to connect?
What level of permissions should the user have, and what do these permissions apply to?
Do you want to allow users to administer permissions?

Let's ask these questions and look at some examples of using the GRANT statement to set up a MySQL user account.

1. Who can connect, where to connect

You can allow users to connect to a specific host or to a group of hosts that are wide in scope. At an extreme, you can restrict access to a single host if you know that the user will connect only from that host:

GRANT all on samp_db.* to Boris@localhost Identfiedby "Ruby"

GRANT all on samp_db.* to fred@ares.mars.net identfiedby "Quartz"

(Symbol samp_db.* meaning "All tables in samp_db database") at the other extreme, you may have a user m A x who travels the world and needs to be able to connect from hosts around the world. In this case, wherever he connects you will allow:

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

The '% ' character Fu Qi wildcard function, the same meaning as like pattern matching, in the last statement, it means "any host." If you do not give the host name section at all, it will have the same meaning as the specified "%". Therefore, Max and max@% are equivalent. This is the easiest way to set up a user, but the security is minimal.

To compromise, you can allow users to connect in a limited set of hosts. For example, to make Mary Connect from any host in the snake.net domain, you can use the%.snake.net host descriptor:

GRANT all on samp_db.* to mary@%.snake.net identfiedby "Topaz"

The host part of the user identifier is available in the IP address instead of the host name (if you wish). You can specify a direct IP address or an address that contains a pattern character. Similarly, since MySQL3.23, you can specify an IP number with a netmask that indicates the number of bits used for that network number:

GRANT all on samp_db.* to joe@192.168.0.3 identifiedby "water"

GRANT all on samp_db.* to ardis@192.168.128.% Identifiedby "Snow"

GRANT all on samp_db.* to Rex@192.168.128.0/17 Identifiedby "ice"

The first statement indicates the specific host that the user can connect to. The second statement specifies the IP mode of the 129.168.128 Class C subnet. In the third statement, 192.168.128.0/17 specifies a 17-bit binary network number and matches the 192.168.128.0/17 in the first 17 bits of any host to its IP address.

If MySQL complains about the user value you specified, you might want to use quotes (but enclose the username and host name separately):

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

2. What level of permissions the user should have, and what these permissions apply to

You can grant different levels of permissions. Global permissions are the most powerful because they apply to any database. To make Ethel a superuser who can do everything, including authorization to other users, publish the following statement:

GRANT all on *.* to Ethel@localhost identifiedby "coffee"

With GRANT OPTION

The *.* specifier in the on clause means "all databases, all tables," and for the sake of insurance, we have specified that Ethel can only be connected from the local host. It is often advisable to restrict which hosts a superuser is connected to, because it restricts other users from testing the password.

Some permissions (F I L E, P R O C E S, RELOAD and S H U T D O W N) are administrative permissions that can only be granted with the no *.* global permission specifier. You can also grant these permissions without granting database-level permissions, if you wish. For example, the following statement establishes a flush user that does nothing else than publish a flush statement. This can be useful in administrative scripts because you need to perform actions such as refreshing the log during the log file cycle:

GRANT RELOAD on *.* to Flush@localhost identifiedby "Flushpass"

Usually granting administrative permissions should be conservative, because users with these permissions can affect the operation of the server.

Database-level permissions apply to all tables in a particular database. These permissions are granted using the on DB_NAME.* clause:

GRANT all on samp_db.* to Bill@racer.snake.net identifiedby ' rock '

GRANT SELECT on menagerie.* to ro_user@% identifiedby "Dirt"

The first statement grants all of Bill's permissions to any table in the samp_db database. The second statement creates a restricted access user R O User (read-only user), which can access all the tables in the Menagerie database, but only read them. In other words, the user can only publish SELECT statements.

How to specify a local host name in an authorization table entry

If you use the server's hostname instead of localhost, there is usually a problem connecting from that server host. This could be an error match between the method that specifies the name in the authorization table and the method that the name resolver routine (name Reslover routine) reports the name to the program. If the factorization reports an unqualified name (such as P I t-v i per), the authorization table contains an entry with a fully qualified name (for example, p i t-v I/s n a k E. N e T, and vice versa), an error match occurs.

To determine if this is happening in the system, try connecting to the local server with the-H option, which specifies the name of the host. Then view the server's regular log files. How does it report host names? Is it in an unqualified form or in a limited form? Whatever form it is, it tells you how you need to specify the host name portion of the user descriptor when you publish the GRANT statement.

You can list many individual 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 table, but do not allow the creation of a new table or delete a table, you can authorize the following:

GRANT select,insert,delete,update on samp_db.* to jennie@%

Identifiedby "Boron"

Access control for smaller granularity (f i n e-g r a i n e D) can be authorized on a single table, even on a single column of a table. Column-specific permissions are useful when there are tables to hide from users, or when only users are allowed to modify specific columns. It is assumed that there are some volunteers in the history Fellowship to help you with your duties as the Secretary of the Alliance. This is good news, but you decide to first grant the new assistant read-only permission to the member table (the table contains membership information), and then add the column-specific update permissions to the table's expiration column. In other words, your assistant can make changes when people update their membership

The first statement grants read access to the entire member table and sets the password. The second statement adds update permissions, but only to the Expiration column. It is not necessary to specify the password at this time, because it is already done in the first statement.
Due date work. Set this MySQL user's statement as follows:

If you want to grant columns exclusive permissions for multiple columns, you can specify a list of columns and separate them with commas. For example, you can use the following statements to increase the update permissions on the member table address columns for assistant users. The new permissions are added to all columns that already exist for the user:

GRANT UPDATE (street,city,state,zip) on Samp_db.member

To Assistant@localhost

In general, do not grant users more permissions than they actually need. However, when you want to enable users to create temporary tables that store intermediate results without allowing users to do so in a database that contains data that they cannot modify, there is a reason to grant a considerable amount of permission on the database. You can create a separate database (the author calls it T M P) and grant the user all permissions to the database. For example, if you want any user in the host of a mars.net domain to be able to use the TMP database, you can publish the following grant statement:

GRANT all on tmp.* to "" @%.mars.net

After this is done, users can create and reference tables in the TMP database using the name of the Tmp.tbl_name format ("" In the user descriptor to create an anonymous user entry that matches the blank user name).

3. Allow users to administer permissions?

You can allow database owners to control access to this database by granting all permissions to the database owner database and specifying with GRANT OPTION when you operate. For example, if you want Alicia to be able to connect to all hosts in the big.corp.com domain and manage permissions for all tables in the sales database, you should use the following grant statement:

In fact, the WITH Grant OPTION clause allows access rights to be granted to another user. You know, two users with grant permissions can grant each other their own permissions. If you grant a SELECT permission to only one user and grant Grant and other permissions to another user in addition to a select, the second user can make the first user "strong".

Canceling permissions and deleting users

To reclaim permissions for a user, you can use the REVOKE statement. In addition to replacing to and without the Identifiedby or with Grant OPTION clauses, the REVOKE syntax is very similar to the GRANT statement:

REVOKE Privileges (columns) on what from user

The user section must match the user portion of the original grant statement of the person to whom you want to revoke permissions. Priv I l e g e s part does not need to match, you can authorize with GRANT statement, and then cancel a part of it with revoke statement. The REVOKE statement deletes only the permissions and does not delete the user. The user's entries remain in the user table, even if you cancel all of the permissions for that user. This means that the user can still connect to the server. To remove an entire user, you must delete the user's record directly from the users table with the DELETE statement:

The DELETE statement deletes the user's entry, and the FLUSH statement tells the server to reload the authorization table (the tables are automatically reloaded when the grant or REVOKE statement is used instead of directly modifying the authorization table).

A privilege challenge, part I

The following is a recurring occurrence in the MySQL mailing list: A new MySQL administrator adds an entry to a user, uses the hostname section, which is specified in a pattern. For example:

GRANT all on samp_db.* to fred@%.snake.net identifiedby "cocoa"

The intention here is to allow the user, Fred, to connect from all hosts in the snake.net domain and have all permissions on the samp_db database. In fact, Fred is able to connect from those hosts (except the server host itself). When Fred tried to connect from the server host, the attempt failed with a "access denied" message. This is true even if the user specifies the correct password.

This can also occur if the authorization table contains the default entries installed by the mysql_install_db installation script. The reason for this is that when the server validates an attempt to connect Fred, an anonymous user entry (Anonymous-user entry) takes precedence over the Fred item. The anonymous user entry requires the user to connect without a password, and a password error match occurs.

Another background to this issue will be given in chapter 12th, "Permissions challenges, Part two". Currently, suffice it to say that the only way to fix this problem is to remove the anonymous user entry from the user table, and we can't use R e V O K because the command only deletes permissions. To get rid of these anonymous entries completely, do the following:

% mysql-uroot MySQL mysql> DELETE from user where user= "";

Mysql>flush privileges;

Now, when Fred tries to connect from the local host, it succeeds.

<