程序员人生 网站导航

mysql学习笔记之十四(安全性机制)

栏目:数据库应用时间:2015-05-28 08:54:25
root和普通用户

权限机制
    mysql系统数据库
        show databases;
        use mysql;
        show tables;
            +---------------------------+
            | Tables_in_mysql           |
            +---------------------------+
            | columns_priv              |
            | db                        |
            | func                      |
            | help_category             |
            | help_keyword              |
            | help_relation             |
            | help_topic                |
            | host                      |
            | proc                      |
            | procs_priv                |
            | tables_priv               |
            | time_zone                 |
            | time_zone_leap_second     |
            | time_zone_name            |
            | time_zone_transition      |
            | time_zone_transition_type |
            | user                      |
            +---------------------------+
        mysql.user
            +-----------------------+-----------------------------------+------+-----+---------+-------+
            | Field                 | Type                              | Null | Key | Default | Extra |
            +-----------------------+-----------------------------------+------+-----+---------+-------+
            | Host                  | char(60)                          | NO   | PRI |         |       |
            | User                  | char(16)                          | NO   | PRI |         |       |
            | Password              | char(41)                          | NO   |     |         |       |
            | Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
            | Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
            | Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
            | Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
            | Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
            | Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
            | Reload_priv           | enum('N','Y')             | NO   |     | N       |       |重新加载权限表
            | Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
            | Process_priv          | enum('N','Y')             | NO   |     | N       |       |服务器管理
            | File_priv             | enum('N','Y')                     | NO   |     | N       |       |
            | Grant_priv            | enum('N','Y')             | NO   |     | N       |       |Grand Option 数据库,表,存储进程函数
            | References_priv       | enum('N','Y')                     | NO   |     | N       |       |
            | Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
            | Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
            | Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
            | Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
            | Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
            | Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
            | Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
            | Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
            | Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
            | Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
            | Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
            | Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
            | Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
            | Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
            | ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
            | ssl_cipher            | blob                              | NO   |     | NULL    |       |
            | x509_issuer           | blob                              | NO   |     | NULL    |       |
            | x509_subject          | blob                              | NO   |     | NULL    |       |
            | max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
            | max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
            | max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
            | max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
            +-----------------------+-----------------------------------+------+-----+---------+-------+    

            Host    主机名
            User    用户名
            Password

            上述3个字段都验证成功,才允许用户登录

            权限字段,以_priv结尾的字段,决定了用户权限
                高级权限
                    对用数据库进行管理
                普通权限    
                    对数据库操作
            安全字段
                ssl*~x509_su*,主要用来实现加密
                mysql通常不支持ssl标准
                show variables like 'have_openssl'
                上句可以用来查询是不是支持ssl
                +---------------+----------+
                | Variable_name | Value    |
                +---------------+----------+
                | have_openssl  | DISABLED |
                +---------------+----------+

        mysql.db
            +-----------------------+---------------+------+-----+---------+-------+
            | Field                 | Type          | Null | Key | Default | Extra |
            +-----------------------+---------------+------+-----+---------+-------+
            | Host                  | char(60)      | NO   | PRI |         |       |
            | Db                    | char(64)      | NO   | PRI |         |       |
            | User                  | char(16)      | NO   | PRI |         |       |
            | Select_priv           | enum('N','Y') | NO   |     | N       |       |
            | Insert_priv           | enum('N','Y') | NO   |     | N       |       |
            | Update_priv           | enum('N','Y') | NO   |     | N       |       |
            | Delete_priv           | enum('N','Y') | NO   |     | N       |       |
            | Create_priv           | enum('N','Y') | NO   |     | N       |       |
            | Drop_priv             | enum('N','Y') | NO   |     | N       |       |
            | Grant_priv            | enum('N','Y') | NO   |     | N       |       |
            | References_priv       | enum('N','Y') | NO   |     | N       |       |
            | Index_priv            | enum('N','Y') | NO   |     | N       |       |
            | Alter_priv            | enum('N','Y') | NO   |     | N       |       |
            | Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
            | Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
            | Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
            | Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
            | Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
            | Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
            | Execute_priv          | enum('N','Y') | NO   |     | N       |       |
            +-----------------------+---------------+------+-----+---------+-------+

            用户字段
                Host,User,Db
            权限字段
                形如*_priv
        mysql.host
            +-----------------------+---------------+------+-----+---------+-------+
            | Field                 | Type          | Null | Key | Default | Extra |
            +-----------------------+---------------+------+-----+---------+-------+
            | Host                  | char(60)      | NO   | PRI |         |       |
            | Db                    | char(64)      | NO   | PRI |         |       |
            | Select_priv           | enum('N','Y') | NO   |     | N       |       |
            | Insert_priv           | enum('N','Y') | NO   |     | N       |       |
            | Update_priv           | enum('N','Y') | NO   |     | N       |       |
            | Delete_priv           | enum('N','Y') | NO   |     | N       |       |
            | Create_priv           | enum('N','Y') | NO   |     | N       |       |
            | Drop_priv             | enum('N','Y') | NO   |     | N       |       |
            | Grant_priv            | enum('N','Y') | NO   |     | N       |       |
            | References_priv       | enum('N','Y') | NO   |     | N       |       |
            | Index_priv            | enum('N','Y') | NO   |     | N       |       |
            | Alter_priv            | enum('N','Y') | NO   |     | N       |       |
            | Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
            | Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
            | Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
            | Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
            | Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
            | Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
            | Execute_priv          | enum('N','Y') | NO   |     | N       |       |
            +-----------------------+---------------+------+-----+---------+-------+

            用户字段
                Host,Db
            权限字段
        其他权限表
            tables_priv
                对单表进行权限设置   
                *************************** 1. row ***************************
                  Field: Host
                   Type: char(60)
                   Null: NO
                    Key: PRI
                Default:
                  Extra:
                *************************** 2. row ***************************
                  Field: Db
                   Type: char(64)
                   Null: NO
                    Key: PRI
                Default:
                  Extra:
                *************************** 3. row ***************************
                  Field: User
                   Type: char(16)
                   Null: NO
                    Key: PRI
                Default:
                  Extra:
                *************************** 4. row ***************************
                  Field: Table_name
                   Type: char(64)
                   Null: NO
                    Key: PRI
                Default:
                  Extra:
                *************************** 5. row ***************************
                  Field: Grantor
                   Type: char(77)
                   Null: NO
                    Key: MUL
                Default:
                  Extra:
                *************************** 6. row ***************************
                  Field: Timestamp
                   Type: timestamp
                   Null: NO
                    Key:
                Default: CURRENT_TIMESTAMP
                  Extra:
                *************************** 7. row ***************************
                  Field: Table_priv
                   Type: set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view')
                   Null: NO
                    Key:
                Default:
                  Extra:
                *************************** 8. row ***************************
                  Field: Column_priv
                   Type: set('Select','Insert','Update','References')
                   Null: NO
                    Key:
                Default:
                  Extra:        
            columns_priv
                对单列进行权限设置
                +-------------+----------------------------------------------+------+-----+-------------------+-------+
                | Field       | Type                                         | Null | Key | Default           | Extra |
                +-------------+----------------------------------------------+------+-----+-------------------+-------+
                | Host        | char(60)                                     | NO   | PRI |                   |       |
                | Db          | char(64)                                     | NO   | PRI |                   |       |
                | User        | char(16)                                     | NO   | PRI |                   |       |
                | Table_name  | char(64)                                     | NO   | PRI |                   |       |
                | Column_name | char(64)                                     | NO   | PRI |                   |       |
                | Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP |       |
                | Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |       |
                +-------------+----------------------------------------------+------+-----+-------------------+-------+
            procs_priv
                系统表
                +--------------+----------------------------------------+------+-----+-------------------+-------+
                | Field        | Type                                   | Null | Key | Default           | Extra |
                +--------------+----------------------------------------+------+-----+-------------------+-------+
                | Host         | char(60)                               | NO   | PRI |                   |       |
                | Db           | char(64)                               | NO   | PRI |                   |       |
                | User         | char(16)                               | NO   | PRI |                   |       |
                | Routine_name | char(64)                               | NO   | PRI |                   |       |
                | Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |       |
                | Grantor      | char(77)                               | NO   | MUL |                   |       |
                | Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |       |
                | Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP |       |
                +--------------+----------------------------------------+------+-----+-------------------+-------+


