MySQL access permission system _ MySQL

1. MySQL username and password * 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: 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 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 *. * TO dummy @ localhost;
Monty
A complete super user who can connect to the server from anywhere, but must use a password ('something' to do this. Note: You must issue a GRANT statement to monty @ localhost and monty @ "%. If we add a localhost entry, the entry created by mysql_install_db for the anonymous user entry of localhost takes priority when we connect from the local Host, because it has a more specific Host field value, therefore, the user table is arranged in the order of users.
Admin
Users who can connect from localhost without a password and are granted reload and process management permissions. This allows you to run the mysqladmin reload, mysqladmin refresh, mysqladmin flush-* commands, and mysqladmin processlist commands. No database-related permissions are granted. They can GRANT permissions in the future by issuing another GRANT statement.
Dummy
You do not need a password to connect to a user, but you can only access the user from the local host. The global permission is set to 'n' -- the USAGE permission type allows you to set a user without permission. It assumes that you will grant database-related permissions in the future.
You can also directly add the same user access information by issuing the INSERT statement, and then tell the server to load the authorization table again:
Shell> mysql -- user = root mysql
Mysql> insert into user VALUES ('localhost', 'Monty ', PASSWORD ('something '),
'Y', 'y', 'Y