admin管理员组文章数量:1638817
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,
(aclexplode(COALESCE(pg_class.relacl, acldefault('r'::"char", pg_class.relowner)))).grantee AS grantee,
(aclexplode(COALESCE(pg_class.relacl, acldefault('r'::"char", pg_class.relowner)))).privilege_type AS privilege_type,
(aclexplode(COALESCE(pg_class.relacl, acldefault('r'::"char", 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权限，来观察该系统表的数据。
1. 创建角色
postgres=# create role john login NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
CREATE ROLE2. 赋予该角色一个数据库的表的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)本文标签： 权限信息postgresqlinformationschematableprivileges
版权声明：本文标题：PostgreSql 权限信息表information_schema.table_privileges 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://m.elefans.com/dongtai/1729279946a1193939.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论