MySQL User management

Source: Internet
Author: User



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

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.