PostgreSQL中用户对表的访问权限控制

参考资料：http://vhttps://www.postgresql.org/docs/9.5/static/sql-revoke.html

E:Program FilesHighGo DataBasebin>psql -E -U highgo -d highgo
highgo=# create database lyy;
highgo=# create user yy password 'yy';

highgo=# c lyy
You are now connected to database "lyy" as user "highgo".
lyy=# create table test1(id int);
CREATE TABLE
lyy=# create table test2(id int);
CREATE TABLE
--对当前库中所有表去掉public的所有访问权限，为了确保除了所有者之外的洽谈用户不能操作这些表。
lyy=# revoke all on test1 from public;
REVOKE
lyy=# revoke all on test2 from public;
REVOKE
--去掉对pg_class的访问权限，为了确保yy用户不能看到所有表名的列表。
lyy=# revoke all on pg_class from public;
REVOKE
lyy=# revoke all on pg_class from yy;
REVOKE
--添加yy用户对test1表的所属关系，确保yy用户对test1表有权限操作
lyy=# ALTER TABLE test1 OWNER TO yy;
lyy=# q

--此时用户yy连接lyyku会报错说没有connect权限，那么就授予用户yy对数据库lyy的访问权限
E:Program FilesHighGo DataBasebin>psql -E -U yy -d lyy
Password for user yy:
psql: FATAL: permission denied for database "lyy"
DETAIL: User does not have CONNECT privilege.

E:Program FilesHighGo DataBasebin>psql -E -U highgo
Password for user highgo:
psql (2.0.2)
Type "help" for help.

highgo=# grant connect on database lyy to yy;
GRANT
highgo=# q

--此时用户yy连接lyy库后，可以对自己拥有的test表操作，但是对于其他表不能操作，也不能查看所有表的表名列表。
E:Program FilesHighGo DataBasebin>psql -E -U yy -d lyy
Password for user yy:
psql (2.0.2)
Type "help" for help.

lyy=> select * from test1;
id
----
(0 rows)

lyy=> select * from test2;
ERROR: permission denied for relation test2

lyy=> d --查看所有表名的列表
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

ERROR: permission denied for relation pg_class lyy=>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!