Oracle 用户/权限/角色管理

1. 用户

1.1. 用户的创建和删除

1.1.1. 创建用户

create user user identified {by password | externally}
[ default tablespace tablespace ]
[ temporary tablespace tablespace ]
[ quota {integer [k | m ] | unlimited } on tablespace [ quota {integer [k | m ] | unlimited } on tablespace]...]
[ password expire ]
[ account { lock | unlock }]
[ profile { profile | default }]

• IDENTIFIED BY password：指定用户的密码。也可以使用EXTERNALLY来指定用户通过外部身份验证进行认证。

ALTER USER user IDENTIFIED BY new_password;

• DEFAULT TABLESPACE tablespace：指定用户的默认表空间，即用户创建的对象（如表、索引等）所使用的表空间。
• TEMPORARY TABLESPACE tablespace：指定用户的临时表空间，用于用户的临时数据和排序操作。

-- 查看默认和临时表空间：
SELECT default_tablespace, temporary_tablespace FROM dba_users WHERE username = 'test_user';

• QUOTA {integer [K | M ] | UNLIMITED } ON tablespace：指定用户在指定表空间上的配额，即用户在该表空间上可以使用的存储空间大小。可以使用整数（单位为KB或MB）或者UNLIMITED表示无限制。

-- 修改配额：
ALTER USER user_name QUOTA {integer [K | M ] | UNLIMITED } ON tablespace_name;
-- 查看表空间配额：
SELECT tablespace_name, max_bytes/1024/1024 AS quota_mb FROM dba_ts_quotas WHERE username = 'test_user';-- 回收表空间配额
revoke unlimited tablespace from hefei01; 
alter user hefei01 quota 0 on users;   --【不限制】

• PASSWORD EXPIRE：指定用户的密码在创建后立即过期，要求用户在首次登录后修改密码。
• ACCOUNT { LOCK | UNLOCK }：指定用户的账号状态，可以将账号锁定或解锁。

ALTER USER user ACCOUNT { LOCK | UNLOCK }

• PROFILE { profile | DEFAULT }：指定用户使用的配置文件（profile），配置文件中包含了用户的资源限制和密码策略等设置。也可以使用DEFAULT表示使用默认配置文件。

1.1.2. 删除用户

DROP USER username [CASCADE];

CASCADE选项将删除用户及其相关的对象（如表、视图等）。

注：如果用户当前正连接到数据库，则不能删除该用户。必须先用ALTER SYSTEM KILL SESSION 语句终止它的会话，然后再用DROP USER 将用户删除。

1.2. 权限的授予和回收

1.2.1. 授予权限

语法：

GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

(1)创建session的权限给username（create session就是允许使用这个用户在服务器上创建session。通俗的说，就是允许这个用户登录。）

grant create session to username;

(2)没有限制的表空间；

SQL> grant unlimited tablespace to username;

(3)如果对权限要求不是很严格的话，直接赋予管理员权限；

SQL> grant dba to username;

1.2.2. 收回权限

语法：

REVOKE CONNECT, RESOURCE FROM 用户名;    REVOKE SELECT ON 表名 FROM 用户名;     REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2 

--收回查询表的权限

revoke select on demo from username;
revoke all on demo from username;

--查询一个用户拥有的对象权限

select table_name,privilege from dba_tab_privs where grantee='username';
SELECT TABLE_NAME,PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE='BOB';

--查询一个用户拥有的系统权限

select * from dba_sys_privs where grantee='username';

--当前会话有效的系统权限

SQL> select * from session_privs;

1.2.3. 权限传递

1.2.3.1. with admin option

with admin option：针对用户授予系统权限，可以让被授予用户继续授予其他用户权限，但是回收时不会级联回收

使用SYS用户做以下操作：

创建两个用户A和B并赋予密码,并且创建时两个用户均为非锁定状态：

CREATE USER a IDENTIFIED BY oracle ACCOUNT UNLOCK;

CREATE USER b IDENTIFIED BY oracle ACCOUNT UNLOCK;

检查当前用户系统权限：

select grantee,privilege from dba_sys_privs where grantee='A';

select grantee,privilege from dba_sys_privs where grantee='B';

SYS赋予A用户CREATE SESSION系统权限的同时并赋予WITH ADMIN OPTION;权限

GRANT CREATE SESSION TO a WITH ADMIN OPTION;

检查A用户系统权限.

select grantee,privilege from dba_sys_privs where grantee='A';

使用A用户登录到ORACLE数据库中：

conn a/oracle

使用A用户赋予B用户create session权限

GRANT CREATE SESSION TO b;

