Skip to end of metadata
Go to start of metadata

数据库用户权限应该按照用户用途最小化。 

数据库用户

  1. 数据库用户类型
    DBA
    在安装时，Vertica数据库自动创建超级用户。默认是dbadmin。该用户不可被删除。具有如下不可撤销的角色： DBADMIN DBDUSER PSEUDOSUPERUSER 这些角色可以允许dbadmin执行所有的数据库操作。

    --创建其他的DBA账户
dbadmin=> create user dbadmin2 ;
CREATE USER 
dbadmin=> grant dbduser,dbadmin,pseudosuperuser to dbadmin2 ;
GRANT ROLE
dbadmin=> \c - dbadmin2
You are now connected as user "dbadmin2". 
dbadmin=> set role dbadmin,dbduser,pseudosuperuser;
SET 

dbadmin=> show enabled roles;
name | setting
--------------+----------------------------------
enabled roles | dbduser, dbadmin, pseudosuperuser

    对象所有者
    创建特定数据库对象的用户，就是object owner。 只有object owner和superuser才能对特定数据库对象进行操作。 如果需要允许其他用户允许访问、操作该数据库对象，需要使用grant进行赋权。
    其他（PUBLIC）
    所有非DBA、非object owner的账户，为其他账户。 新创建的用户默认没有访问PUBLIC schema的权限，需要赋权 grant usage on schema public to xxx ;


  2. 创建数据库用户
    新建的用户默认只具有创建临时表的权限。

  3. 锁定用户账号

    alter user username account lock \[ unlock \] ;
\\
create user username account lock ;


  4. 设置/更改用户密码

    alter user username identified by 'password';


数据库角色

role是一组权限的集合，可以被赋予一个/多个用户或其他角色。

  • 预定义数据库角色：

    角色说明
    DBADMIN创建用户和角色，对其授权等等
    创建和删除schemas
    查看所有系统表
    查看/终止用户session
    Access all data
    PSEUDOSUPERUSER绕过所有grant/revoke authorization
    创建schema和表
    创建用户和角色，并对其授权
    更新用户账户 （密码/锁定等）
    创建和删除UDF library和function，或者其他外部procedure
    DBDUSERcall Database Designer function
    SYSMONITOR查看所有标记为可监控的系统表 => select * from system_tables where is_monitorable='t';
    PUBLIC

  • 查看可用/已启用角色

    -- 查看所有可用角色
SHOW AVAILABLE ROLES ;
-- 查看所有以启用角色
SHOW ENABLED ROLES ;
-- 查看所有可用角色
select * from roles ;
-- 查看所有用户
select * from v_catalog.users;
-- 查看当前用户的被授权
select * from grants ;

数据库权限


