DOCX

Account and authority management in MySQL

By Christopher Reynolds,2015-04-09 18:44
19 views 0
Account and authority management in MySQL

    Account and authority management in MySQL

    MySQL rights management

    The working principle of authority system

    MySQL privileges system certification through the following two phases:

    (1) to connect a user identity authentication, legitimate users through certification, illegal connection refused.

    (2) the certified legitimate users give the corresponding privileges, users can within the scope of these privileges to the operation of the database accordingly.

    For identity, MySQL is confirmed through the IP address and user name, such as MySQL user created a default installation root @ localhost said user root can only from the local (localhost) to connect to through the authentication, the user from any other host on the database connection will be rejected.That is to say, the same user name, if from different IP addresses, MySQL as a different user.

    MySQL permissions tablestart to load the memory when I was in the database, when the user through the identity authentication, makes a corresponding permissions to access in memory, in this way, the user can do various operations within the scope of authority in database.

    Access permissions table

    In the process of the two permissions to access, the system will use "mysql databases" (is created, when installing mysql database name is "mysql") of the user, host and db, the three most important permissions table

The name of the table user db host

    The user list User Host Host

     Password Db Db

    Permissions, Select_priv User Select_priv

     Insert_priv Select_priv Insert_priv

     Update_priv Insert_priv Update_priv

     Delete_priv Update_priv Delete_priv

     Create_priv Delete_priv Create_priv

     Drop_priv Create_priv Drop_priv

     Reload_priv Drop_priv Grant_priv

     Shutdown_priv Grant_priv References_priv

     Process_priv References_priv Index_priv

     File_priv Index_priv Alter_priv

     Grant_priv Alter_priv Create_tmp_table_pri

    v

     References_priv Create_tmp_table_priLock_tables_priv

    v

     Index_priv Lock_tables_priv Create_view_priv

     Alter_priv Create_view_priv Show_view_priv

     Show_db_priv Show_view_priv Create_routine_priv

     Super_priv Create_routine_priv Alter_routine_priv

     Create_tmp_table_priAlter_routine_priv Execute_priv

    v

     Lock_tables_priv Execute_priv Trigger_priv

     Execute_priv Event_priv

     Repl_slave_priv Trigger_priv

     Repl_client_priv

     Create_view_priv

     Show_view_priv

     Create_routine_priv

     Alter_routine_priv

     Create_user_priv

     Event_priv

     Trigger_priv

     Create_tablespace_pr

    iv

Security column ssl_type

     ssl_cipher

     x509_issuer

     x509_subject

     max_questions

     max_updates

     max_connections

     max_user_connections

    In the table 3, the most important table

    Among them, usually use the most is the user privilege column and column, which permissions in divided into ordinary authority and administrative authority.Ordinary authority is mainly used for the operation of the database, such as select_priv, create_priv, etc.The administrative authority is mainly used to manage the database operation, such as process_priv, super_priv, etc.

    When users connect permissions table access process has the following a stage now.

    ; From the host in the user table, the user first and passwd judgment connections in the three fields of

    IP, user name and password whether exists in the table, if present, is authenticated, otherwise

    rejected the connection.

    ; If authenticated, according to the following permissions table order get database permissions: user

    - > db - > tables_priv - > coloumns_priv.

    In this a few permissions table, the scope of authority, in turn global permissions to cover local authority.

    The first stage of the above understanding, the following as a example to explain in detail the second stage.

    (1) create user CQH @ localhost, and give all all database table select privilege.

mysql> grant select on *.* to cqh@localhost;

Query OK, 0 rows affected (0.05 sec)

    mysql> select * from user where user='cqh' and host='localhost' \G

    *************************** 1. row ***************************

     Host: localhost

     User: cqh

     Password:

     Select_priv: Y

     Insert_priv: N

     Update_priv: N

     Delete_priv: N

     Create_priv: N

     Drop_priv: N

     ...

    (2) look at the db table:

