关于 SEQUENCE 的 USAGE | SELECT | UPDATE 权限实例

本文主要是介绍关于 SEQUENCE 的 USAGE | SELECT | UPDATE 权限实例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

结论:

usage:  对应 nextval 执行权限
select: 对应 select * from <sequence_name>; 执行权限
update: 对应 setval 执行权限

查看 SEQUENCE 相关的权限

GRANT { { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }ON { SEQUENCE sequence_name [, ...]| ALL SEQUENCES IN SCHEMA schema_name [, ...] }TO role_specification [, ...] [ WITH GRANT OPTION ]

实例

psql
(postgres)test3=# create sequence test_id_seq;
CREATE SEQUENCE
(postgres)test3=# create user test with password 'test';
CREATE ROLE
(postgres)test3=# \du test List of rolesRole name | Attributes | Member of 
-----------+------------+-----------test   |            | {}(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> \dp+ test_id_seqAccess privilegesSchema |    Name     |   Type   | Access privileges | Column privileges | Policies 
--------+-------------+----------+-------------------+-------------------+----------public | test_id_seq | sequence |                   |                   | 
(1 row)-- 默认 新建的用户对序列无 usage/select/update 权限
(postgres)test3=> select * from test_id_seq; 
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> 
(postgres)test3=> \c - postgres 
You are now connected to database "test3" as user "postgres".
(postgres)test3=# grant select on SEQUENCE test_id_seq to test ;
GRANT
(postgres)test3=# \dp+ test_id_seqAccess privilegesSchema |    Name     |   Type   |  Access privileges  | Column privileges | Policies 
--------+-------------+----------+---------------------+-------------------+----------public | test_id_seq | sequence | postgres=rwU/postgres+|                   | |             |          | test=r/postgres   |                   | 
(1 row)
-- select 对应的 select * from  <sequence_name>; 
(postgres)test3=# 
(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> select * from test_id_seq ;last_value | log_cnt | is_called 
------------+---------+-----------75 |       8 | t
(1 row)(postgres)test3=> select nextval('test_id_seq');
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> 
(postgres)test3=> 
(postgres)test3=> select setval('test_id_seq',10,false);
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> -- usage 对应的是 nextval 执行权限
(postgres)test3=> \c - postgres
You are now connected to database "test3" as user "postgres".
(postgres)test3=# grant usage on SEQUENCE test_id_seq to test ;
GRANT
(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> select setval('test_id_seq',10,false);
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> select nextval('test_id_seq');nextval 
---------76
(1 row)(postgres)test3=> select nextval('test_id_seq');nextval 
---------77
(1 row)-- update 对应的是 setval 权限
(postgres)test3=> \c - postgres 
You are now connected to database "test3" as user "postgres".
(postgres)test3=# grant update on SEQUENCE test_id_seq  to test ;
GRANT
(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> select * from test_id_seq;last_value | log_cnt | is_called 
------------+---------+-----------77 |      31 | t
(1 row)(postgres)test3=> select nextval('test_id_seq');nextval 
---------78
(1 row)(postgres)test3=> select nextval('test_id_seq');nextval 
---------79
(1 row)(postgres)test3=> select setval('test_id_seq',100,false);setval 
--------100
(1 row)(postgres)test3=> select nextval('test_id_seq');nextval 
---------100
(1 row)
(postgres)test3=> exit

这篇关于关于 SEQUENCE 的 USAGE | SELECT | UPDATE 权限实例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---