MySQL Application skills access rights

MySQL uses the user name for the authentication purpose, regardless of the UNIX user name (login name) or the Windows user name. By default, most MySQL clients try to log on using the current UNIX username as the MySQL username, but this is only for convenience, the client program allows you to specify a different name with the-U or--user option. and security considerations, all MySQL user names should have a password.

1. MySQL Username and password

* MySQL uses user names for authentication purposes, regardless of UNIX user name (login name) or Windows user name. By default, most MySQL clients try to log on using the current UNIX username as the MySQL username, but this is only for convenience, the client program allows you to specify a different name with the-U or--user option. and security considerations, all MySQL user names should have a password.

* MySQL username can be as long as 16 characters, typically, the UNIX user name is limited to 8 characters.

* The MySQL password does not matter with the UNIX password.

* The MySQL encryption password uses the different algorithms used during a UNIX login, PASSWORD () and Encrypt ()

function password (str)

Computes a password string from the plain text password str. This function is used to encrypt the MySQL password in order to store the password 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 the 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 encrypted value stored in the UNIX password file. See Encrypt ().

ENCRYPT (Str[,salt])

Use the Unix crypt () system to invoke encrypted Str. The salt parameter should be a 2-character string. (In MySQL 3.22.16, a 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 () only keeps Str starting at 8 characters and ignores all others, at least on some systems. This is determined by the behavior that is invoked by the underlying crypt () system.

1. Connect with MySQL server

Syntax format:

shell> MySQL [-H host_name][-u User_name][-pyour_pass]

Another form of the-H,-u and-P options is--host=host_name 、--user=user_name and--password=your_pass.

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

For a join parameter that is not on the command line, MySQL uses the default value:

* The default host name is localhost.

* The default user name is your UNIX login name.

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

Specify the default value parameter:

Specify the connection parameters in the [Client] section of the configuration file for ". 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 the environment variable

The safest way to do this is to have the client prompt for a password or specify a password in an appropriately protected ". My.cnf" file.

1. mysql provides the right

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 access to files on the server

Note: Grant permissions allow you to delegate those permissions you own to other users.

The file permission gives you the load DATA infile and select ... Into the outfile statement reads and writes files on the server, and any user who is granted this permission can read or write any files that the MySQL server can read or write.

2. Access Control: Connection Confirmation

The identity check uses the User table 3 (Host, user, and password) range fields. The server only accepts connections if a user table entry matches your host name and username and you provide the correct password.

Note: A host value can be either a hostname or an IP number, or ' localhost ' indicates the local host. You can use the wildcard character "%" and "_" in the Host field. The host value '% ' matches any host name. 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 username, when the server tries to find a match for the connection, all entries that explicitly name that user will be used first. This is clearly 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 to see if 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 versions. For earlier MySQL versions, the GRANT statement does nothing.

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

Global level

Global Permissions Act on all databases on a given server. These permissions are stored in the Mysql.user table.

Database level

Database Permissions Act on all tables in a given database. These permissions are stored in the mysql.db and Mysql.host tables.

Table level

Table Permissions Act on all columns of a given table. These permissions are stored in the Mysql.tables_priv table.

Column level

Column permissions are used as a single column in a given table. These permissions are stored in the Mysql.columns_priv table.

User table permissions are superuser permissions. It is wise to grant the user table permissions only to Superuser, such as a server or database supervisor. For other users, you should set the permissions in the user table to ' N ' and authorize it only on a specific database basis, using DB and Host tables.

4. When permission changes take effect

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

Modifications to the authorization table with GRANT, REVOKE, or set password are immediately noticed by the server.

If you manually modify the authorization form (using INSERT, UPDATE, and so on), you should execute a flush privileges statement or run Mysqladmin flush-privileges tell the server to reload the authorization form, or your changes will not take effect. Unless you reboot the server.

5. Establish the initial MySQL permissions

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

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

* An anonymous user who can do any period of time with a database with a ' test ' or a name starting with ' Test_ ', and the connection must be sent by the local host. This means that any local user can connect and be treated as an anonymous user.

* Other permissions are denied. For example, a general 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 the password):

shell> mysql-u Root MySQL

mysql> UPDATE user SET Password=password (' New_password ')

WHERE user= ' root ';

mysql> FLUSH privileges;

In the MySQL 3.22 and above 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

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

To completely rebuild the permission table, delete all "*.frm", "*" in the directory that contains the MySQL database. Myi "and" *. MyD "file. (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 you first edit it with the permissions you want.

1. Add new user rights to MySQL

Add 2 different methods to the user:

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

The better approach 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 in *.* to Monty@localhost

Identified by ' something ' with GRANT OPTION;

Mysql> GRANT all privileges in *.* to monty@ "%"

Identified by ' something ' with GRANT OPTION;

Mysql> GRANT reload,process on *.* to Admin@localhost;

Mysql> GRANT USAGE on *.* to Dummy@localhost;

Monty

You can connect to a full superuser from anywhere, but you must use a password (' Something ' to do this.) Note that we must issue a grant statement to Monty@localhost and monty@ "%". If we add a localhost entry, an entry created by mysql_install_db for the localhost anonymous user entry when we connect from the local host is preferred, because it has a more specific host field value, so it's an earlier arrival in the user table order.

Admin

Users who can connect from localhost without a password and are granted reload and process management privileges. This allows the user to perform mysqladmin reload, mysqladmin refresh and mysqladmin flush-* commands, as well as Mysqladmin processlist. No permissions are granted to the database. They can authorize it later by issuing another grant statement.

Dummy

A user can connect without a password, but only from the local host. Global permissions are set to ' N '--usage permission type allows you to set a user without permission. It assumes that you will grant database-related permissions at a later time.

You can also add the same user access information directly by issuing an INSERT statement, and then tell the server to reload the authorization table again:

shell> MySQL--user=root MySQL

mysql> INSERT into user VALUES (' localhost ', ' Monty ', PASSWORD (' something '),

' Y ', ' y ', ' Y