PostgreSQL 权限信息表information_schema.table_privileges
发布时间:2020-12-13 17:19:44 所属栏目:百科 来源:网络整理
导读:information_schema.table_privileges表记录着所有用户的权限信息。 postgres=# d+ information_schema.table_privileges View "information_schema.table_privileges" Column | Type | Modifiers | Storage | Description ----------------+--------------
information_schema.table_privileges表记录着所有用户的权限信息。 postgres=# d+ information_schema.table_privileges
View "information_schema.table_privileges"
Column | Type | Modifiers | Storage | Description
----------------+-----------------------------------+-----------+----------+-------------
grantor | information_schema.sql_identifier | | extended | 授权者
grantee | information_schema.sql_identifier | | extended | 被授权者
table_catalog | information_schema.sql_identifier | | extended | 数据库名
table_schema | information_schema.sql_identifier | | extended | schema名
table_name | information_schema.sql_identifier | | extended | 表名
privilege_type | information_schema.character_data | | extended | 对表的操作权限
is_grantable | information_schema.yes_or_no | | extended |
with_hierarchy | information_schema.yes_or_no | | extended |
View definition:
SELECT u_grantor.rolname::information_schema.sql_identifier AS grantor,grantee.rolname::information_schema.sql_identifier AS grantee,current_database()::information_schema.sql_identifier AS table_catalog,nc.nspname::information_schema.sql_identifier AS table_schema,c.relname::information_schema.sql_identifier AS table_name,c.prtype::information_schema.character_data AS privilege_type,CASE WHEN pg_has_role(grantee.oid,c.relowner,'USAGE'::text) OR c.grantable THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS is_grantable,CASE WHEN c.prtype = 'SELECT'::text THEN 'YES'::text ELSE 'NO'::text END::information_schema.yes_or_no AS with_hierarchy FROM ( SELECT pg_class.oid,pg_class.relname,pg_class.relnamespace,pg_class.relkind,pg_class.relowner,(aclexplode(COALESCE(pg_class.relacl,acldefault('r'::"char",pg_class.relowner)))).grantor AS grantor,pg_class.relowner)))).grantee AS grantee,pg_class.relowner)))).privilege_type AS privilege_type,pg_class.relowner)))).is_grantable AS is_grantable FROM pg_class) c(oid,relname,relnamespace,relkind,relowner,grantor,grantee,prtype,grantable),pg_namespace nc,pg_authid u_grantor,( SELECT pg_authid.oid,pg_authid.rolname FROM pg_authid UNION ALL SELECT 0::oid AS oid,'PUBLIC'::name) grantee(oid,rolname) WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r'::"char",'v'::"char"])) AND c.grantee = grantee.oid AND c.grantor = u_grantor.oid AND (c.prtype = ANY (ARRAY['INSERT'::text,'SELECT'::text,'UPDATE'::text,'DELETE'::text,'TRUNCATE'::text,'REFERENCES'::text,'TRIGGER'::text])) AND (pg_has_role(u_grantor.oid,'USAGE'::text) OR pg_has_role(grantee.oid,'USAGE'::text) OR grantee.rolname = 'PUBLIC'::name);
现在创建一个角色并赋予SELECT权限,来观察该系统表的数据。
postgres=# create role john login NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
CREATE ROLE
2. 赋予该角色一个数据库的表的SEKECT权限 -- 赋予数据库的链接权限
postgres=# GRANT CONNECT ON DATABASE postgres TO john;
GRANT
-- 赋予表的查询功能
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO john;
GRANT
-- tb1表的INSERT INTO权限
postgres=# GRANT INSERT ON tb1 TO john;
GRANT
3. 查看该角色的所有权限 postgres=# SELECT * from information_schema.table_privileges where grantee='john' order by privilege_type;
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+---------+---------------+--------------+----------------------+----------------+--------------+----------------
postgres | john | postgres | public | tb1 | INSERT | NO | NO postgres | john | postgres | public | book | SELECT | NO | YES postgres | john | postgres | public | weather | SELECT | NO | YES postgres | john | postgres | public | cities | SELECT | NO | YES postgres | john | postgres | public | tb1 | SELECT | NO | YES postgres | john | postgres | public | book2 | SELECT | NO | YES postgres | john | postgres | public | person | SELECT | NO | YES postgres | john | postgres | public | tb2 | SELECT | NO | YES postgres | john | postgres | public | orders | SELECT | NO | YES postgres | john | postgres | public | test_unlogged | SELECT | NO | YES postgres | john | postgres | public | test | SELECT | NO | YES postgres | john | postgres | public | system_monitor | SELECT | NO | YES postgres | john | postgres | public | tb3 | SELECT | NO | YES postgres | john | postgres | public | pg_stat_statements | SELECT | NO | YES postgres | john | postgres | public | view_business_device | SELECT | NO | YES postgres | john | postgres | public | student | SELECT | NO | YES postgres | john | postgres | public | pgbench_tellers | SELECT | NO | YES postgres | john | postgres | public | pgbench_branches | SELECT | NO | YES postgres | john | postgres | public | pgbench_accounts | SELECT | NO | YES postgres | john | postgres | public | pgbench_history | SELECT | NO | YES postgres | john | postgres | public | goods | SELECT | NO | YES postgres | john | postgres | public | bloat | SELECT | NO | YES (22 rows)
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
相关内容
- Oracle APEX 系列文章6:Oracle APEX 到底适不适合企业环境
- ruby-on-rails – 如何摆脱Spring警告:运行`gem pristine
- Oracle 统计用户下表的数据量
- 为什么VC C4150(删除指针到不完整的类型)只有一个警告?
- 详解次小生成树以及相关的C++求解方法
- c – 简单地包括SDL头导致链接器错误
- ruby-on-rails – 如何破坏Ruby on Rails中Paperclip错误创
- ruby – 为什么写或者不是||?
- Failed to replace a bad datanode on the existing pipeli
- ruby-on-rails – 通过ansible playbook安装私有宝石时捆绑