Shulou(Shulou.com)05/31 Report--

This article mainly explains "the application skills of MySQL access". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the application skills of MySQL access.

MySQL is a user name used for authentication purposes, regardless of the Unix user name (login name) or Windows user name. By default, most MySQL customers try to log in with the current Unix user name as the MySQL user name, but just for convenience, the client allows you to specify a different name with the-u or-- user option. And for security reasons, all MySQL usernames should have passwords.

1. MySQL username and password

* MySQL uses the user name for authentication purposes, regardless of the Unix user name (login name) or Windows user name. By default, most MySQL customers try to log in with the current Unix user name as the MySQL user name, but just for convenience, the client allows you to specify a different name with the-u or-- user option. And for security reasons, all MySQL usernames should have passwords.

* the MySQL user name can be up to 16 characters long; typically, the Unix user name is limited to 8 characters.

* the MySQL password is not related to the Unix password.

* the MySQL password uses a different algorithm used during Unix login, PASSWORD () and ENCRYPT ()

Function PASSWORD (str)

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

Mysql > select PASSWORD (badpwd)

-> 7f84554057dd964b

PASSWORD () encryption is non-reversible. PASSWORD () does not perform password encryption in the same way as Unix password encryption. You should not assume that if your Unix password is the same as your MySQL password, PASSWORD () will result in the same encryption value as that stored in the Unix password file. See ENCRYPT ().

ENCRYPT (str [, salt])

Encrypt the str using the Unix crypt () system call. The salt parameter should be a string of 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 eight characters of str and ignores everything else, at least on some systems. This will be 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]

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

Note: there is no space between-p or-- password= and the password that follows it. It is not safe to specify a password on the command line

For join parameters that are not available on the command line, mysql uses the default values:

* the default host name is localhost.

* the default user name is your Unix login.

* if there is no-p, no password is provided.

The specification of default parameters:

Specify the connection parameters in the [client] section of the configuration file of ".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 have the client prompt for a password or specify a password in an appropriately 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, 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 access of file File_priv on the server

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

File permission gives you access to LOAD DATA INFILE and SELECT. The INTO OUTFILE statement reads and writes files on the server, and any user granted this permission can read or write any file that the MySQL server can read or write.

two。 Access control: connection confirmation

Identity checking uses the three (Host, User, and Password) range fields of the user table. The server accepts connections only if an user table entry matches your hostname and user name and you provide the correct password.

Note: a host value can be a hostname or an IP number, or localhost indicates the local host. You can use the wildcard characters "%" and "_" in the Host field. The Host value% matches any hostname. When a connection is attempted, the server browses the sorted entry and uses the first match found.

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

3. Access control: request confirmation

Once you have established a connection, the server enters phase 2. For each request that comes in on this connection, the server checks that you have sufficient permissions to execute it, and the authorization table operates 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 did nothing.

The GRANT and REVOKE commands allow the system supervisor to authorize and revoke the rights granted to MySQL users at four permission levels:

Global level

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

Database level

Database permissions apply to all tables in a given database. These permissions are stored in the mysql.db and mysql.host tables.

Table level

Table permissions apply to all columns of a given table. These permissions are stored in the mysql.tables_ private table.

Column level

Column permissions apply to a single column in a given table. These permissions are stored in the mysql.columns_ private table.

User table permissions are superuser permissions. It is wise to grant permissions to only the user table to a super user such as a server or database manager. For other users, you should set the permissions in the user table to N and authorize them only on a specific database basis, using db and host tables.

4. When does the permission change take effect

When mysqld starts, all authorization table contents are read into memory and take effect from that point.

Changes made to the authorization table with GRANT, REVOKE, or SET PASSWORD are immediately noticed by the server.

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

5. Establish initial MySQL permissions

After installing MySQL, you install the initial access permissions by running scripts/mysql_install_db. Contains the following permission sets:

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

* an anonymous user who can do anything for any period of time on a database with a test or a name starting with test_. The connection must be made by the local host. This means that any local user can connect and be considered anonymous.

* other permissions are denied. For example, the average user cannot use mysqladmin shutdown or mysqladmin processlist.

Specify a password for the MySQL root user (note that you use the PASSWORD () function to specify a 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 above, 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

Look at the scripts/mysql_install_db script to see how it installs the default permissions. You can use it as a basis for studying how to add other users.

To completely rebuild the permissions table, delete all "* .frm", "* .MYI" and "* .MYD" files in the directory containing my. (this is the directory named "mysql" under the database directory, which is listed when you run mysqld-- help.) Then run the mysql_install_db script, probably after editing it first with the permissions you want.

1. Add new user rights to MySQL

There are 2 different ways to add users:

By using the Grant statement or by directly manipulating the MySQL authorization table.

It is better to use Grant statements 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 *. * TO

Thank you for your reading. the above is the content of "Application skills of MySQL access". After the study of this article, I believe you have a deeper understanding of the application skills of MySQL access, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.