superuser具有所有权限。


  • Schemas级别

    操作所需权限
    create schemacreate on database databaseName
    drop schema schema的创建者
    alter schema create on database databaseName

  • Tables级别

    操作所需权限
    create tablecreate on schema schemaName
    drop table create on schema schemaName 或 usage on schema schemaName 或是 schema的创建者 或是表的创建者
    truncate table usage on schema schemaName 或是 schema的创建者
    alter table add/drop/rename/alter-type columnusage on schema schemaName
    alter table add/drop constraintusage on schema schemaName
    alter table partition (reorganize) usage on schema schemaName
    alter table rename usage/create privileges on the schema that contains the table
    alter table ... set schema create on new schema AND usage on old schema
    select usage on schema AND select on table 
    insert usage on schema AND insert on table 
    deleteusage on schema AND DELETE/SELECT on table
    updateusage on schema AND UPDATE/SELECT on table
    REFERENCESusage on schema AND REFERENCES to create foreign key constraints that reference this table

    analyze_statistics

    analyze_statistics_partition

    		usage on schema AND INSERT | DELETE | UPDATE  on table 
    drop_statisticsusage on schema AND INSERT | DELETE | UPDATE  on table 
    drop_partitionsusage on schema 

  • Views级别

    操作所需权限
    create viewcreate on schema AND select on BaseObjects
    drop view usage on schema And owner of view
    select

    view owner must have select ... with grant options 

    usage on schema 

    select on view 

  • Projections级别

    操作所需权限
    create projectionselect on theTable AND  [  usage and create on schema  | schema owner ]
    auto / delayed projectionselect on theTable AND usage on schema
    alter projection

    usage AND create on schema

    drop projectionusage on schema Or owner

  • External Procedures级别

    操作所需权限
    create proceduresuperuser
    drop proceduresuperuser
    executeusage on schema AND execute on procedure

  • Libraries级别

  • 操作所需权限
    create librarysuperuser
    drop librarysuperuser

  • User-Defined Functions级别
    UDF = Scalar
    UDT = Transform
    UDAnF= Analytic
    UDAF = Aggregate

    操作所需权限

    create function(SQL)
    create function(scalar)
    create transform function
    create analytic function(UDFnF)
    create aggregate function(UDAF)

    create on schema 

    AND

    usage on baseLibrary

    drop function
    drop transform function
    drop analytic function
    drop aggregate function

    usage on schema

    AND

    owner of function

    alter function(scalar) ... rename to usgae And Create on schema
    alter function(scalar) ... set schema

    usage on old schame 

    AND

    create on new schema

    execute(SQL/UDF/UDT/ADAF/UDAnF） functionusage on schema AND execute on function

  • Sequences级别

    操作所需权限
    create sequencecreate on schema
    drop sequenceusage on schema Or owner
    alter sequence usgae AND create on schema 
    alter sequence ... set schemausage on old schema AND create on new schema 
    currval / nextvalusage on schema AND select on sequence

  • Resource Pools级别

    操作所需权限
    create resource pool superuser
    alter resource pool 

    superuser:
    can modify these : MAXMEMORYSIZE PRIORITY QUEUETIMEOUT

    normal-user:
    update on resource pool ( can modify these : PLANNEDCONCURRENCY SINGLEINITIATOR MAXCONCURRENCY)

    set session resource poolusage on resource_pool and  user can change their own resource pool 
    drop resource poolsuperuser

  • Users/Profiles/Roles级别

    操作所需权限
    create user/profile/rolesuperuser
    alter user/profile/rolesuperuser
    drop user/profile/rolesuperuser

  • Object Visibility级别

    操作所需权限
    look up schemaAt least one  privilege on schema
    look up object in schema or in system tables 

    usgae on schema 

    AND

    at least on privilege on any of the following objects : table/view/function/procedure/sequence

    look up projectionat least on privilege on All anchor tables And usage on the schema of all anchor tables
    look up resource pool select on resource pool
    existence of object usage on schema 

  • I/O Operations级别

    操作所需权限
    connect to verticadisconnectnone
    export to verticaselect on source table 
    usage on source schema 
    insert on destination table
    usage on destination schema
    copy from verticausage on source/destination schema
    select on source table
    insert on destination table
    copy from filesuperuser
    copy from STDIN

    usage on schema 
    insert on table

    copy local

    usage on schema 
    insert on table

  • Comments级别

    操作所需权限

    comment on {one of } :

    AGGREGATE FUNCTION
    ANALYTIC FUNCTION
    CONSTRAINT
    FUNCTION
    LIBRARY
    NODE
    PROJECTION
    PROJECTION COLUMN
    SCHEMA
    SEQUENCE
    TABLE
    TABLE COLUMN
    TRANSFORM FUNCTION
    VIEW

    		object owner or superuser

  • Tuning Operations级别

    操作所需权限
    profileSame privileges required to run the query being profiled
    explainSame privileges required to run the query for which you use the EXPLAIN keyword

Access策略

列 access polices
行 access polices
Access Policies and DML Operations
Access Policies and Query Optimization
管理Access policies

  • No labels