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,functions 支持这种用法。
6.0更多用法：来自官网
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [,...]
| ALL SEQUENCES IN SCHEMA schema_name [,...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [,...] ] ) [,...]
| ALL FUNCTIONS IN SCHEMA schema_name [,...] [ WITH GRANT OPTION ]
总结： Postgresql 提供的 " ALL ... IN SCHEMA schema_name " 用法使给DBA在日常工作中只需要一条命令
就能给指定用户授权，打破了传统的每张表单独赋权的模式。
上面只演示 tables 的授权方法，有兴趣的朋友可能对 functions 和 sequences 做下类似的实验。
这里不再详细描述。