表空间、用户、权限、角色 (数据控制语言-DCL-grant,revoke)-数据库

捐助

表空间、用户、权限、角色 (数据控制语言-DCL-grant,revoke)

2194 次浏览

2018-7-4

编辑推荐:

本文来自于csdn,介绍了登陆数据库，表空间，创建用户，给用户授权 /角色，回收用户权限/角色等。

1、登陆数据库

SQLPLUS SYS AS SYSDBA; // 登陆SYS帐户

SQLPLUS 用户名/密码@SID; // 登陆普通用户

例如：SQLPLUS LAOSAN/LAOSAN@ORCL

连接到另外一个用户

CONN 用户名/密码;

例如：CONN LAOSAN/LAOSAN;

2、表空间

ORACLE的表空间分了几种类型，临时表空间[TEMPFILE]，用户表空间 [DATAFILE]，大文件表空间[ BIGFILE ] 等，创建表空间，一般都是 DBA干的事情。

创建临时表空间，几个参数要重点注意一下

SIZE *[K,M]表空间的初始大小，单位可以是[K,M]

MAXSIZE *[K,M]表空间的最大存储值，MAXSIZE UNLIMITED 是指表空间的大小不受限制。

AUTOEXTEND ON NEXT *[K,M] 设置表空间自动扩展

EXTENT MANAGEMENT LOCAL本地管理表空间

EXTENT是"区间"的意思,在ORACLE数据库中：EXTENT MANAGEMENT 有两种方式 EXTENT MANAGEMENT LOCAL(本地管理); EXTENT MANAGEMENT DICTIONARY(数据字典管理)。默认的是LOCAL本地管理表空间有自动分配(AUTOALLOCATE)和统一大小分配(UNIFORM)两种空间分配方式，自动分配方式(AUTOALLOCATE)是由系统来自动决定EXTENTS大小，而统一大小分配(UNIFORM)则是由用户指定EXTENTS大小。这两种分配方式都提高了空间管理效率。UNIFORM：默认为1M大小，在TEMP表空间里为默认的，但是不能被应用在UNDO表空间。

--创建

CREATE TEMPORARY TABLESPACE temptest

tempFile

'E:\database\temptest01.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M,

'E:\database\temptest02.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M,

'E:\database\temptest03.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE 1024M EXTENT MANAGEMENT LOCAL ;

-- TEMPORARY在创建临时表空间时才需要，例如创建用户表空间如下：

CREATE TABLESPACE usertest

dataFile

'E:\database\usertest01.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED ,

'E:\database\usertest02.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED ,

'E:\database\usertest03.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ;

--增加表空间文件、修改

ALTER TABLESPACE temptest

ADD tempFile

'E:\database\ temptest04.dbf' SIZE 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED;

--删除

DROP TABLESPACE temptest INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

--查看表空间

SELECT * FROM DBA_TABLESPACES;

查看当前用户的缺省表空间

SELECT USERNAME,DEFAULT_TABLESPACE FROM USER_USERS;

3、用户

创建用户

创建用户时，需要注意如下几个参数

IDENTIFIED BY *** 用户的密码

DEFAULT TABLESPACE *** 默认用户表空间

TEMPORARY TABLESPACE *** 默认临时空间

CREATE USER laosan

IDENTIFIED BY ls0123

DEFAULT TABLESPACE usertest

TEMPORARY TABLESPACE temptest;

CREATE USER 用户名 IDENTIFIED BY 密码; --如果不指定表空间，默认创建在SYSTEM表空间下

例如：CREATE USER LAOSAN IDENTIFIED BY LAOSAN DEFAULT TABLESPACE LAOSAN;

修改密码

ALTER USER 用户名 IDENTIFIED BY 新密码;

例如：ALTER USER laosan IDENTIFIED BY ls4567;

修改用户缺省表空间

ALTER USER laosan DEFAULT TABLESPACE user_data;

修改用户临时表空间

ALTER USER laosan TEMPORARY TABLESPACE temp_data;

将用户加锁

ALTER USER laosan ACCOUNT LOCK; // 加锁

ALTER USER laosan ACCOUNT UNLOCK; // 解锁

删除用户

DROP USER 用户名;

例如：DROP USER laosan;

删除用户以及跟用户关联的对象

DROP USER LAOSAN CASCADE;

查看所有用户

SELECT * FROM ALL_USERS;

查看当前用户

SHOW USER;

连接到另外一个用户

CONN 用户名/密码;

例如：CONN laosan/laosan;

查询用户会话信息

SELECT USERNAME, SID, SERIAL#, MACHINE FROM V$SESSION;

删除用户会话信息

ALTER SYSTEM KILL SESSION 'sid, serial#';

查询用户SQL语句

SELECT USER_NAME, SQL_TEXT FROM V$OPEN_CURSOR;

4、给用户授权/角色

Oracle的特殊权限

系统权限UNLIMITED TABLESPACE是隐含在DBA, RESOURCE角色中的一个系统权限. 当用户得到DBA或RESOURCE的角色时, UNLIMITED TABLESPACE系统权限也隐式受权给用户.

系统权限UNLIMITED TABLESPACE不能被授予ROLE, 可以被授予用户.

系统权限UNLIMITED TABLESPACE不会随着RESOURCE, DBA被授予ROLE而授予给用户.

Oracle特殊角色

DBA角色，是授权数据库管理员的权限

CONNECT角色，是授予最终用户的典型权利，最基本的一个(CREATE SESSION)

