Access permission for MySQL application tips

The username used by MySQL for authentication purposes, regardless of the Unix username (login name) or Windows username. By default, most MySQL users attempt to log on using the current Unix user name as the MySQL user name, but this is only for convenience. The client program allows the use of the-u or -- user option to specify a different name. And security considerations, all MySQL usernames should have passwords.

1. MySQL user name and password

* The username used by MySQL for authentication is irrelevant to the Unix username (login name) or Windows username. By default, most MySQL users attempt to log on using the current Unix user name as the MySQL user name, but this is only for convenience. The client program allows the use of the-u or -- user option to specify a different name. And security considerations, all MySQL usernames should have passwords.

* The MySQL user name can be up to 16 characters. Typically, the Unix user name must be 8 characters long.

* The MySQL password has nothing to do with the Unix Password.

* The MySQL encryption PASSWORD uses different algorithms used during Unix logon, including PASSWORD () and ENCRYPT ()

Function PASSWORD (str)

Calculate a password string from the plain text password str. This function is used to encrypt the MySQL Password to store the Password in the Password column of the user authorization table.

Mysql> select PASSWORD (badpwd );

-> 7f84554057dd964b

PASSWORD () encryption is non-reversible. PASSWORD () is not encrypted in the same way as the Unix PASSWORD. You should not assume that if your Unix PASSWORD is the same as your MySQL PASSWORD, PASSWORD () will lead to the same encryption value as that stored in the Unix PASSWORD File. See ENCRYPT ().

ENCRYPT (str [, salt])

Use the Unix crypt () system to call encrypted str. The salt parameter should be a string with 2 characters. (In MySQL 3.22.16, salt can be longer than 2 characters .)

Mysql> select ENCRYPT ("hello ");

-> VxuFAJXVARROc

If crypt () is not available on your system, ENCRYPT () always returns NULL. ENCRYPT () retains only the first 8 characters of str and ignores all other characters, at least on some systems. This is determined by the behavior of the underlying crypt () System Call.

1. Connect to the MySQL server

Syntax format:

Shell> mysql [-h host_name] [-u user_name] [-pyour_pass]

-H,-u, and-p options are in the form of -- host = host_name, -- user = user_name, and -- password = your_pass.

Note: There is no space between-p or -- password = and the password following it. (It is insecure to specify a password on the command line !)

Mysql uses the default value for connection parameters not available in the command line:

* The default host name is localhost.

* The default user name is your Unix login name.

* If no-p exists, no password is provided.

Specify the default value:

Specify the connection parameters in the [client] section of the configuration file ". my. cnf" in your home directory:

[Client]

Host = host_name

User = user_name

Password = your_pass

Note: the value specified on the command line takes precedence over the value specified in the configuration file and environment variables.

The safest way is to ask the customer program to prompt a password or specify a password in a properly protected ". my. cnf" file.

1. Permissions provided by MySQL

Permission column Context

Select Select_priv table

Insert Insert_priv table

Update Update_priv table

Delete Delete_priv table

Index Index_priv table

Alter Alter_priv table

Create Create_priv database, table, or index

Drop Drop_priv database or table

Grant Grant_priv database or table

References References_priv database or table

Reload Reload_priv Server Management

Shutdown Shutdown_priv Server Management

Process Process_priv Server Management

File File_priv file access on the server

Note: grant permissions allow you to grant your own permissions to other users.

You can use the load data infile and SELECT... the into outfile statement reads and writes files on the server. Any user authorized to this permission can read or write any files that the MySQL server can read or write.

2. Access Control: Connection Validation

Identity check uses three (Host, user, and Password) range fields in the User table. The server accepts the connection only when a user table entry matches your host name and user name and you provide the correct password.

Note: A Host value can be a Host name or an IP number, or localhost indicates the local Host. The wildcard characters "%" and "_" can be used in the Host field. The Host Value % matches any Host name. When a connection is attempted, the server browses the sorted entries and uses the first matching.