mysql> select * from db where user='cqh';

    Empty set (0.00 sec)

    Can be found that the user table select_priv column is "Y", and did not record in db table, that is, for all database has the same permissions user record does not need to write down the db table, just need to user select_priv instead to "Y" in the table.In other words, the user in the table every permissions represent the full access to all databases.

    (3) to CQH @ the permissions on the localhost instead just select privilege on all table on the test database.

    mysql> revoke select on *.* from cqh@localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant select on test.* to cqh@localhost; Query OK, 0 rows affected (0.00 sec) mysql> select * from user where user='cqh' and host='localhost' \G

    *************************** 1. row ***************************

     Host: localhost

     User: cqh

     Password:

     Select_priv: N

     Insert_priv: N

     Update_priv: N

     Delete_priv: N

     Create_priv: N

     Drop_priv: N

     Reload_priv: N

     Shutdown_priv: N

     Process_priv: N

     File_priv: N

     Grant_priv: N

     References_priv: N

     Index_priv: N

     Alter_priv: N

     Show_db_priv: N

     Super_priv: N

     Create_tmp_table_priv: N

     Lock_tables_priv: N

     Execute_priv: N

     Repl_slave_priv: N

     Repl_client_priv: N

     Create_view_priv: N

     Show_view_priv: N

     Create_routine_priv: N

     Alter_routine_priv: N

     Create_user_priv: N

     Event_priv: N

     Trigger_priv: N Create_tablespace_priv: N

     ssl_type:

     ssl_cipher:

     x509_issuer:

     x509_subject:

     max_questions: 0

     max_updates: 0

     max_connections: 0

     max_user_connections: 0

     plugin:

     authentication_string: NULL 1 row in set (0.00 sec)

mysql> select * from db where user='cqh'\G

    *************************** 1. row ***************************

     Host: localhost

     Db: test

     User: cqh

     Select_priv: Y

     Insert_priv: N

     Update_priv: N

     Delete_priv: N

     Create_priv: N

     Drop_priv: N

     Grant_priv: N

     References_priv: N

     Index_priv: N

     Alter_priv: N Create_tmp_table_priv: N

     Lock_tables_priv: N

     Create_view_priv: N

     Show_view_priv: N

     Create_routine_priv: N

     Alter_routine_priv: N

     Execute_priv: N

     Event_priv: N

     Trigger_priv: N 1 row in set (0.00 sec)

    Found at this time, the user select_priv into "N" in the table, while increase the db table by db for the test of a record, that is to say, when only certain permissions granted to database, the user in the table corresponding permissions, user permissions corresponding column in the table to keep "N", and to write specific database access into the db table.

    Table and column permission mechanism similar to the db, there is no longer here.

    Can be seen from the above example, when a user by authority certification, assign permissions, will be in accordance with the user - > db - > tables_priv - > assign permissions coloumns_priv order, namely, first check the global permissions table user, if the user in the corresponding permissions for "Y", then the user for all database permissions to "Y", will no longer check the db, tables_priv and coloumns_priv;If it is "N" to check the db table for the user the corresponding specific database, and to get to "Y" in the db access;If the corresponding permissions in the db for "N", then check the database in the tables_priv corresponding specific tables, obtain the permissions for the "Y" in the table;If tables_priv the corresponding permissions for "N", then check this table in the columns_priv corresponding concrete columns, column for "Y" permissions.

    Account management

    Bank account management mainly includes the creation of, permissions, change and delete accounts.The first step of users connect to the database are created from the account.

    There are two kinds of methods can be used to create accounts: using GRANT authorization table syntax to create or direct operation, but more is recommended to use the first method, because of simple operation, less likely to go wrong.

    A. Create account

    GRANT a common syntax is as follows:

GRANT

     priv_type [(column_list)]

     [, priv_type [(column_list)]] ...

     ON [object_type] priv_level

     TO user_specification [, user_specification] ...

     [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]

     [WITH with_option ...]

    GRANT PROXY ON user_specification

     TO user_specification [, user_specification] ...

     [WITH GRANT OPTION]

    object_type:

     TABLE

     | FUNCTION

     | PROCEDURE

    Look at the following example.

    Example 1: create user CQH, permissions for all permissions can be performed on all

    database, only from the local to connect.

    mysql> grant all privileges on *.* to cqh@localhost; Query OK, 0 rows affected (0.00 sec)

    mysql> select * from user where user='cqh' and host='localhost' \G

    *************************** 1. row ***************************

     Host: localhost

     User: cqh

     Password:

     Select_priv: Y

     Insert_priv: Y

     Update_priv: Y

     Delete_priv: Y

     Create_priv: Y

     Drop_priv: Y

     Reload_priv: Y

     Shutdown_priv: Y

     Process_priv: Y

     File_priv: Y

     Grant_priv: N

     References_priv: Y

     Index_priv: Y

     Alter_priv: Y

     Show_db_priv: Y

     Super_priv: Y

     Create_tmp_table_priv: Y

     Lock_tables_priv: Y

     Execute_priv: Y

     Repl_slave_priv: Y

     Repl_client_priv: Y

     Create_view_priv: Y

     Show_view_priv: Y

     Create_routine_priv: Y

     Alter_routine_priv: Y

     Create_user_priv: Y

     Event_priv: Y

     Trigger_priv: Y Create_tablespace_priv: Y

Report this document

For any questions or suggestions please email
cust-service@docsford.com