Greenplum中角色权限及客户端认证管理
来源:互联网 发布:源码吧吧 编辑:程序博客网 时间:2024/05/04 15:36
角色权限及客户端认证管理
GP数据库逻辑结构
在GP中,Database(数据库)、Schema(模式)以及Role(角色)三者之间的关系如下所示:
- 一个数据库下可以有多个模式,一个模式只属于一个数据库。模式在GP中也被称为Namespace,不同数据库之间的模式没有关系,可以重名;
- 语言在使用之前必须创建,一个语言只属于一个数据库;
- 表、视图、索引、序列、函数必须属于一个模式;
- 一个文件空间可以有多个表空间,一个表空间只属于一个文件空间,文件空间和角色之间没有关系;
- 表空间和表时一对多的关系,一个模式下的表可以分布在多个表空间下;
- 除了文件空间之外,其他的权限管理都是通过角色来实现,在这些层次结构中,用户必须对上一层有访问权限才能够访问该层的内容;
什么是角色(role)
- Role的组成:由用户(User)和组(Group)组成;
- 跟OS的role没有关系;
- User通过Master节点登录和认证的;
- Role是定义在GPDB系统级别的;
- 初始化SUPERUSERROLE:gpadmin。
角色与权限安全的最佳实践
- 保护系统gpadmin的用户;
- 为每个登录的User分配不同的角色;
- 使用组来管理权限从而实现管理组;
- 控制具备SUPERUSER属性的User数量。
创建Role
创建用户User Role
使用CREATE ROLE创建一个User Role,语法如下所示:
template1-# ]\h create role
Command: CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [[WITH] option [ ... ]]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEEXTTABLE | NOCREATEEXTTABLE
[ ( attribute='value'[, ...] ) ]
where attributes and values are:
type='readable'|'writable'
protocol='gpfdist'|'http'|'gphdfs'
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE rolename [, ...]
| ROLE rolename [, ...]
| ADMIN rolename [, ...]
| RESOURCE QUEUE queue_name
例如: # CREATE ROLE mavshuangWITH LOGIN;
template1=# CREATE ROLE mavshuang WITH LOGIN;
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
ALTER ROLE属性
使用ALTER ROLE修改角色的属性,语法如下所示:
template1-# \h alter role
Command: ALTER ROLE
Description: change a database role
Syntax:
ALTER ROLE name RENAME TO newname
ALTER ROLE name SET config_parameter {TO | =} {value | DEFAULT}
ALTER ROLE name RESET config_parameter
ALTER ROLE name RESOURCE QUEUE {queue_name | NONE}
ALTER ROLE name [ [WITH] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEEXTTABLE | NOCREATEEXTTABLE
[ ( attribute='value'[, ...] ) ]
where attributes and values are:
type='readable'|'writable'
protocol='gpfdist'|'http'|'gphdfs'
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| CONNECTION LIMIT connlimit
| [ENCRYPTED | UNENCRYPTED] PASSWORD 'password'
| VALID UNTIL 'timestamp'
创建组Group Role
使用CREATE ROLE创建一个GroupRole:
=#CREATE ROLE admin CREATEROLE CREATEDB;
template1=# \du
List of roles
Role name | Attributes | Member of
-----------+--------------------------------------+-----------
admin | Create role, Create DB, Cannot login |
gpadmin | Superuser, Create role, Create DB |
mavshuang | |
添加或删除Member(UserRole)
使用GRANT关键字授权,使用REVOKE关键字取消授权;
template1=# \h GRANT
Command: GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCE sequencename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE langname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schemaname [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespacename [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ]
GRANT { SELECT | INSERT | ALL [PRIVILEGES] }
..ON PROTOCOL protocolname
..TO username
template1=# \h REVOKE
Command: REVOKE
Description: remove access privileges
Syntax:
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] tablename [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON SEQUENCE sequencename [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE dbname [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE langname [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schemaname [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespacename [, ...]
FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ ADMIN OPTION FOR ]
role [, ...] FROM username [, ...]
[ CASCADE | RESTRICT ]
template1=# GRANT admin TO mavshuang;
GRANT ROLE
template1=# \du
List of roles
Role name | Attributes | Member of
-----------+--------------------------------------+-----------
admin | Create role, Create DB, Cannot login |
gpadmin | Superuser, Create role, Create DB |
mavshuang | | {admin}
template1=# REVOKE admin FROM mavshuang;
REVOKE ROLE
template1=# \du
List of roles
Role name | Attributes | Member of
-----------+--------------------------------------+-----------
admin | Create role, Create DB, Cannot login |
gpadmin | Superuser, Create role, Create DB |
mavshuang | |
赋予合适的权限给GROUP ROLE
=#GRANT ALL ON TABLE mytable TO admin;
=# GRANT ALL ON SCHEMA myschema TO admin;
=# GRANT ALL ONDATABASE mydb TO admin;
获取管理属性
testdw=# \h SET ROLE
Command: SET ROLE
Description: set the current user identifier of the current session
Syntax:
SET [ SESSION | LOCAL ] ROLE rolename
SET [ SESSION | LOCAL ] ROLE NONE
RESET ROLE
管理对象权限
对象类型
权限
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
每个对象的权限必须被独立的授权
使用GRANT SQL命令给指定的Role授权一个对象
=# GRANT INSERT ON mytable TO mavshuang
使用DROP OWNED和REASSIGN OWNED命令来取消Role的Owner权限
=# REASSIGNOWNED BY mavshuang TO tom;
=# DROP OWNED BYmavshuang;
testdw=# \d 通过\d命令行查看表相关信息包括模式,名称、类型、所有者以及存储方式
List of relations
Schema | Name | Type | Owner | Storage
--------+-------------+-------+---------+---------
public | tb1_test_01 | table | gpadmin | heap
(1 row)
模拟Row或者Column级别的权限控制:本身不支持Row和Column级别的访问控制,可以通过View方式模拟。
密码加密
GPDB4.2.1版本之前,密码默认使用MD5加密,MD5加密目前可以被破解但消耗成本太高。从4.2.1版本开始,开始使用SHA-256加密,美国国家安全局使用此方法加密。
基于时间的登录认证
访问限制可以控制到具体时间点。时间约束仅仅对于设置的Role有效。比如晚上8点到凌晨6点用于跑批处理程序;早8点到下午6点用于开发用户使用等;
需要的权限:只有SUPERUSER(比如gpadmin)或者具备CREATEROLE权限是必须的。
如何添加时间约束:在CREATE ROLE或者ALTER ROLE的时候使用DENY关键字来实现:
1、某天或者某个时间访问限制;
2、一个有开始时间和结束时间的访问控制
指明日期和时间:
英文表述
数字表述
DAY 'Sunday'
DAY 0
DAY 'Monday'
DAY 1
DAY 'Tuesday'
DAY 2
DAY 'Wednesday'
DAY 3
DAY 'Thursday'
DAY 4
DAY 'Friday'
DAY 5
DAY 'Saturday'
DAY 6
TIME ‘14:00’ (24小时格式的时间)
TIME '02:00 PM' (12小时格式的时间)
TIME ‘02:00’ (24小时格式的时间) 等价于 TIME '02:00 AM'.
如何添加时间约束
指定时间间隔
通过BETWEEN和AND关键字连接两个日期/时间。
BETWEEN DAY 'Monday' AND DAY 'Tuesday'
BETWEEN DAY 'Monday' TIME '00:00' ANDDAY 'Monday' TIME '01:00'
BETWEEN DAY 'Monday' TIME '12:00 AM'AND DAY 'Tuesday' TIME '02:00 AM'
BETWEEN DAY 'Monday' TIME '00:00' ANDDAY 'Tuesday' TIME '02:00'
BETWEEN DAY 1 TIME '00:00' AND DAY 2TIME '02:00'
注意:日期间隔不能跨Saturday(周六)
Incorrect: DENY BETWEENDAY 'Saturday' AND DAY 'Sunday‘
testdw=# \! date 通过该命令查看当前日期
Mon Mar 23 21:58:25 CST 2015
删除时间约束
使用ALTER ROLE命令接DROP DENYFOR关键字
原则:有交集即移出
ALTER ROLE mavshuang DROP DENYFOR DAY ‘Monday’;
配置客户端认证
允许连接到GPDB,通过pg_hba.conf文件;该文件在Master和Segment节点上都存在;
包含每行一条记录的平面文件,格式:
Remote:host database role CIDR-address authentication-method
Unix-domain:local database role authentication-method
默认Master主机pg_hba.conf文件
允许SUPERUSER的本地连接;但不允许远程连接。
- Greenplum中角色权限及客户端认证管理
- ORACLE创建用户、角色及权限管理
- Oracle用户、权限及角色管理
- SQL Server中角色与权限管理
- SQL Server中角色与权限管理
- SQL Server中角色与权限管理
- SQL Server中角色与权限管理
- SQL Server中角色与权限管理
- SQL Server中角色与权限管理
- GreenPlum 之数据库的用户角色管理
- Greenplum中管理数据
- 权限角色管理模块
- 权限角色管理模块
- 权限角色管理模块
- Oracle10g权限角色管理
- 角色权限管理
- 权限系统--角色管理
- ASP.NET MVC:窗体身份验证及角色权限管理示例
- 二叉树的C++实现(前中后序和层次遍历,添加删除前中后序线索,中序线索下的一些操作,重构二叉树)
- spring mvc配置过程
- HDFS数据完整性设计
- J2EE企业级开发基础教程(3) -8种基本数据类型
- LESS CSS 框架简介
- Greenplum中角色权限及客户端认证管理
- TCP的定时器系列 — 零窗口探测定时器
- linux 安装配置DNS服务器
- POJ 2395 Out of Hay (MST)
- MySql远程连接无法打开解决办法
- HDU 1840 Equations【简单数学题】
- 用软碟同(UtroISO)制作install.win大于4G的启动盘
- SpringMVC 两种上传文件方法
- C语言小板凳(2)