达梦8数据库用户、权限、角色介绍

用户：

1.创建表空间

create tablespace “DM” datafile ‘/dm8/dmdata/DAMENG/DM01.DBF’ size 500 autoextend on maxsize 67108863, ‘/dm8/dmdata/DAMENG/DM02.DBF’ size 500 autoextend on maxsize 67108863 CACHE = NORMAL;

2.删除表空间

drop tablespace “DM”;

3.扩容表空间

alter tablespace DM resize datafile ‘/dm8/dmdata/DAMENG/DM01.DBF’ to 600;

4.创建用户指定表空间

create user dm identified by “dm@123456” default tablespace DM;

5.修改用户密码

alter user dm identified by DMdm123456;

6.用户空间限制

ALTER USER DM DISKSPACE LIMIT 50;

7.用户空间无限制

ALTER USER DM DISKSPACE UNLIMITED;

8.用户状态锁定

alter user DM account lock;

9.用户解锁

alter user DM account unlock;

角色：

1.查看当前角色

select * from dba_role_privs where grantee=’DM’;

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
——- ———— ———— ————
DM PUBLICNNULL
DM SOI NNULL

2.查看角色权限

SQL> set pages 200
SQL> select * from DBA_SYS_PRIVS where GRANTEE=’PUBLIC’;

GRANTEE PRIVILEGEADMIN_OPTION
——- ———————— ————
PUBLIC INSERT TABLE NO
PUBLIC UPDATE TABLE NO
PUBLIC DELETE TABLE NO
PUBLIC SELECT TABLE NO
PUBLIC REFERENCES TABLE NO
PUBLIC GRANT TABLE NO
PUBLIC INSERT VIEW NO
PUBLIC UPDATE VIEW NO
PUBLIC DELETE VIEW NO
PUBLIC SELECT VIEW NO
PUBLIC GRANT VIEWNO
PUBLIC EXECUTE PROCEDURENO
PUBLIC GRANT PROCEDURE NO
PUBLIC SELECT SEQUENCE NO
PUBLIC GRANT SEQUENCENO
PUBLIC EXECUTE PACKAGE NO
PUBLIC GRANT PACKAGENO
PUBLIC SELECT ANY DICTIONARYNO
PUBLIC SELECT MATERIALIZED VIEW NO
PUBLIC GRANT DOMAIN NO
PUBLIC USAGE DOMAIN NO
PUBLIC DUMP TABLENO

22 rows got

3.用户赋权

grant RESOURCE to DM;

4.用户权限回收

5.用户单独赋权

赋权：

建表测试：

建议用户创建完毕后赋权角色RESOURCE，看下此角色包含权限

SQL> select * from DBA_SYS_PRIVS where GRANTEE =’RESOURCE’;

GRANTEE PRIVILEGEADMIN_OPTION
——– ———————— ————
RESOURCE CREATE SCHEMANO
RESOURCE CREATE TABLE NO
RESOURCE CREATE VIEW NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGERNO
RESOURCE CREATE INDEX NO
RESOURCE CREATE CONTEXT INDEX NO
RESOURCE CREATE LINK NO
RESOURCE CREATE PACKAGENO
RESOURCE CREATE SYNONYMNO
RESOURCE CREATE PUBLIC SYNONYMNO
RESOURCE INSERT TABLE NO
RESOURCE UPDATE TABLE NO
RESOURCE DELETE TABLE NO
RESOURCE SELECT TABLE NO
RESOURCE REFERENCES TABLE NO
RESOURCE GRANT TABLE NO
RESOURCE INSERT VIEW NO
RESOURCE UPDATE VIEW NO
RESOURCE DELETE VIEW NO
RESOURCE SELECT VIEW NO
RESOURCE GRANT VIEWNO
RESOURCE EXECUTE PROCEDURENO
RESOURCE GRANT PROCEDURE NO
RESOURCE SELECT SEQUENCE NO
RESOURCE GRANT SEQUENCENO
RESOURCE EXECUTE PACKAGE NO
RESOURCE GRANT PACKAGENO
RESOURCE SELECT ANY DICTIONARYNO
RESOURCE CREATE MATERIALIZED VIEW NO
RESOURCE SELECT MATERIALIZED VIEW NO
RESOURCE CREATE DOMAINNO
RESOURCE GRANT DOMAIN NO
RESOURCE USAGE DOMAIN NO
RESOURCE DUMP TABLENO
RESOURCE CREATE PARTITION GROUPNO
RESOURCE USAGE PARTITION GROUPNO

38 rows got

基本权限都包含的。

在线服务平台地址：

https://eco.dameng.com