用户机制

    用户管理机制包括登陆退出mysql,创建用户,修改用户密码,删除用户,为用户赋予权限

    1、登陆或退出mysql

        mysql -h hostname|hostIP -p port -u username -pPassword DatabaseName -e "SQL语句"     
        登陆数据库服务器,如果直接接密码,则密码password直接写在-p以后,中间不能有空格

        mysql -u root -p 
        登陆本地的数据库,则隐藏输入密码

        exit|quit:退出数据库

    2、创建普通用户
        root用户的权限太大,应当严格杜绝使用root登陆mysql
        (a)create user
            create user username [identifield by [password] 'password']
                [,username [identifield by [password] 'password']]
                ....
            password关键字主要用来对密码进行加密

            create user qionghua identified by '123456';

        (b)insert
            mysql.user存储了关于用户账户的信息
            insert into user(host,user,password) values('hostname','username',password('password'));
            在具体创建用户账户时,由于表user中字段ssl_cipher,x509_issuer,x509_subject没有默许值,所以还需要设置这些字段的值,对password字段,1定要使用函数PASSWORD()进行加密   
            创建用户成功后如果不能登陆,使用
                flush privileges
            刷新下权限
        (c)grant
            上述两种创建用户的方式不方便给用户赋予权限
            grant priv_type on databasename.tablename
                to username[identified by [password]'password']
                   [,username[identified by [password]'password']]  
                   [,username[identified by [password]'password']]
                    ....
    2、修改用户密码

        两种方式:通过超级权限用户root和通过普通用户

        root用户密码修改
            1、mysqladmin
                修改root用户密码
                    mysqladmin -u username -p oldpassword "new_password"
            2、set
                登陆mysql服务器后,可以通过set命令修改root用户密码
                set password=password('new password')
            3、更改系统表mysql.user数据记录修改
                update user set password=password('new_password') 
                    where user='root' and host='localhost'
        普通用户密码修改
            1、仿照创建用户时的grant方式
                grant priv_type on databasename.tablename
                    to username[identified by [password]'password']
            2、set命令
                通过root账号登陆mysql后使用set命令
                set password for 'username'@'hostname'=password("newpassword")

                通过普通账户登陆mysql后使用set命令修改自己的密码
                set password=password("new_password")
            3、更改系统表mysql.user
                update user set password=password("password") where user='username' and host="localhost"
                如果没有设置主机,则不需要匹配host="localhost"

    3、删除普通用户账户
        1、drop user
            drop user user1[,usre2,...] 
        2、删除系统表mysql.user数据记录
            delete from user where user="username" and host="hostname"  