RESOURCE角色，是授予开发人员的默认有八个权限(CREATE SEQUENCE,CREATE TRIGGER,CREATE CLUSTER,CREATE PROCEDURE,CREATE TYPE,CREATE OPERATOR,CREATE TABLE,CREATE INDEXTYPE)

EXP_FULL_DATABASE角色，拥有导出数据库的权限

IMP_FULL_DATABASE角色，拥有导入数据库的权限

语法：

GRANT 权限类型, 权限类型 TO 用户名1, 用户名2;

GRANT 权限类型, 权限类型表名 TO 用户名1, 用户名2;

例如：GRANT CONNECT, RESOURCE TO LAOSAN;

常用的系统权限集合有以下三个:

CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)

常用的数据对象权限有以下五个:

ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名, DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名

GRANT ALL TO PUBLIC;//授予所有权限(ALL)给所有用户(PUBLIC)

--授权

GRANT

CONNECT,

RESOURCE,

--DBA,

--UNLIMITED TABLESPACE,

CREATE SESSION,

CREATE ANY SEQUENCE,

CREATE ANY TABLE,

CREATE ANY VIEW ,

CREATE ANY INDEX,

CREATE ANY PROCEDURE,

CREATE ANY DIRECTORY,

ALTER SESSION,

ALTER ANY SEQUENCE,

ALTER ANY TABLE,

--ALTER ANY VIEW , --不能修改视图

ALTER ANY INDEX,

ALTER ANY PROCEDURE,

--ALTER ANY DIRECTORY, --不能修改目录

--DROP SESSION, --不能删除SESSION

DROP ANY SEQUENCE,

DROP ANY TABLE,

DROP ANY VIEW ,

DROP ANY INDEX,

DROP ANY PROCEDURE,

DROP ANY DIRECTORY,

SELECT ANY TABLE,

SELECT ANY DICTIONARY,

INSERT ANY TABLE,

UPDATE ANY TABLE,

DELETE ANY TABLE,

DEBUG ANY PROCEDURE,

DEBUG CONNECT SESSION,

EXP_FULL_DATABASE,

IMP_FULL_DATABASE

TO laosan;

5、回收用户权限/角色

REVOKE 权限类型, 权限类型 FROM 用户名;

REVOKE 权限类型, 权限类型表名 FROM 用户名1, 用户名2;

6、查询用户权限/角色

以下的查询中，基本都要以DBA的角色进行查询

查看用户拥有那些角色

SELECT * FROM DBA_ROLE_PRIVS A WHERE A.GRANTEE='laosan';

--或

SELECT * FROM DBA_SYS_PRIVS A WHERE A.GRANTEE='laosan';

查看角色拥有那些权限

SELECT ROLE, PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE'; --RESOURCE，CONNECT，DBA

--或

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

7、权限传递

即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：

GRANT ALERT TABLE ON tablename TO laosan WITH ADMIN OPTION;//关键字 WITH ADMIN OPTION

GRANT ALERT TABLE ON tablename TO laosan WITH GRANT OPTION;//关键字 WITH GRANT OPTION效果和ADMIN类似

WITH ADMIN OPTION 用于系统权限授权，WITH GRANT OPTION 用于对象授权

但给一个用户授予系统权限带上WITH ADMIN OPTION 时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限CREATE SESSION WITH ADMIN OPTION,然后A又把CREATE SESSION权限授予B,但管理员收回A的CREATE SESSION权限时，B依然拥有CREATE SESSION的权限，但管理员可以显式收回B CREATE SESSION的权限，即直接REVOKE CREATE SESSION FROM B.

而WITH GRANT OPTION用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用WITH GRANT OPTION授权的用户对象权限时，权限会因传播而失效，如GRANT SELECT ON TABLE WITH GRANT OPTION TO A,A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。

8、角色

系统预定义角色

CONNECT, RESOURCE, DBA 这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。

DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE 这些角色主要用于访问数据字典视图和包。

EXP_FULL_DATABASE， IMP_FULL_DATABASE 这两个角色用于数据导入导出工具的使用。

AQ_USER_ROLE， AQ_ADMINISTRATOR_ROLE AQ:Advanced Query。这两个角色用于oracle高级查询功能。

SNMPAGENT 用于oracle enterprise manager和Intelligent Agent

RECOVERY_CATALOG_OWNER 用于创建拥有恢复库的用户。

HS_ADMIN_ROLE 使用Oracle的异构服务功能的一个DBA需要这个角色来访问数据字典相应的表。

角色即权限的集合，可以把一个角色授予给用户

CREATE ROLE myrole;//创建角色

GRANT CREATE SESSION TO myrole;//将创建SESSION的权限授予MYROLE

GRANT myrole TO laosan;//授予laosan用户MYROLE的角色

DROP ROLE myrole;//删除角色

修改指定用户，设置其默认角色

ALTER USER user1 DEFAULT ROLE role1;

ALTER USER user1 DEFAULT ROLE ALL EXCEPT role1;

角色的权限查询

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='角色名';

创建带有口令的角色(在生效带有口令的角色时必须提供口令)

CREATE ROLE role1 IDENTIFIED BY password1;

修改角色：是否需要口令

ALTER ROLE role1 NOT IDENTIFIED;

ALTER ROLE role1 IDENTIFIED BY password1;

查询当前用户被授予的角色

SELECT * FROM SESSION_ROLES ORDER BY ROLE;

2194 次浏览