MySQL user set Password and permissions

Source: Internet
Author: User
Tags ip number net domain

http://apps.hi.baidu.com/share/detail/5530778

My MySQL is installed in C:\mysql
First, change the password
The first way:
1. The situation where root does not have a password before change
C:\mysql\bin>mysqladmin-u root password "Your password"
2. If the root has a password before the change, if it is 123456
C:\mysql\bin>mysqladmin-u root-p123456 Password "Your password"
   Note:Changed passwords cannot be enclosed in single quotes, double or no quotation marks

The second way:
1, c:\mysql\bin>mysql-uroot-p PasswordLog in as Root
2, mysql>Use MySQLSelect Database
3, mysql>Update user Set Password=password (' Your password ') where user= ' root ';
4. mysqlflush Privileges;Reload Permissions Table

Second, user rights settings
1. Log in as root (also available to other authorized users)
2, create a test user, the password is test, and can only manipulate the picture database command
Mysql>GRANT all on picture.* to test identified by "test";

The syntax for the GRANT statement looks like this:
   grant privileges (columns) on the What to the user identified by ' password ' with GRANT OPTION  

To use this statement, you need to fill in the following sections:
   PrivilegesPermissions granted to the user, the following table lists the permission specifiers that are available for the GRANT statement:
Allowed actions for permission specifier permissions
   Altermodifying tables and Indexes
   CreateCreating databases and Tables
   DeleteDelete an existing record in a table
   DropDiscard (delete) databases and tables
   INDEXCreate or discard an index
   InsertInsert a new row into the table
   REFERENCENot used
   SelectRetrieving records in a table
   UpdateModify an existing table record
   FILERead or write files on the server
   PROCESSView thread information or kill threads in a server
   RELOADOverloads the authorization table or empties the log, host cache, or table cache.
   SHUTDOWNShutting down the server
   AllAll privileges synonyms
   USAGESpecial "No Permissions" permissions

The above table shows the permissions specified in the first set of applies for databases, tables, and columns, and the second set of administrative permissions. Generally, these are relatively strictly authorized because they allow the user to affect the operation of the server. The third set of permissions is special, all means "all permissions," and Uasge means no permissions, that is, creating the user, but not granting permissions.

   ColumnsThe permissions apply to the column, which is optional, and you can only set column-specific permissions. If the command has more than one column, you should separate them with commas.

   WhatThe level at which permissions are applied. Permissions can be global (for all databases and all tables), a specific database (for all tables in a database), or a specific table. You can specify a columns sentence by specifying that the permission is column-specific.

   UserThe user granted permission, which consists of a user name and host name. In MySQL, you not only specify who can connect, but also where to connect. This allows you to connect two users of the same name from different places. MySQL allows you to differentiate between them and give permission independently of each other. A user name in MySQL is the user name you specify when you connect to the server, which does not have to be associated with your UNIX login or Windows name. By default, if you do not explicitly specify a name, the client program will use your login name as the MySQL user name. It's just a convention. You can change the name to nobody in the authorization form, and then perform the action that requires superuser privileges on the nobody connection.

   PasswordGives the user a password, which is optional. If you do not specify a identified by clause for a new user, the user does not assign a password (unsafe). For existing users, any password you specify will replace the old password. If you do not specify a password, the old password remains the same, and when you use identified by, the password string uses the literal meaning of the password instead, grant will encode the password for you, and do not use the PASSWORD () function with set PASSWORD.

   With GRANT OPTIONThe clauses are optional. If you include it, the user can grant permissions to other users through the GRANT statement. You can use this clause to give the ability to authorize other users.

   Note:The user name, password, database, and table name are case-sensitive in the authorization table record, and the hostname and column name are not.

In general, 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 to?
Should the user allow administrative privileges?

Some examples are discussed below.

1.1 Who can connect and connect from there?
You can allow a user to connect from a specific or a range of hosts. There's an extreme if you know demotion from a host connection, you can restrict permissions to a single host:

   GRANT all on samp_db.* to [e- Mail protected] identified by "Ruby"
GRANT all on samp_db.* to [e-mail protected] identified by "quartz"

(samp_db.* means "All tables of the samp_db database") Another extreme is that you may have a user Max who travels frequently and needs to be able to connect from hosts around the world. In this case, you can allow him to connect no matter where:
GRANT all on samp_db.* to [e-mail protected]% identified by "Diamond"
The "%" character character the wildcard function, which matches the meaning of the like pattern. In the above statement, it means "any host". So Max and [email protected]% are equivalent. This is the simplest way to build a user, but it is also the least secure.
Where you can allow a user to access from a restricted host collection. For example, to allow Mary to connect from any host in the snake.net domain, use a%.snake.net host specifier:
   GRANT all on samp_db.* to [e- Mail protected] identified by "quartz";
  
If you like, the host part of the user identifier can be given with an IP address instead of a hostname. You can specify an IP address or an address that contains a pattern character, and, from MySQL 3.23, you can also specify the IP number that has a netmask that indicates the number of bits used for the network number:

   GRANT all on samp_db.* to [e- Mail protected] identified by "Ruby"
GRANT all on samp_db.* to [e-mail protected]% identified by "quartz"
GRANT all on samp_db.* to [e-mail PROTECTED]/17 identified by "Ruby"

