Greenplum Database角色及權限管理

How to connect R with Tableau
2018-02-01
2018.01.26 Docker Containerized Platforms
2018-02-06

Ren

創建一個角色Ren,使其能夠存取資料庫gpadmin下的schema_1.table_1表單

Database gpadmin

tablename  table_1

schemaname schema_1

方法一.創建使用者 並給予權限

psql -d gpadmin

建立使用者給予登入權限

=#Create role Ren with login;

配置密碼

=#Alter role Ren with password ‘ren’;

 

給予Ren角色存取schema_1.table_1 的權限

=#grant ALL on table schema_1.table_1 to Ren;

=#grant ALL on schema schema_1 to Ren;

 

給予Ren角色在database下建立schema的權限

=#grant ALL on database gpadmin to Ren;

————————————————————————-

可以指定範圍權限

=#grant select on table schema_1.table_1 to Ren;

=#grant insert on table schema_1.table_1 to Ren;

可移除指定範圍權限

=#revoke select on table schema_1.table_1 from Ren;

可移除所有權限

=#REVOKE ALL PRIVILEGES ON schema_1.table_1 FROM Ren;

—————————————————————————–

方法二.創立群組,直接給予使用者(群組權限)

=#Create role Rengroup CREATEROLE CREATEDB;  (群組不給予登入權限)

=#grant ALL on table schema_1.table_1 to Rengroup;

=#grant ALL on schema schema_1 to Rengroup;

=#grant ALL on database gpadmin to Rengroup;

給予角色群組權限

=#grant Rengroup to Ren,Alex;

移除角色群組權限

=#revoke Rengroup FROM REN;

如果無法移除角色代表角色有自己的物件(表單)

  1. 先轉移角色的物件

REASSIGN OWNED 角色A TO 角色B

Drop role by 角色A

  1. 或連角色所OWNED物件也移除

DROP OWNED BY 角色A

DROP ROLE BY 角色A

三.修改客戶端訪問權限管理(/data/master/gpseg-1/pg_hba.conf)

vi pg_hba.conf

最底下加入

遠端或本地  資料庫  使用者  md5驗證

local       gpadmin   Ren            md5

host        gpadmin   Ren    遠端來源IP  md5

Greenplum Database角色及權限管理1

資料庫需重新讀取參數

gpstop -u

四.登入測試(登入gpadmin資料庫,使用者為Ren)

psql -d gpadmin -U Ren

select * from schema_1.table_1;

五.角色權限請參考
Attributes

Description

SUPERUSER | NOSUPERUSER

Determines if the role is a superuser. A superuser always bypasses all access permission checks within the database and has full access to everything. Superuser status is dangerous and should be used only when really needed. You must yourself be a superuser to create a new superuser. NOSUPERUSER is the default.

CREATEDB | NOCREATEDB

Determines if the role is allowed to create databases. NOCREATEDB is the default.

CREATEROLE | NOCREATEROLE

Determines if the role is allowed to create and manage other roles. NOCREATEROLE is the default.

INHERIT | NOINHERIT

Determines whether a role inherits the privileges of roles it is a member of. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. INHERIT is the default.

LOGIN | NOLOGIN

Determines whether a role is allowed to log in. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges (groups). NOLOGIN is the default.

CONNECTION LIMIT connlimit

If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit.

PASSWORD ‘password

Sets the role’s password. If you do not plan to use password authentication you can omit this option. If no password is specified, the password will be set to null and password authentication will always fail for that user. A null password can optionally be written explicitly as PASSWORD NULL.

ENCRYPTED | UNENCRYPTED

Controls whether the password is stored encrypted in the system catalogs. The default behavior is determined by the configuration parameter password_encryption (currently set to MD5). If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.

VALID UNTIL ‘timestamp

Sets a date and time after which the role’s password is no longer valid. If omitted the password will be valid for all time.

RESOURCE QUEUE queue_name

Assigns the role to the named resource queue for workload management. Any statement that role issues is then subject to the resource queue’s limits. Note that the RESOURCE QUEUE attribute is not inherited; it must be set on each user-level (LOGIN) role.

DENY {deny_interval | deny_point}

Restricts access during an interval, specified by day or day and time. For more information see “Time-based Authentication” on page 35.

六.物件權限請參考

Object Type

Privileges

Tables, Views, Sequences

SELECT

INSERT

UPDATE

DELETE

RULE

ALL

External Tables

SELECT

RULE

ALL

Databases

CONNECT

CREATE

TEMPORARY | TEMP

ALL

Functions

EXECUTE

Procedural Languages

USAGE

Schemas

CREATE

USAGE

ALL

七.參考原廠文件

GPDB43AdminGuide.pdf

Chapter 12.Managing Roles and Privileges