登录回SYS用户，用SYS用户检查B用户的系统权限

select grantee,privilege from dba_sys_privs where grantee='B';

使用SYS用户收回A用户的CREATE SESSION系统权限。

REVOKE CREATE SESSION FROM a;

检查A用户和B用户现在的系统权限：

select grantee,privilege from dba_sys_privs where grantee='A';

select grantee,privilege from dba_sys_privs where grantee='B';

我们发现A用户的CREATE SESSION系统权限被收回了，但是B用户的CREATE SESSION系统权限没有被收回。

因此我们得出结论，被WITH ADMIN OPTION权限赋予的系统权限，在赋权账户的该系统权限被收回时，被WITH ADMIN OPTION权限授予用户的系统权限并不被级联收回。 而且可以跨用户收回！！！

1.2.3.2. with grant option

with grant option：针对用户授予对象权限，可以让被授予用户继续授予，但是回收时会产生联级效应。

WITH GRAT OPTION实验：

首先使用sys用户赋予a b用户CREATE SESSION系统权限：

GRANT CREATE SESSION TO a;

GRANT CREATE SESSION TO b;

先检查A用户和B用户的对象权限：

SET LINES 300

SET PAGES 20

COL GRANTEE FOR A20

COL OWNER FOR A20

COL TABLE_NAME FOR A20

COL PRIVILEGE FOR A20

select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs WHERE grantee='A';

select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs WHERE grantee='B';

切换到HR用户：

conn hefei/hefei

使用HR用户把TF_DICT_ORDER表的SELECT ON对象权限赋予给A用户，并且需要对A再另赋WITH GRANT OPTION权限：

GRANT SELECT ON TF_DICT_ORDER TO a WITH GRANT OPTION;

赋权结束后检查A用户权限：

select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs WHERE grantee='A';

切换到A用户下，并使用A用户对B用户赋予SELECT ON EMOLOYEES权限。

conn a/oracle

GRANT SELECT ON HEFEI.TF_DICT_ORDER TO b;

这里需要注意的是由于目前用户不是 hefei 用户，所以对a用户对b用户赋予SELECT ON TF_DICT_ORDER对象权限时，要特别指定表的归属用户 hefei。

检查B用户的对象权限:

select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs WHERE grantee='B';

切换回HR用户：

conn hefei/hefei

从A用户上收回SELECT ON TF_DICT_ORDER对象权限。

REVOKE SELECT ON TF_DICT_ORDER FROM a;

检查A用户和B用户的对象权限：

select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs WHERE grantee='A';

select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from user_tab_privs WHERE grantee='B';

我们发现A用户和B用户的SELECT ON EMPLOYEES对象权限已都被收回了。

因此我们得出结论，被WITH GRANT OPTION权限赋予的对象权限，在赋权用户的该对象权限被收回时，被WITH GRANT OPTION授予用户的对象权限也一并被级联收回。

1.3. 用户profile

1.3.1. 创建profile

CREATE PROFILE devp_session LIMIT 
CPU_PER_SESSION 5000 
SESSIONS_PER_USER 2 
CONNECT_TIME 30 
IDLE_TIME 10;

分配概要文件给用户devp

ALTER USER devp PROFILE devp_session; 

1.3.2. profile常见参数：

参数	说明	默认值
failed_login_attempts	允许的输入错误口令的次数	10次
PASSWORD_LOCK_TIME	账户被锁定的天数	1
PASSWORD_life_TIME	口令的有效期	180天
PASSWORD_grace_TIME	口令失效的宽限期	7天
PASSWORD_reuse_TIME	重用口令之前口令需要改变的次数	UNLIMITED
PASSWORD_reuse_MAX	重复使用口令之前必须对口令进行修改的次数	UNLIMITED
IDLE_TIME	允许的最大空闲时间	UNLIMITED
CONNECT_TIME	允许的最大连接时间	UNLIMITED
SESSIONS_PER_USER	允许的最大并发会话数	UNLIMITED
CPU_PER_SESSION	用户每个会话允许使用的CPU时间	UNLIMITED
logical_reads_per_session	用户每个会话允许的逻辑读取次数	UNLIMITED
LOGICAL_READS_PER_CALL	用户每次调用允许的逻辑读取次数	UNLIMITED

1.4. 与用户、权限、角色相关的动态性能视图和数据字典

1.4.1. DBA_USERS：

包含有关所有数据库用户的信息，如用户名、默认表空间、临时表空间、账户状态等。

desc DBA_USERS;

SELECT * FROM DBA_USERS;

SELECT username, account_status, default_tablespace FROM dba_users;

