Grant: Postgresql 9.0 得到增强

sdfsdnfslk

    Postgresql9.0 在 grant 命令上得到了增强。它提供一条 grant命令，
能够将某个schema下的全部表赋给用户, 这在工作中带来了便利。
--来自PG9.0官网文档,语法如下
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
      其中 "ALL TABLES IN SCHEMA schema_name" 就为上面的这种用法。今天正好有项目
有查询需求，需要新增一查询帐号，能够查询生产库上所有的业务表。以下是详细步骤
1. 开通数据库连接权限
skytf=#grant connect on database skytf to select_csl;
GRANT
2. 开通数据库 skytf下业务SCHEMA的使用权限
grant select on  all tables in schema skytf        to select_csl;
3 给查询帐号 select_csl赋予 schemal skytf 下所有表的只读权限
skytf=# grant select on all tables in schema skytf to select_csl;
GRANT
4. 检查权限是否已加    
skytf=> select grantor,grantee,table_schema,table_name from information_schema.table_privileges where grantee='select_csl'
and table_schema='skytf' order by table_name ;
 grantor |  grantee   | table_schema |         table_name          
---------+------------+--------------+-----------------------------
 skytf     | select_csl | skytf        | skytf_user
 skytf     | select_csl | skytf        | albumcheck_log
 skytf     | select_csl | skytf        | building_guest_log
 ..
 ..
 省略
(15 rows)
     上面部分结果已经省略，从结果可以看出 帐号select_csl 获得了skytf下所有的表的查询权限。
5.官网解释
There is also an option to grant privileges on all objects of the same type within one or more schemas. T
his functionality is currently supported only for tables, sequences, and functions 
(but note that ALL TABLES is considered to include views).
  上面说 tables,sequences,functions 支持这种用法。
  
6.0更多用法：来自官网
GRANT { { USAGE | SELECT | UPDATE }
    [,...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
　　总结： Postgresql 提供的 " ALL ... IN SCHEMA schema_name " 用法使给DBA在日常工作中只需要一条命令
            就能给指定用户授权，打破了传统的每张表单独赋权的模式。
        
           上面只演示 tables 的授权方法，有兴趣的朋友可能对　functions 和 sequences 做下类似的实验。
           这里不再详细描述。