权限管理
    授权权限
        在进行授权操作之前,需要用户具有grant权限
        grant priv_type[(column_list)] on databasename.tablename
            to user [identified by [password] "password"]
            [,user [identified by [password] "password"]]
            ...
            [with with_option[with_option]....]
        with_option的取值
            grant option
            max_queries_per_hour count
            max_update_per_hour count
            max_connections_per_hour count
            max_user_connections count 单个用户可以同时具有count个连接

    查看权限
        1、查看系统表mysql.user的数据记录
            grant select,update,create,drop on *.* to 'qionghua';
            select * from mysql.user where user='qionghua'G
            *************************** 1. row ***************************
                 Host: %
                 User: qionghua
                 Password: *00A51F3F48415C7D4E8908980D443C29C69B60C9
              Select_priv: Y
              Insert_priv: N
              Update_priv: Y
              Delete_priv: N
              Create_priv: Y
                Drop_priv: Y
            ........
            后面为N的字段省略。
            同这个查询,可以看出该用户所具有的权限
    收回权限
        revoke priv_type[(column_list)] on databasename.tablename
            from user1 [identified by [password] "password"]
            [,user2 [identified by [password] "password"]]
            ...
            [with with_option[with_option]....]
        收回全部权限
            revoke all privilege,grant option
            from user1 [identified by [password] "password"]
            [,user2 [identified by [password] "password"]]
            ...
        mysql> show grants for "qionghua"G
        *************************** 1. row ***************************
        Grants for qionghua@%: GRANT SELECT, UPDATE, CREATE, DROP ON *.* TO 'qionghua'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74
        329105EE4568DDA7DC67ED2CA2AD9'
        1 row in set (0.00 sec)

        mysql> revoke select on *.* from "qionghua";
        Query OK, 0 rows affected (0.00 sec)

        mysql> show grants for "qionghua"G
        *************************** 1. row ***************************
        Grants for qionghua@%: GRANT UPDATE, CREATE, DROP ON *.* TO 'qionghua'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE
        4568DDA7DC67ED2CA2AD9'
        1 row in set (0.00 sec)
------分隔线----------------------------
------分隔线----------------------------

最新技术推荐