Add users under MySQL and give permissions to the implementation

Source: Internet
Author: User

The methods for adding users to MySQL are as follows:

1 log in with the root user

Mysql-u root-p

2 Select MySQL Database


3 Adding users

Insert into User (Host,user,password) VALUES ("localhost", "Your user name", Password ("Your password"));

Replace the kanji with the user name or password you want to use

4 Refreshing the MySQL system permissions related table

Flush privileges;

To add permissions to a User:

Suppose I now have a database in the database named Ysou, there is a user named honest user, I want to let honest have all the tables in Ysou (Increase and remove) permissions,

The operation is as follows:

1 log in with the root user

Mysql-u root-p

2 Adding permissions

Grant all on ysou.* to honest;

3 Exit root, use honest to log in to MySQL, use show databases to see the current honest can manipulate the table, of course, we can enter the corresponding database to operate.

mysql> UPDATE user SET Password=password ("123") WHERE;

mysql> FLUSH privileges;

Mysql> GRANT all on mail.* to identified by "456";

Solution Grant all privileges on * * to identified by ' 1 ';

Flush privileges;


Mysql> grant permissions 1, permissions 2,... Permission n on the database name. Table name to User name @ user address identified by ' connection password ';

Permissions 1, Permissions 2,... Permission n represents 14 permissions, such as Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file.
When permissions 1, permissions 2,... Permission n is replaced by all privileges or all to give the user full permission.
When the database name. The table name is replaced by *. *, which indicates that the user is given permission to manipulate all tables on the server.
The user address can be localhost, or it can be an IP address, a machine name, and a domain name. You can also use '% ' to indicate connections from any address.
' Connection password ' cannot be empty, otherwise the creation failed.

Mysql>grant Select,insert,update,delete,create,drop on Vtdc.employee to [e-mail protected] identified by ' 123′;
Assign the user Joe from the ability to perform operations such as Select,insert,update,delete,create,drop on the employee table VTDC the database, and set the password to 123.

Mysql>grant all privileges in vtdc.* to [e-mail protected] identified by ' 123′;
For users from, Joe assigns permissions to all operations on the database VTDC all tables, and sets the password to 123.

Mysql>grant all privileges on * * to [e-mail protected] identified by ' 123′;
For users from, Joe assigns permissions to all the tables in all databases and sets the password to 123.

Mysql>grant all privileges on * * to [e-mail protected] identified by ' 123′;
Assign the native user Joe permission to all operations on all tables in all databases, and set the password to 123.

Grant allows the creator of an object to give certain permissions to a user or to a group or all users (public). After an object is created, except for the creator, the other person does not have permission to access the object unless the Creator grants permission. Once a user has permission to an object, he can use that privilege. You do not need to give the creator permission to the (GRANT) object, and the creator automatically owns all the permissions for the object, including the permission to delete it.



grant-give a user, a group or all user access rights

GRANT privilege [, ...] On object [, ...]

to {public | Group Group | Username}



The possible permissions are:


Access all the columns/fields of the declared table/view.


Inserts all column fields into the declared table.


Update all the columns/fields of the claim.


Removes all rows from the declared table.


Define the rule on the table/view (see CREATE rule statement).


Give all permissions.


The name of the object to which the permission is given. The possible objects are:

Table (Tables)

View (views)

Sequence (sequence)

Index (indexed)


Delegates are shorthand for all users.

Group Group

Group to which the permission will be given. In the current version, the group must be explicitly created using the following method.


The name of the user that will be given permission. Public is shorthand for all users.



If successful, returns this information.

ERROR:ChangeAcl:class "Object" not found

If the declared object is not available or it is not possible to grant permissions to the declared group or user.


For now, to give only a few columns of permissions to the Postgres, you must create a view that contains those columns, and then assign the permissions to the several views.

Use the Psql \z command to get more information about existing object permissions:

Database = Lusitania


| Relation | Grant/revoke Permissions |


| MyTable | {"=RW", "MIRIAM=ARWR", "group Todos=rw"} |



UNAME=ARWR--privileges granted to a user

Group GNAME=ARWR--privileges granted to a group

=ARWR--privileges granted to public






Tip: For now, to create a group, you will have to manually insert data into the table Pg_group, like this:

INSERT into Pg_group VALUES (' Todos ');

CREATE USER Miriam in GROUP Todos;

Refer to the REVOKE statement to reassign access rights.


Permission to insert records to table films for all users:

GRANT INSERT on films to public;

Give the user all permissions manuel The operation view kinds:

GRANT all on kinds to Manuel;


The SQL92 grant syntax allows you to set permissions on a single column/field in a table, and allows you to set a permission to give others the same permissions.
GRANT privilege [, ...]
On object [(column [, ...])] [, ...]
to {public | username [, ...]} [With GRANT OPTION]
These fields are compatible with the Postgres implementation, except for the following exceptions:
SQL92 allows you to declare additional permissions:
Allows some or all of the columns/fields to be used in the consolidation constraints of a declared table.
Allows a domain, character set, collection, or transaction to be used. If the declared object is not a table/view, privilege can only be declared as USAGE.
[Table] Table
SQL92 allows an additional non-function keyword TABLE.
Allows the use of a declared character set.
Enables the use of a declared collection sequence.
Allows the use of declared character set conversions.
Allows the use of declared domains.
Allow the same permissions to be granted to others.

Add users under MySQL and give permissions to the implementation

Related Article

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: 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.