The first example indicates the specific host from which the user can connect, the second specifies the IP schema for the class C subnet 192.168.128, and in the third statement, 192.168.128.0/17 specifies a 17-bit network number and matches the IP address with 192.168.128 Header 17 bits.
 
1.2 What level of permissions should users have and what should they apply to?
You can authorize different levels of permissions, and global permissions are the most powerful because they apply to any database. To make Ethel a super user that can do anything, including being able to authorize other users, issue the following statement:

   Grant All on * * to [e-mail protected] identified by ' coffee ' with GRANT OPTION

The *. * in the ON clause means "all databases, all tables." From security considerations, we specify that Ethel can only be connected from local. Restricting a host that a superuser can connect to is usually sensible because it restricts 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 permission specifier. If you want, you can authorize these permissions without authorizing the database permissions. For example, the following statement sets a flush user, and he can only issue flush statements. This can be useful in administrative scripts that you need to perform such as emptying the log:
   GRANT RELOAD On * * to [e-mail protected] identified by "Flushpass"

In general, you want to grant administrative permissions, stingy, because users who have them can affect the operation of your server.
Database-level permissions apply to all tables in a particular database, and they can be granted by using the on db_name.* clause:

   Grant all on samp_db to [e- Mail protected] indetified by ' Rock ' GRANT Select on samp_db to [email protected]% indetifie D by "Rock"

The first statement authorizes bill to samp_db all tables in the database, the second creates a user Ro_user (read-only user) that restricts access, and only accesses all tables in the SAMP_DB database, but only reads, that is, the user can only issue SELECT statements.

You can list the various permissions that are granted at the same time. For example, if you want users to be able to read and modify the contents of an existing database, but cannot create a new table or delete a table, grant these permissions as follows:
  GRANT select,insert,delete,update on samp_db to [e-mail protected] indetified by "Rock"

For more sophisticated access control, you can authorize on individual tables, or even on each column of a table. Column-specific permissions are useful when you want to hide portions of a table from the user, or if you want a user to be able to modify only specific columns. Such as:

   GRANT Select on Samp_db.member to [e-mail protected] indetified by "Rock"
GRANT Update (expiration) on samp_db. Member to [email protected]
The first statement grants read access to the entire member table and sets a password, and the second statement adds the update permission when only the expiration column. There is no need to specify the password again because the first statement is already specified.
If you want to grant permissions to multiple columns, specify a comma-separated list. For example, for the assistant user to increase the update permission for the Address field of the member table, use the following statement, and the new permissions will be added to the user's existing permissions:
   GRANT Update (street,city,state,zip) on samp_db to [email protected]
 
1.3 Should the user be allowed to manage permissions?
You can allow the owner of a database to control access to the database by granting all owner permissions on the database, specifying with Grant OPTION when authorizing. For example, if you want Alicia to be able to connect from any host in the big.corp.com domain and have administrator permissions on all tables in the sales database, you can use the following grant statement:
   GRANT all on sales.* to [e- mail protected]%.big.corp.com indetified by ' Applejuice ' with GRANT OPTION

On the effect with the GRANT OPTION clause allows you to grant access to another user with authorization rights. Be aware that two users with grant permissions can authorize each other. If you give only the first user select permission, and the other user has grant plus SELECT permission, then the second user can be the first user more "powerful".

2 withdrawing rights and deleting users
To remove a user's permissions, use the REVOKE statement. The syntax of revoke is very similar to the GRANT statement, except to the From and without the indetifed by and with GRANT option clauses:

   REVOKE Privileges (columns) on how from user

The user section must match the users section of the original grant statement that you want to withdraw. The privileges part does not need to be matched, you can authorize with the grant statement, and then use the REVOKE statement to revoke only partial permissions.
The REVOKE statement removes only the permission without deleting the user. Even if you revoke all permissions, the user records in the users table remain, which means the user can still connect to the server. To completely remove a user, you must explicitly delete the user record from the users table with a DELETE statement:
%mysql-u root mysqlmysql>delete from user->where user= "user_name" and host= "host_name"; mysql>flush privileges;

The DELETE statement deletes the user record, and the flush statement tells the server to overload the authorization table. (When you use the GRANT and REVOKE statements, the table is automatically overloaded, not when you modify the authorization table directly.) )
For your safety, please only open URLs with reliable sources

Open Site Cancellation

From: http://hi.baidu.com/killerz/blog/item/cd6651437a57bf119213c660.html
Read (478) | Comments (0) | Forwards (0) |0

Previous: Crack site anti-theft chain method

Next: Scholars grant education injustice to hinder the poor second generation upward flow to the poorer people poorer

Related Popular articles
    • Python multi-process pipeline instance (modulo ...
    • Resolve MySQL "Access denied for ...
    • "Original" PostgreSQL implements MySQL ...
    • The new mysql5.1 Chinese manual ...
    • Four ways to start MySQL
    • Linux DHCP Peizhi ROC
    • Soft links to Unix files
    • What does this command mean, I'm new ...
    • What does sed-e "/grep/d" mean ...
    • Who can help me solve Linux 2.6 10 ...
Leave something to the owner! ~~ Comment on the hot topic

MySQL user set Password and permissions

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.