數(shù)據(jù)庫(kù)用戶(hù)權(quán)限應(yīng)該按照用戶(hù)用途最小化。

數(shù)據(jù)庫(kù)用戶(hù)數(shù)據(jù)庫(kù)用戶(hù)類(lèi)型DBA在安裝時(shí)，Vertica數(shù)據(jù)庫(kù)自動(dòng)創(chuàng)建超級(jí)用戶(hù)。默認(rèn)是dbadmin。該用戶(hù)不可被刪除。具有如下不可撤銷(xiāo)的角色： DBADMIN DBDUSER PSEUDOSUPERUSER 這些角色可以允許dbadmin執(zhí)行所有的數(shù)據(jù)庫(kù)操作。

--創(chuàng)建其他的DBA賬戶(hù)

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

對(duì)象所有者創(chuàng)建特定數(shù)據(jù)庫(kù)對(duì)象的用戶(hù)，就是object owner。 只有object owner和superuser才能對(duì)特定數(shù)據(jù)庫(kù)對(duì)象進(jìn)行操作。 如果需要允許其他用戶(hù)允許訪問(wèn)、操作該數(shù)據(jù)庫(kù)對(duì)象，需要使用grant進(jìn)行賦權(quán)。其他(PUBLIC)所有非DBA、非object owner的賬戶(hù)，為其他賬戶(hù)。 新創(chuàng)建的用戶(hù)默認(rèn)沒(méi)有訪問(wèn)PUBLIC schema的權(quán)限，需要賦權(quán) grant usage on schema public to xxx ;

創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù)

新建的用戶(hù)默認(rèn)只具有創(chuàng)建臨時(shí)表的權(quán)限。

鎖定用戶(hù)賬號(hào)

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

\\

create user username account lock ;

設(shè)置/更改用戶(hù)密碼

alter user username identified by 'password';

數(shù)據(jù)庫(kù)角色

role是一組權(quán)限的集合，可以被賦予一個(gè)/多個(gè)用戶(hù)或其他角色。預(yù)定義數(shù)據(jù)庫(kù)角色：

角色說(shuō)明

DBADMIN創(chuàng)建用戶(hù)和角色，對(duì)其授權(quán)等等

創(chuàng)建和刪除schemas

查看所有系統(tǒng)表

查看/終止用戶(hù)session

Access all data

PSEUDOSUPERUSER繞過(guò)所有g(shù)rant/revoke authorization

創(chuàng)建schema和表

創(chuàng)建用戶(hù)和角色，并對(duì)其授權(quán)

更新用戶(hù)賬戶(hù) (密碼/鎖定等)

創(chuàng)建和刪除UDF library和function，或者其他外部procedure

DBDUSERcall Database Designer function

SYSMONITOR查看所有標(biāo)記為可監(jiān)控的系統(tǒng)表 => select * from system_tables where is_monitorable='t';

PUBLIC

查看可用/已啟用角色

-- 查看所有可用角色

SHOW AVAILABLE ROLES ;

-- 查看所有以啟用角色

SHOW ENABLED ROLES ;

-- 查看所有可用角色

select * from roles ;

-- 查看所有用戶(hù)

select * from v_catalog.users;

-- 查看當(dāng)前用戶(hù)的被授權(quán)

select * from grants ;

數(shù)據(jù)庫(kù)權(quán)限

superuser具有所有權(quán)限。

Schemas級(jí)別

操作所需權(quán)限

create schemacreate on database databaseName

drop schemaschema的創(chuàng)建者

alter schemacreate on database databaseName

Tables級(jí)別

操作所需權(quán)限

create tablecreate on schema schemaName

drop tablecreate on schema schemaName 或?usage on schema schemaName 或是 schema的創(chuàng)建者 或是表的創(chuàng)建者

truncate tableusage on schema schemaName 或是 schema的創(chuàng)建者

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 renameusage/create privileges on the schema that contains the table

alter table ... set schemacreate on new schema AND usage on old schema

selectusage on schema AND select on table

insertusage 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_partitionusage on schema AND INSERT | DELETE | UPDATE? on table

drop_statisticsusage on schema AND?INSERT | DELETE | UPDATE? on table

drop_partitionsusage on schema

Views級(jí)別

操作所需權(quán)限

create viewcreate on schema AND select on BaseObjects

drop viewusage on schema And owner of view

selectview owner must have select ... with grant options

usage on schema

select on view

Projections級(jí)別

操作所需權(quán)限

create projectionselect on theTable AND? [? usage and create on schema? | schema owner ]

auto / delayed projectionselect on theTable AND usage on schema

alter projectionusage AND create on schema

drop projectionusage on schema Or owner

External Procedures級(jí)別

操作所需權(quán)限

create proceduresuperuser

drop proceduresuperuser

executeusage on schema AND execute on procedure

Libraries級(jí)別

操作所需權(quán)限

create librarysuperuser

drop librarysuperuser

User-Defined Functions級(jí)別

UDF = ScalarUDT = TransformUDAnF= AnalyticUDAF = Aggregate

操作所需權(quán)限

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 functionusage on schema

AND

owner of function

alter function(scalar) ... rename tousgae And Create on schema

alter function(scalar) ... set schemausage on old schame

AND

create on new schema

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

Sequences級(jí)別

操作所需權(quán)限

create sequencecreate on schema

drop sequenceusage on schema Or owner

alter sequenceusgae 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級(jí)別

操作所需權(quán)限

create resource poolsuperuser

alter resource poolsuperuser:

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級(jí)別

操作所需權(quán)限

create user/profile/rolesuperuser

alter user/profile/rolesuperuser

drop user/profile/rolesuperuser

Object Visibility級(jí)別

操作所需權(quán)限

look up schemaAt least one? privilege on schema

look up object in schema or in system tablesusgae 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 poolselect on resource pool

existence of objectusage on schema

I/O Operations級(jí)別

操作所需權(quán)限

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 STDINusage on schema

insert on table

copy localusage on schema

insert on table

Comments級(jí)別

操作所需權(quán)限

comment on?{one of } :

AGGREGATE FUNCTION

ANALYTIC FUNCTION

CONSTRAINT

FUNCTION

LIBRARY

NODE

PROJECTION

PROJECTION COLUMN

SCHEMA

SEQUENCE

TABLE

TABLE COLUMN

TRANSFORM FUNCTION

VIEWobject owner or superuser

Tuning Operations級(jí)別

操作所需權(quán)限

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

總結(jié)

以上是生活随笔為你收集整理的授予数据库账号dba权限_数据库用户和权限的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。