Greenplum Database角色及權限管理
內容目錄
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;
如果無法移除角色代表角色有自己的物件(表單)
- 先轉移角色的物件
REASSIGN OWNED 角色A TO 角色B
Drop role by 角色A
- 或連角色所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
資料庫需重新讀取參數
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
