MySQL User management

User management for MySQL

User account Format

[Email protected]

View Help

MySQL > Help Contents; MySQL > Help account Management;

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M02/8B/B4/wKiom1hV1w7hVEq3AAAlPfMOAJw298.png-wh_500x0-wm_3 -wmp_4-s_1211069233.png "title=" 1.png "alt=" Wkiom1hv1w7hveq3aaalpfmoajw298.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/B0/wKioL1hV1z6jGNelAAAdlewVFPw185.png-wh_500x0-wm_3 -wmp_4-s_424347469.png "title=" 1.png "alt=" Wkiol1hv1z6jgnelaaadlewvfpw185.png-wh_50 "/>

Account Management commands (limited permissions at this time)

Create Userdrop userrename userset password

Rights Management Commands:

Grantrevoke

View Create user command Help

mysql> help create user; CREATE USER [email protected] [identified by [PASSWORD] ' PASSWORD '] host can also use wildcard characters: [email protected] ' 172.16.100.                                      1__ ' (172.16.100.100-172.16.100.199) (two underscore) can also use%:[email protected] ' 172.16.%.% ' See what permissions users can use: Show grants for [email protected] ' hostname '

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/B4/wKiom1hV2M2CeWcYAAA0G0maiyM571.png-wh_500x0-wm_3 -wmp_4-s_1870092160.png "style=" Float:none; "title=" 1.png "alt=" Wkiom1hv2m2cewcyaaa0g0maiym571.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/B0/wKioL1hV2M7SRYKuAAGqb2JmUqA872.png-wh_500x0-wm_3 -wmp_4-s_3893565452.png "style=" Float:none; "title=" 2.png "alt=" Wkiol1hv2m7srykuaagqb2jmuqa872.png-wh_50 "/>

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/B0/wKioL1hV2M6QFXuvAAAoW6lEeww615.png-wh_500x0-wm_3 -wmp_4-s_4173618626.png "style=" Float:none; "title=" 3.png "alt=" Wkiol1hv2m6qfxuvaaaow6leeww615.png-wh_50 "/>

Renaming

MySQL > Help rename user Rename user old_user to New_user [, Old_user to New_user] ... mysql > Rename user ' Jef Frey ' @ ' localhost ' to ' Jeff ' @ ' 127.0.0.1 ';

Set a password for a user

MySQL > Help Set password; Set PASSWORD [for user] = {PASSWORD (' cleartext PASSWORD ')}mysql > set PASSWORD for [email protected] ' 172 .16.%.% ' = password (' 123456 '); Call the password function

Type of permissions for MySQL:

Library level

Table level

Field level

Management class

Program classes (Stored procedures, triggers, storage functions, Time schedulers)

Administrative class permissions

Create Temporary tables Temp table

Create user

File allows users to read or write certain files

Lock tables Add an explicit lock

Process: View the user's thread

Reload: Equivalent to execute flush and reset

Replication client queries which replication clients

Replication slave giving users copy rights

Show databases

Shutdown

Super

Database access permissions (Library level and table level)

alter:modify tables with ALTER tables

Alter routine Modify the storage history

Create

Create routine stored procedures, stored functions

CREATE view

Delete

Drop

Whether execute can execute stored procedures, or store functions

Grant option copies its own permissions to other users

Index indexes

Show view

Data Manipulation Class permissions (table level)

Select

Insert

Update

Delete

Field Level :