select username from dba_users where username='HEFEI';

1.4.2. DBA_ROLES：

包含有关所有角色的信息，如角色名、角色类型等。

SELECT * FROM DBA_ROLES;

1.4.3. DBA_ROLE_PRIVS：

显示用户与角色之间的关系，即哪些用户被授予了哪些角色。

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HEFEI';

1.4.4. DBA_TAB_PRIVS：

显示用户对表的权限信息，包括授予的权限类型和授权者。

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'HEFEI';

1.4.5. DBA_SYS_PRIVS：

显示用户的系统级权限信息，如SELECT ANY TABLE、CREATE TABLE等。

desc dba_sys_privs;

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HEFEI';

1.4.6. 案例

create user worddb identified by "!@#123";
grant connect,resource,unlimited tablespace to worddb;
alter user worddb default tablespace tbs_data;grant select on HEFEI.UR_INDUSTRYPORT_INFOF to worddb;set line 222
set pagesize 99
col grantee for a20
col owner for a20
col table_name for a20
col grantor for a20
col privilege for a20
-- 这个用户被授予什么角色
select grantee,granted_role from dba_role_privs where grantee in ('WORDDB') order by 1;  
-- 这个用户有什么权限
Select grantee,privilege from dba_sys_privs where grantee in ('WORDDB') order by 1;   
-- 这个用户对表有什么权限
Select grantee,privilege from dba_tab_privs where grantee in ('WORDDB') order by 1;  SQL> select grantee,granted_role from dba_role_privs where grantee in ('WORDDB') order by 1;-- GRANTEE              GRANTED_ROLE
------------------ --------------------
-- WORDDB               CONNECT
-- WORDDB               RESOURCESQL> Select grantee,privilege from dba_sys_privs where grantee in ('WORDDB') order by 1;-- GRANTEE              PRIVILEGE
------------------ ----------------------------------------
-- WORDDB               UNLIMITED TABLESPACEgrant select on HEFEI.UR_INDUSTRYPORT_INFO to worddb;
Select * from dba_tab_privs where grantee in ('WORDDB') order by 1; -- GRANTEE              OWNER                TABLE_NAME           GRANTOR              PRIVILEGE            GRA HIE COM TYPE                     INH
------------------ -------------------- -------------------- -------------------- -------------------- --- --- --- ------------------------ ---
-- WORDDB               HEFEI                UR_INDUSTRYPORT_INFO HEFEI                SELECT               NO  NO  NO  TABLE                    NO-- 创建角色
create role role_wh1;
-- 角色授权
grant resource to role_wh1;
grant connect to role_wh1;
grant unlimited tablespace to role_wh1;
grant select any table to role_wh1;set line 222
set pagesize 99
col role for a20
col role_id for 999
col password for a20
col external_name for a20
col GRANTEE for a20
col GRANTED_ROLE for a20
select * from dba_roles where role='ROLE_WH1';SELECT * FROM dba_role_privs WHERE GRANTEE = 'ROLE_WH1';
GRANTEE              GRANTED_ROLE         ADM DEL DEF COM INH
-------------------- -------------------- --- --- --- --- ---
ROLE_WH1             RESOURCE             NO  NO  YES NO  NO
ROLE_WH1             CONNECT              NO  NO  YES NO  NOgrant unlimited tablespace to role_wh1;
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
1. 系统权限unlimited tablespace是隐含在dba, resource角色中的一个系统权限. 当用户得到dba或resource的角色时, unlimited tablespace系统权限也隐式受权给用户.
2. 系统权限unlimited tablespace不能被授予role, 可以被授予用户.
3. 系统权限unlimited tablespace不会随着resource, dba被授予role而授予给用户.SQL> SELECT * FROM dba_sys_privs WHERE GRANTEE = 'ROLE_WH1';GRANTEE              PRIVILEGE                                ADM COM INH
-------------------- ---------------------------------------- --- --- ---
ROLE_WH1             SELECT ANY TABLE                         NO  NO  NOgrant insert on HEFEI.UR_INDUSTRYPORT_INFO to WORDDB;
Select * from dba_tab_privs where grantee in ('WORDDB') order by 1; 
-- GRANTEE              OWNER                TABLE_NAME           GRANTOR              PRIVILEGE            GRA HIE COM TYPE                     INH
------------------ -------------------- -------------------- -------------------- -------------------- --- --- --- ------------------------ ---
-- WORDDB               HEFEI                UR_INDUSTRYPORT_INFO HEFEI                INSERT               NO  NO  NO  TABLE                    NO-- F-- WORDDB               HEFEI                UR_INDUSTRYPORT_INFO HEFEI                SELECT               NO  NO  NO  TABLE                    NOgrant ROLE_WH1 to WORDDB;
SQL> select grantee,granted_role from dba_role_privs where grantee in ('WORDDB') order by 1;-- GRANTEE              GRANTED_ROLE
------------------ --------------------
-- WORDDB               CONNECT
-- WORDDB               RESOURCE
-- WORDDB               ROLE_WH1

