vertica角色管理

vertica数据库有5个预定义角色：
public
pseudosuperuser
dbadmin
dbduser
sysmonitor
预定义角色不能drop或者rename，public特殊一点，可以对public赋予回收role，但是不能对其他4个预定义role赋予回收其他role；

dbadmin role有如下权限：
Create or drop users
Create or drop schemas
Create or drop roles
Grant roles to other users
View all system tables
View and terminate user sessions
Access to all data created by any user

不允许user：
Start and stop a database
Set configuration parameters

在vsql中输入\du，可以查看超级用户：
dbadmin=> \du
      List of users
 User name | Is Superuser
-----------+--------------
 dbadmin   | t
 ming1     | f
 ming2     | f
 mingadmin | f
(4 rows)

pseudosuperuser role：
该角色在每一个数据库中自动创建。
这个角色可以做：
Create schemas
Create and grant privileges to roles
Bypass all GRANT/REVOKE authorization
Set user account's passwords
Lock and unlock user accounts
Create or drop a UDF library
Create or drop a UDF function
Create or drop an external procedure
Add or edit comments on nodes
Create or drop password profiles

public role：
默认地，每一个数据库都会有一个public role。在创建用户时，vertica会自动地将这个role赋予用户，并且在用户登录时自动生效，不用用户显式的set。该角色不可drop，不可revoke，也不可赋予with grant option；


赋予test角色权限：
dbadmin=> grant select on mingshuo.t1 to test;
WARNING 5682:  USAGE privilege on schema "mingshuo" also needs to be granted to "test"
GRANT PRIVILEGE

dbadmin=> grant usage on schema mingshuo to test;
GRANT PRIVILEGE

如今有ming1，ming2，ming3三个用户：
dbadmin=> grant test to ming1 with admin option;
GRANT ROLE
这里与oracle数据库不同的是，vertica数据库没有with grant option选项。admin选项就可以继续给其他用户赋予。

dbadmin=> \c - ming1
You are now connected as user "ming1".
dbadmin=> select * from mingshuo.t1;
ERROR 3580:  Insufficient privilege: USAGE on SCHEMA 'mingshuo' not granted for current user
dbadmin=> show enabled_roles;
     name      | setting
---------------+---------
 enabled roles |
(1 row)
没有role enable，所以无法查询。

dbadmin=> set role test ;
SET
dbadmin=> show enabled_roles;
     name      | setting
---------------+---------
 enabled roles | test*
(1 row)
test带了一个*，代表的是有with admin option；
dbadmin=> select * from mingshuo.t1;
 b
---
 1
(1 row)
此时可以查询。

ming1赋予ming2
dbadmin=> grant test to ming2 with admin option;
GRANT ROLE

ming2赋予ming3：
dbadmin=> grant test to ming3;
GRANT ROLE

ming1尝试回收ming3的test角色：
dbadmin=> revoke test from ming3;
REVOKE ROLE
成功！证明可以跨用户回收角色。

ming1赋予ming2，ming2赋予ming3，都带有with admin option
ming1尝试回收ming3的admin option：
dbadmin=> revoke admin option for test from ming3;
REVOKE ROLE
成功！

ming1回收ming2的test角色，不会影响ming2对ming3的授权：
dbadmin=> revoke test from ming2;
REVOKE ROLE

dbadmin=> select user_name,all_roles,default_roles from users;
 user_name |              all_roles               |            default_roles             
-----------+--------------------------------------+--------------------------------------
 dbadmin   | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 mingadmin | dbduser, dbadmin, pseudosuperuser    |
 ming1     | dbduser, test*                       |
 ming2     | dbduser                              |
 ming3     | test                                |
(5 rows)

重新赋予ming2：
dbadmin=> grant test to ming2 with admin option;
GRANT ROLE
ming3赋予test给ming4：
dbadmin=> grant test to ming4;
GRANT ROLE
ming1尝试用cascade回收ming2的test role：
dbadmin=> select user_name,all_roles,default_roles from users;
 user_name |              all_roles               |            default_roles             
-----------+--------------------------------------+--------------------------------------
 dbadmin   | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 mingadmin | dbduser, dbadmin, pseudosuperuser    |
 ming1     | dbduser, test*                       |
 ming2     | dbduser, test*                       |
 ming3     | test*                                |
 ming4     | test                                 |
(6 rows)
dbadmin=> revoke test from ming2 cascade;
REVOKE ROLE
dbadmin=> select user_name,all_roles,default_roles from users;
 user_name |              all_roles               |            default_roles             
-----------+--------------------------------------+--------------------------------------
 dbadmin   | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 mingadmin | dbduser, dbadmin, pseudosuperuser    |
 ming1     | dbduser, test*                       |
 ming2     | dbduser                              |
 ming3     | test*                                |
 ming4     | test                                 |
(6 rows)
可以看到此时不会级联回收。


删除角色：
dbadmin=> drop role test;
NOTICE 4927:  The User ming1 depends on Role test
NOTICE 4927:  The User ming2 depends on Role test
ROLLBACK 3131:  DROP ROLE failed due to dependencies
DETAIL:  Cannot drop Role test because other objects depend on it
HINT:  Use DROP ROLE ... CASCADE to remove granted roles from the dependent users/roles
因为有用户依赖关系存在，所以删除角色失败。根据提示：可以加cascade级联删除角色，但不会删除依赖的用户；
dbadmin=> drop role test cascade;
DROP ROLE
删除前：
dbadmin=> select user_name,all_roles,default_roles from users;
 user_name |              all_roles               |            default_roles             
-----------+--------------------------------------+--------------------------------------
 dbadmin   | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 mingadmin | dbduser, dbadmin, pseudosuperuser    |
 ming1     | dbduser, test*                       |
 ming2     | dbduser, test*                       |
 ming3     |                                      |
(5 rows)

删除后：
dbadmin=> select user_name,all_roles,default_roles from users;
 user_name |              all_roles               |            default_roles             
-----------+--------------------------------------+--------------------------------------
 dbadmin   | dbduser*, dbadmin*, pseudosuperuser* | dbduser*, dbadmin*, pseudosuperuser*
 mingadmin | dbduser, dbadmin, pseudosuperuser    |
 ming1     | dbduser                              |
 ming2     | dbduser                              |
 ming3     |                                      |
(5 rows)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2155948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2155948/