The general misunderstanding is that, for a given user name, when the server tries to find a match for the connection, all entries that explicitly name the user will be used first. This is obviously not a fact.

3. Access Control: Request confirmation

Once you establish a connection, the server enters Stage 2. For each request sent over this connection, the server checks whether you have sufficient permissions to execute it and authorizes the table to operate with the GRANT and REVOKE commands.

GRANT priv_type [(column_list)] [, priv_type [(column_list)]...]

ON {tbl_name | *. * | db_name .*}

TO user_name [identified by password]

[, User_name [identified by password]...]

[With grant option]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]...]

ON {tbl_name | *. * | db_name .*}

FROM user_name [, user_name...]

GRANT is implemented in MySQL 3.22.11 or later. For earlier MySQL versions, the GRANT statement does not do anything.

The GRANT and REVOKE commands allow the System Supervisor to GRANT and REVOKE permissions granted to MySQL users at four levels of permissions:

Global

Global permissions apply to all databases on a given server. These permissions are stored in the mysql. user table.

Database level

The database Permission applies to all tables of a given database. These permissions are stored in the mysql. db and mysql. host tables.

Table Level

The table Permission applies to all columns of a given table. These permissions are stored in the mysql. tables_priv table.

Column level

The column Permission applies to a single column in a given table. These permissions are stored in the mysql. columns_priv table.

The user table permission is the Super user permission. It is wise to grant only the permissions of the user table to a Super user, such as a server or database supervisor. For other users, you should set the permissions in the user table to N and grant permissions only on the basis of a specific database to use the db and host tables.

4. When does the permission change take effect?

When mysqld is started, all authorization table content is read into the memory and takes effect from that point.

Modifications made to the authorization table using GRANT, REVOKE, or set password will be immediately noticed by the server.

If you manually modify the authorization table (using INSERT, UPDATE, and so on), you should execute a flush privileges statement or run mysqladmin flush-privileges to tell the server to load the authorization table again, otherwise, your change will not take effect unless you restart the server.

5. Create initial MySQL Permissions

After installing MySQL, you can run scripts/mysql_install_db to install the initial access permission. Contains the following permission sets:

* The MySQL root User is a super user who can do anything. The connection must be sent by the local host. Note: The generated root password is empty, so anyone can connect with root without a password and be granted all permissions.

* An anonymous user can perform any period of action on a database named test _ or a database named test _. The connection must be sent by the local host. This means that any local user can connect and is considered an anonymous user.

* Other permissions are denied. For example, you cannot use mysqladmin shutdown or mysqladmin processlist.

Specify a PASSWORD for the MySQL root User (note that you use the PASSWORD () function to specify the PASSWORD ):

Shell> mysql-u root mysql

Mysql> UPDATE user SET Password = PASSWORD (new_password)

WHERE user = root;

Mysql> flush privileges;

In MySQL 3.22 and later versions, you can use the set password statement:

Shell> mysql-u root mysql

Mysql> set password for root = PASSWORD (new_password );

Another way to set a password is to use the mysqladmin command:

Shell> mysqladmin-u root password new_password

Check the scripts/mysql_install_db script to see how to install the default permissions. You can use it as a basis for research on how to increase other users

To completely recreate the permission table, delete all "*. frm", "*. MYI", and "*. MYD" files under the directory containing the mysql database. (This is the directory named "mysql" under the database directory. It is listed when you run mysqld -- help .) Then run the mysql_install_db script. You may edit the script after you have the required permissions.

1. Add new user permissions to MySQL

Add two different methods to the user:

Use the GRANT statement or directly operate the MySQL authorization table.

The better way is to use the GRANT statement, because they are more concise and seem to have fewer errors.

Shell> mysql -- user = root mysql

Mysql> grant all privileges on *. * TO monty @ localhost

Identified by something with grant option;

Mysql> grant all privileges on *. * TO monty @ "%"

Identified by something with grant option;

Mysql> grant reload, process on *. * TO admin @ localhost;

Mysql> grant usage on *. *