1.5. 用户查询常用

1.5.1. 创建授权

--用户创建授权
CREATE USER HEFEI3 IDENTIFIED BY "123456";GRANT CONNECT,RESOURCE TO HEFEI3;show parameter db_name;SELECT username from all_users wehre username ='AUDITA';###设置用户的默认表空间
alter user dgb default tablespace MYTBS02####用户创建create user user_name identified  by password_
default tablespace user_data
temporary tablespace user_temp;---------------------------------------------------------
create user NH_DW_TBL identified by XXXdefault tablespace NH_DW_TBL_DATAtemporary tablespace TEMPprofile DEFAULT; 
-------------------------------------------------------####为用户授权
grant connect,resource,dba to user_name;###删除用户并且删除用户下的数据比如表
drop user dgb cascade;####锁定和解锁一个用户
alter user perfstat account lock;
alter user ITMS5_1 account unlock;--查看用户表空间配额
select * from dba_ts_quotas; 
select * from user_ts_quotas; 
select username,tablespace_name,max_bytes/1024/1024 "max mb" 
from dba_ts_quotas 
where username='hefei'; --回收表空间配额
revoke unlimited tablespace from hefei01; 
alter user hefei01 quota 0 on users;   --【不限制】-- 1、普通用户服务生成AWR报告权限
grant select any dictionary to user_name;
grant execute on DBMS_WORKLOAD_REPOSITORY to user_name;-- 2、普通用户赋予查看数据字典权限
grant select_catalog_role to user_name;

1.5.2. 获取用户DDL

获得单个用户的DDL：
select dbms_metadata.get_ddl('USER','HEFEI') from dual;获得所有用户的DDL：
SELECT DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;

1.5.3. 查询是否有这个用户

show parameter name
select username from dba_users where username like '*HEF*';--查询数据库中非系统的用户
select username from dba_users where username not in ('SYSTEM','SYSAUX');

1.5.4. 找出使用多个会话的用户

select username,count(*) from v$session group by username;

1.5.5. 查看一个用户所有的权限及角色

select privilege from dba_sys_privs where grantee='RFUSER' union select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='RFUSER');
select granted_role from dba_role_privs where grantee='RFUSER';select privilegefrom dba_sys_privswhere grantee = '&RFUSER'
union
select privilegefrom dba_sys_privswhere grantee in(select granted_role from dba_role_privs where grantee ='&RFUSER');
select granted_role from dba_role_privs where grantee = '&RFUSER';

-- 栗子

CREATE USER COMMDB IDENTIFIED BY "tdV7o6L";

grant connect,RESOURCE,UNLIMITED TABLESPACE to COMMDB;

select privilege from dba_sys_privs where grantee='COMMDB' union

select privilege from dba_sys_privs where grantee in

(select granted_role from dba_role_privs where grantee='COMMDB');

1.5.6. 获得创建用户脚本及权限

