了解usage权限的使用与管理session
场景1 ：只受权usage on schema 权限
session 1：
--建立test用户,并将highgo模式赋予test用户。url
| highgo=# create user test with password 'password';spa CREATE ROLE.net highgo=# grant USAGE on SCHEMA highgo to test ;对象 GRANT文档
highgo=# \dn highgo get List of schemasit Name | Owner io --------+--------event highgo | highgo |
session 2：
--登录readonly用户能够查询highgo模式下现存的全部表。
| highgo=# \c highgo test
highgo=> select * from highgo. highgo.big_table highgo.dept highgo.ump highgo.bonus highgo.pgdo highgo=> select * from highgo.ump ; pid | event_type | event | queryid | count ---------+---------------+-------------------------+-----------------+-------- 14764 | IO | DataFileRead | 0 | 3 14986 | IPC | MessageQueuInternal | 3876349911 | 1 14767 | Activity | LogicalLauncherMain | 0 | 273690 14986 | IO | DataFileImmediateSync | 0 | 1 14760 | Activity | AutoVacumMain | 0 | 273622 14765 | Extension | Extension | 0 | 268108 14757 | Activity | CheckpointerMain | 0 | 273344 16728 | Client | ClientRead | 0 | 1454 14765 | IPC | MessageQueuInternal | 4242708246 | 1 14757 | IO | DataFileSync | 0 | 1 16741 | Client | ClientRead | 0 | 44655 14758 | Activity | BgWriterHibernte | 0 | 256733 14758 | Activity | BgWriterMain | 0 | 16926 14757 | IO | DataFileWrite | 0 | 1 16425 | Client | ClientRead | 0 | 30320 14765 | LWLock | lock_manager | 0 | 1 14986 | Client | ClientRead | 0 | 253179 14759 | Activity | WalWriterMain | 0 | 273673 (18 rows)
|
切换到session1建立新表t1
| highgo=# create table t1 as select * from ump; |
切换到session2 test用户下，t1表没法查询
|
highgo=> select * from highgo. highgo.big_table highgo.dept highgo.t1 highgo.bonus highgo.pgdo highgo.ump
highgo=> select * from highgo.t1; ERROR: 42501: permission denied for relation t1 |
总结：若是只授予 usage on schema 权限，test只能查看 highgo 模式下已经存在的表和对象。
在授予 usage on schema 权限以后建立的新表没法查看。
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
场景2：授予usage on schema权限以后 ，再赋予 select on all tables in schema权限
针对场景1 [ERROR: 42501: permission denied for relation t1] 错误的处理
| highgo=> select * from highgo. highgo.big_table highgo.dept highgo.t1 highgo.bonus highgo.pgdo highgo.ump
highgo=> select * from highgo.t1; ERROR: 42501: permission denied for relation t1 |
session1：受权test用户select on all tables 权限
| highgo=# grant select on all tables in schema highgo TO test ; GRANT |
session2： test用户查询t1表
| highgo=> select * from highgo.t1 ; pid | event_type | event | queryid | count ---------+---------------+-------------------------+-----------------+-------- 14764 | IO | DataFileRead | 0 | 3 14986 | IPC | MessageQueuInternal | 3876349911 | 1 14767 | Activity | LogicalLauncherMain | 0 | 273690 14986 | IO | DataFileImmediateSync | 0 | 1 14760 | Activity | AutoVacumMain | 0 | 273622 14765 | Extension | Extension | 0 | 268108 14757 | Activity | CheckpointerMain | 0 | 273344 16728 | Client | ClientRead | 0 | 1454 14765 | IPC | MessageQueuInternal | 4242708246 | 1 14757 | IO | DataFileSync | 0 | 1 16741 | Client | ClientRead | 0 | 44655 14758 | Activity | BgWriterHibernte | 0 | 256733 14758 | Activity | BgWriterMain | 0 | 16926 14757 | IO | DataFileWrite | 0 | 1 16425 | Client | ClientRead | 0 | 30320 14765 | LWLock | lock_manager | 0 | 1 14986 | Client | ClientRead | 0 | 253179 14759 | Activity | WalWriterMain | 0 | 273673 (18 rows) |
session1：登录 highgo 用户的 highgo 模式下建立新表 t2
| highgo=# create table t2 as select * from ump ; SELECT 18 |
session2：test用户查询t2表权限不足
| highgo=> select * from highgo.t2; ERROR: 42501: permission denied for relation t2 |
session1：再次赋予 grant select on all tables
| highgo=# grant select on all tables in schema highgo TO test ; GRANT |
session2：test用户又能够查看 t2 表
| highgo=> select * from highgo.t2 ; pid | event_type | event | queryid | count ---------+---------------+-------------------------+-----------------+-------- 14764 | IO | DataFileRead | 0 | 3 14986 | IPC | MessageQueuInternal | 3876349911 | 1 14767 | Activity | LogicalLauncherMain | 0 | 273690 14986 | IO | DataFileImmediateSync | 0 | 1 14760 | Activity | AutoVacumMain | 0 | 273622 14765 | Extension | Extension | 0 | 268108 14757 | Activity | CheckpointerMain | 0 | 273344 16728 | Client | ClientRead | 0 | 1454 14765 | IPC | MessageQueuInternal | 4242708246 | 1 14757 | IO | DataFileSync | 0 | 1 16741 | Client | ClientRead | 0 | 44655 14758 | Activity | BgWriterHibernte | 0 | 256733 14758 | Activity | BgWriterMain | 0 | 16926 14757 | IO | DataFileWrite | 0 | 1 16425 | Client | ClientRead | 0 | 30320 14765 | LWLock | lock_manager | 0 | 1 14986 | Client | ClientRead | 0 | 253179 14759 | Activity | WalWriterMain | 0 | 273673 (18 rows) |
更多详细信息请登陆【瀚高技术支持平台】查看 https://support.highgo.com/#/index/docContentHighgo/5ec1f71d00ef9617