Select (col1,.... ）

Update (col1,.... ）

Insert (col1,.... ）

All Permissions

all [privileges]

View Help

mysql > help grant    priv_type [(column_list)]       [, priv_type [(column_list)]] ...    on [object_type ] PRIV_LEVEL    TO USER_SPECIFICATION [, USER_SPECIFICATION]&NBSP, .....     [REQUIRE {NONE | SSL_OPTION [[AND] SSL_OPTION]&NBSP, ...}]     [WITH WITH_OPTION&NBSP, ...] Object_type: (Default is table)     table  | function  | procedurepriv_ Level:    *  | *.*  | db_name.*  | db_ name.tbl_name  | tbl_name  | db_name.routine_nameuser_specification:     user    [        identified by  [PASSWORD]  ' PASSWORD '       | identified with auth_plugin [as  ' auth_string ']    ] //can implement authorization and add passwords ssl_ option:    ssl    ...with_option:    grant  option  to others   | MAX_QUERIES_PER_HOUR count  maximum number of queries allowed per hour   | max_ updates_per_hour count  Maximum number of updates allowed per hour   | MAX_CONNECTIONS_PER_HOUR count  Maximum allowed connections per hour   | MAX_USER_CONNECTIONS count  number of simultaneous connections using the same account

Instance

Grant TestUser user The MySQL > Grant create on testdb.* to [email protected] ' 192.168.139.1__ ';

At this point testuser can only create TestDB libraries

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8B/B4/wKiom1hV2rHwU59BAAAYyfMaH5M457.png-wh_500x0-wm_3 -wmp_4-s_398568808.png "title=" 1.png "alt=" Wkiom1hv2rhwu59baaayyfmah5m457.png-wh_50 "/>

View your permissions MySQL > show grants for [email protected] ' 192.168.139.1__ ';

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/8B/B0/wKioL1hV2vuhKIFeAAIGDH35G9k426.png-wh_500x0-wm_3 -wmp_4-s_897807571.png "style=" Float:none; "title=" 2.png "alt=" Wkiol1hv2vuhkifeaaigdh35g9k426.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/B4/wKiom1hV2vuxavQyAAAYvUxIztk712.png-wh_500x0-wm_3 -wmp_4-s_898411770.png "style=" Float:none; "title=" 3.png "alt=" Wkiom1hv2vuxavqyaaayvuxiztk712.png-wh_50 "/>

You can create tables under this database, but because there is no select and drop permissions,

Therefore cannot view table data and structure, cannot delete table

Give testuser user permission to delete a table MySQL > Grant drop on testdb.* to [email protected] ' 192.168.139.1__ '; View permissions MySQL > show grants for [ Email protected] ' 192.168.139.1__ ';

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8B/B4/wKiom1hV24myxeMQAAEvZnqSJHs308.png-wh_500x0-wm_3 -wmp_4-s_2143345778.png "title=" 1.png "alt=" Wkiom1hv24myxemqaaevznqsjhs308.png-wh_50 "/>

To testuser users to increase, delete, check, change the rights of MySQL > Grant Select,insert,update,delete on testdb.* to [email protected] ' 192.168.139.1__ '; View Permissions MySQL > show grants for [email protected] ' 192.168.139.1__ ';

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/8B/B0/wKioL1hV272BC7b7AAFDPo3axW0675.png-wh_500x0-wm_3 -wmp_4-s_161606123.png "title=" 2.png "alt=" Wkiol1hv272bc7b7aafdpo3axw0675.png-wh_50 "/>

Note: To create a library, you should have permissions on all tables

Revoke authorization

MySQL > Help REVOKE priv_type [(column_list)] [, Priv_type [(column_list)]] ...    On [object_type] priv_level from user [, user] ...    REVOKE all privileges, GRANT OPTION from user [, user] ... REVOKE PROXY on the user from user [, user] ...

Example:

mysql > Revoke insert,select on testdb.* from [email protected] ' 192.168.139.1__ '; View permissions MySQL > show grants for [email Protected] ' 192.168.139.1__ ';

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/B0/wKioL1hV3CuSEGbGAAFj5gP_7-s413.png-wh_500x0-wm_3 -wmp_4-s_4164301907.png "title=" 1.png "alt=" Wkiol1hv3cusegbgaafj5gp_7-s413.png-wh_50 "/>

Several tables related to user authorization: (in MySQL library)

DB: library-level permissions host: Host-level permissions, deprecated Tables_priv: Table-level Permissions Columns_priv: Column-level permissions procs_priv: Stored procedures and stored function-related permissions Proxies_priv: Delegate user Rights ·

Almost every operation of MySQL involves a permission management check, so the authorization table is loaded into memory (to improve speed)

This article is from the "Homecoming" blog, make sure to keep this source http://sixijie123.blog.51cto.com/11880770/1883685

MySQL User management