set line 199  
set long 100000 
set pages 1000 
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
SELECT (CASEWHEN ((SELECT COUNT(*) FROM dba_users WHERE username = '&&Username') > 0)THEN dbms_metadata.get_ddl ('USER', '&&Username')ELSE to_clob (' -- Note: User not found!')END ) extracted_ddl
FROM dual
UNION ALL
SELECT (CASEWHEN ((SELECT COUNT(*) FROM dba_ts_quotas WHERE username = '&&Username') > 0)THEN dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')ELSE to_clob (' -- Note: No TS Quotas found!')END )
FROM dual
UNION ALL
SELECT (CASEWHEN ((SELECT COUNT(*) FROM dba_role_privs WHERE grantee = '&&Username') > 0)THEN dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')ELSE to_clob (' -- Note: No granted Roles found!')END )
FROM dual
UNION ALL
SELECT (CASEWHEN ((SELECT COUNT(*) FROM dba_sys_privs WHERE grantee = '&&Username') > 0)THEN dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')ELSE to_clob (' -- Note: No System Privileges found!')END )
FROM dual
UNION ALL
SELECT (CASEWHEN ((SELECT COUNT(*) FROM dba_tab_privs WHERE grantee = '&&Username') > 0)THEN dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')ELSE to_clob (' -- Note: No Object Privileges found!')END )
FROM dual;

1.5.7. 用户角色查询

set line 222
col username for a20;
col ACCOUNT_STATUS for a30;
col default_tablespace for a30;
col temporary_tablespace for a30;
col granted_role for a30;select username,ACCOUNT_STATUS,default_tablespace,temporary_tablespace,granted_rolefrom dba_users u, dba_role_privs rwhere u.username = r.granteeorder by username;

1.5.8. 单个用户大小估算

select nvl(t.owner, 'total:') owner,casewhen (to_char(sum(bytes) / 1024 / 10241)) < 1 then'0' || to_char(round(sum(bytes) / 1024 / 10241, 2))elseto_char(round(sum(bytes) / 1024 / 10241, 2))end "大小/Mb"from dba_segments tgroup by rollup(t.owner);#查询AHJZH库占用空间大小SELECT SUM(bytes)/1024/1024 AS "MB" 
FROM dba_segments
WHERE owner='AHJZH';

1.5.9. 具有DBA角色的用户

select grantee,granted_role from dba_role_privs where granted_role='DBA';

1.5.10. 系统表空间中非SYS对象

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,decode(segment_type,'TABLE','alter table ' || OWNER || '.' || SEGMENT_NAME ||' MOVE TABLESPACE &' || 'TABLESPACE;','INDEX','alter index ' || OWNER || '.' || SEGMENT_NAME ||' REBUILD TABLESPACE &' || 'TABLESPACE NOLOGGING;',null) SCRIPTfrom dba_segments twhere t.tablespace_name = 'SYSTEM'AND OWNER NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'WMSYS');

1.5.11. 检测SYSTEM表空间里的用户对象

select owner, segment_type, segment_namefrom dba_segmentswhere owner not in ('SYS', 'SYSTEM')and tablespace_name = 'SYSTEM'order by 1;

1.5.12. 查询用户的表空间

2. 权限

2.1. 授权和权限查询

#授权
grant alter on all table in schema dbcustadm to dbwebopr;
grant select on DBCMAD,T_TASK_DICT TO LIUJW;-- 查询 
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTEE IN ('LIUJW','ZHANGRU','TENGWZ01','YANGMEIYU')
AND TABLE_NAME IN ('T_TASK_DICT','T_FUNCTASK_INFO_CONPLT');

2.2. 用户查询授权

--1、授权表上的读写权限
select 'grant select,insert,update,delete on '||owner||'.'||table_name||' to fslda_zhj;' from dba_tables where owner = 'PDEFSLP7';--2、授权视图上的读写权限
select 'grant select,insert,update,delete on '||owner||'.'||view_name||' to fslda_zhj;' from dba_views  where owner = 'PDEFSLP7';--3、授权函数和存储过程的读写权限
select 'grant execute on ' || 'PDEFSLP7' || '.' || t.name ||' to fslda_zhj;'from (select distinct namefrom dba_sourcewhere owner = 'PDEFSLP7'and type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY','TYPE BODY', 'TRIGGER', 'TYPE')) t--4、授权序列的读写权限
select 'grant select,insert,update,delete on '||sequence_owner||'.'||sequence_name||' to fslda_zhj;' from dba_sequences where sequence_owner = 'PDEFSLP7' ;

3. 角色

创建角色：

CREATE ROLE manager;

将权限授予角色：

GRANT create table,create view TO manager;

将角色授予用户：

GRANT manager TO scott;

常见的预定义角色：

CONNECT, RESOURCE, DBA等。

在SYS用户下执行该语句

select * from role_sys_privs where role=‘角色名’; （查看角色的系统权限）
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE=‘角色名’;(查看角色的对象权限)

DBA在创建用户时，会让您设置一个密码。如果密码忘记了的话，使用以下命令修改：

在sys用户下使用修改用户的密码：

ALTER USER USER_NAME IDENTIFIED BY PASSWORD;

在sys用户下修改角色的密码：

ALTER ROLE ROLE_NAME IDENTIFIED BY PASSWORD;

3.1.1. 获取角色DDL

SELECT DBMS_METADATA.GET_DDL('ROLE','ROLENAME') FROM DUAL;

3.1.2. 查询角色所拥有的权限

select * from role_sys_privs where role='角色名';

3.1.3. 没有授予给任何角色和用户的角色

Select role
from dba_roles r
whererole not in ('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE','WM_ADMIN_ROLE','IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE','OEM_MONITOR','HS_ADMIN_ROLE')andnot exists (Select 1from   dba_role_privs pwhere  p.granted_role = r.role);