Modify Mysql user password settings and assign Permissions

Mysql user password setting modification and permission assignment my mysql is installed in c: \ mysql I. Change the password first method: 1. Change the password before the root has no password c: \ mysql \ bin> mysqladmin-u root password "your password" 2. Change the password of the root user if it is 123456 c: \ mysql \ bin> mysqladmin-u root-p123456 password "your password" NOTE: The changed password cannot be enclosed in single quotes. You can use double quotation marks or no quotation marks. Method 2: 1. c: \ mysql \ bin> mysql-uroot-p password Log On As root 2. mysql> use mysql select database 3. mysql> update user set password = password ('your password ') where User = 'root'; 4. mysqlflush Privileges; reload permission Table 2. user permission settings 1. Log On As root (or other users with permissions). 2. Create a test user and set the password to test, mysql> grant all on picture. * TO test identified by "test"; the syntax of the GRANT statement looks like this: GRANT privileges (columns) ON what TO user identified by "password" with grant option to use this statement, you need to fill in the following parts: privileges grants the user permissions. The following table lists the permission specifiers that can be used for the GRANT statement: permission to specify the permissions allowed to operate Alter Modify Table and index Create database and table Delete table existing records Drop discard (Delete) database and table IN DEX creates or discards Index Insert inserts a new row REFERENCE into the table. The record Update in the Select search table is not used. Modify the existing table record FILE read or write the file process on the server to view the thread information or kill the execution in the server. the thread RELOAD reloads the authorization table or clears logs, host caches, or table caches. SHUTDOWN shut down ALL the server; all privileges synonyms USAGE special "no permission" permissions The table above shows that the first group of permission specifiers apply to databases, tables, and columns, and the second group of several management permissions. Generally, these are relatively strictly authorized because they allow users to affect server operations. The third group has special permissions. "ALL" means "ALL Permissions", and "UASGE" means "no Permissions", that is, creating users, but not granting permissions. Columns permission is optional, and you can only set column-specific permissions. If the command has more than one column, separate them with commas. What permission usage level. Permissions Can be global (applicable to all databases and tables), specific databases (applicable to all tables in a database), or specific tables. You can specify a columns statement to indicate that the permission is column-specific. The user authorized by the user, which consists of a user name and a host name. In MySQL, you not only specify who can connect, but also where to connect. This allows two users with the same name to connect from different places. MySQL allows you to differentiate them and grant them permissions independently. A user name in MySQL is the user name specified when you connect to the server. It does not need to be associated with your Unix or Windows Name. By default, if you do not specify a specific name, the customer program uses your login name as the MySQL user name. This is just an agreement. You can change the name to "nobody" in the authorization table, and then use the "nobody" connection to perform operations that require superuser permissions. Password: Optional. If you do not specify the identified by clause for a new user, the user is not assigned a password (Insecure ). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains unchanged. When you use identified by, the password string uses the literal meaning of the password, and GRANT will encode the password for you, do not use the PASSWORD () function like SET password. The with grant option clause is optional. If you include it, you can GRANT permissions to other users through the GRANT statement. You can use this clause to grant permissions to other users. Note: The username, password, database, and table names are case sensitive in the authorization table records, and the host name and column name are not. Generally, you can identify the types of GRANT statements by asking a few simple questions: who can connect and connect from there? What level of permissions should users have and what do they apply? Should the user be allowed to manage permissions? The following are some examples. 1.1 who can connect and connect from there? You can allow a user to connect from a specific host or a series of hosts. There is one extreme: If you know that a demotion is connected from a host, you can limit the permission to a single host: grant all on samp_db. * TO boris @ localhost identified by "ruby" grant all on samp_db. * TO fred@res.mars.com identified by "quartz" (samp_db. * Meaning "all tables in the samp_db Database") Another extreme is that you may have a user max who travels frequently and needs to be connected from hosts around the world. In this case, you can allow him to connect from anywhere: grant all on samp_db. * TO max @ % identified by "diamond" % "is used as a wildcard and matches the LIKE pattern. In the preceding statement, it means "any host ". So max and max @ % are equivalent. This is the easiest way to build a user, but it is also the least secure. You can allow a user to access from a restricted host set. For example, to allow mary to connect from any host in the snake.net domain, use the "grant all on samp_db" specified by the "% .snke.net" host. * TO mary @ .snke.net identified by "quartz"; if you like, the host part of the User Identifier can be specified BY an IP address instead of a host name. You can specify an IP address or an address that contains a pattern character. In addition, from MySQL 3.23, you can also specify an IP number with a network mask indicating the number of digits used for the network number: grant all on samp_db. * TO boris@192.168.128.3 identified by "ruby" grant all on samp_db. * TO fred@192.168.128. % identified by "quartz" grant all on samp_db. * TO rex@192.168.128.0/17 identified by "ruby" the first example shows that the user can connect from a specific host, and the second specifies the IP Mode for Class C subnet 192.168.128, in the third statement, 192.168.128.0/17 specifies a 17-bit network number and matches an IP address with the header of 192.168.128. 1.2 What level of permissions should users have and what should they apply? You can grant different levels of permissions. Global permissions are the most powerful because they apply to any database. To make Etel a Super User who can do anything, including authorizing other users, issue the following statement: grant all on *. * TO Etel @ localhost identified by "coffee" with grant option on Clause *. * Indicates "all databases and all tables ". For security considerations, we specify that Etel can only be connected locally. It is usually wise to restrict the host that a Super User can connect to because it limits the host that tries to crack the password. Some permissions (FILE, PROCESS, RELOAD, and SHUTDOWN) are administrative permissions and can only be authorized with the "ON *. *" Global permissions. If you want to, you can grant these permissions without authorizing database permissions. For example, if the following statement sets a flush user, it can only issue flush statements. This may be useful when you need to execute management scripts such as clearing logs: grant reload on *. * TO flushl @ localhost identified by "flushpass" generally, you want TO authorize management permissions, because users with these permissions can affect operations on your servers. Database-level permissions apply to all tables in a specific database by using ON db_name. * Sub-statement authorization: grant all on samp_db TO bill@racer.snake.net indetified by "rock" GRANT Select ON samp_db TO ro_user @ % indetified by "rock" the first statement grants the permissions to all tables in the samp_db database TO bill, article 2 create a user ro_user (read-only user) with strict access restrictions, which can only access all tables in the samp_db database, but only read, that is, the user can only issue Select statements. You can list a series of permissions granted at the same time. For example, if you want users to read and modify the content of an existing database but cannot create or Delete a new table, GRANT the following permissions: GRANT Select, Insert, Delete, update ON samp_db TO bill@snake.net indetified by "rock" for more refined access control, you can authorize ON each table or even ON each column of the table. When you want to hide a part of a table from a user, or you want a user to modify only specific columns, column-specific permissions are very useful. For example, GRANT Select ON samp_db.member TO bill @ localhost indetified by "rock" GRANT Update (expiration) ON samp_db. member TO bill @ localhost the first statement grants read permission TO the entire member table and sets a password. The second statement adds the Update permission when only the expiration column is applied. You do not need to specify a password because the first statement has already been specified. If you want to grant permissions to multiple columns, specify a list separated by commas. For example, to add the Update permission for the address field of the member table to the assistant user, use the following statement to add the new permission to the user's existing permissions: GRANT Update (street, city, state, zip) ON samp_db TO assistant @ localhost 1.3 Should users be allowed TO manage permissions? You can allow a database owner to control database access by granting all database owner permissions. During authorization, specify with grant option. For example, if you want alicia to connect to any host in the big.corp.com domain and have the Administrator permission for ALL tables in the sales database, you can use the following GRANT statement: grant all on sales. * TO alicia @ % .big.corp.com indetified by "applejuice" with grant option in effect, the with grant option clause allows you to grant the Access Authorization right TO another user. Note that two users with the GRANT permission can authorize each other. If you only GRANT Select permission to the first user and GRANT Select permission to the other user, the second user can be the first user more powerful ". 2. REVOKE and delete a user. Use the REVOKE statement to cancel the permission of a user. The REVOKE syntax is very similar TO the GRANT statement, except for replacing from with the indetifed by and with grant option clause: REVOKE privileges (columns) ON what FROM user must match the user part of the user you want to revoke permissions FROM the original GRANT statement. Privileges does not need to be matched. You can use the GRANT statement to GRANT permissions, and then use the REVOKE statement to REVOKE only some permissions. The REVOKE statement only deletes permissions, but does not delete users. Even if you revoke all permissions, the user records in the user table are retained, which means that the user can still connect to the server. To completely Delete a user, you must use a Delete statement to explicitly Delete user records from the user table: % mysql-u root mysqlmysql> Delete FROM user-> Where User = "user_name" and Host = "host_name"; mysql> flush privileges; Delete statement Delete user records, the FLUSH statement tells the server to overload the authorization table. (When you use the GRANT and REVOKE statements, the table is automatically reloaded, but you do not modify the authorization table directly .)