Oracle用户、角色、权限管理

概述

oracle数据库的权限系统分为System privileges与Object privileges。

oracle官方文档：

A system privilege is the right to perform a particular action or to perform an action on any object of a particular type. Objects include tables, views, materialized views, synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. Only the instance administrator or a user with ADMIN privilege can grant or revoke system privileges.

谷歌翻译：

系统特权是执行特定操作或对特定类型的任何对象执行操作的权限。对象包括表，视图，物化视图，同义词，索引，序列，缓存组，复制方案和PL / SQL函数，过程和包。只有实例管理员或具有ADMIN权限的用户才能授予或撤消系统权限。

系统权限包含：CREATE TABLE，CREATE VIEW等。

An object privilege is the right to perform a particular action on an object or to access another user’s object. Objects include tables, views, materialized views, indexes, synonyms, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages.

An object’s owner has all object privileges for that object, and those privileges cannot be revoked. The object’s owner can grant object privileges for that object to other database users. A user with ADMIN privilege can grant and revoke object privileges from users who do not own the objects on which the privileges are granted.

翻译：

对象权限是对对象执行特定操作或访问其他用户对象的权限。对象包括表，视图，物化视图，索引，同义词，序列，缓存组，复制方案和PL / SQL函数，过程和包。

对象的所有者拥有该对象的所有对象权限，并且无法撤消这些权限。对象的所有者可以将该对象的对象权限授予其他数据库用户。具有ADMIN权限的用户可以授予和撤消不拥有此对象用户的对象权限。

对象权限包含：DELETE,EXECUTE，SELECT等。

这些权限可以直接授予user，也可以授予role。角色相等于权限组，权限可以授权给用户。

oracle内置的用户有：SYS,SYSTEM,SCOTT(初始未激活)等。

oracle内置的角色有：CONNECT,RESOURCE,DBA等。

一、创建用户

oracle内部有两个建好的用户：system和sys。用户可直接用其一登陆以创建其他用户。

语法[创建用户]： create user 用户名 identified by 口令[即密码]；

例子： create user test identified by test;

语法[更改用户]: alter user 用户名 identified by 口令[改变的口令];

例子： alter user test identified by 123456;

二、删除用户

语法：drop user 用户名;

例子：drop user test;

若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，并删除用户。

语法： drop user 用户名 cascade;

例子： drop user test cascade;

三、授权角色

oracle为兼容以前版本，提供三种标准角色（role）:connect/resource和dba.

CONNECT:只有连接权限。别人可以授权某些表的权限（如select）给你，你才可操作（如查看）这些被授权的特定表。

系统权限为：CREATE SESSION

RESOURCE:资源操作，一般用户授予connect和resource权限即可。

系统权限为：CREATE TRIGGER,CREATE SEQUENCE,CREATE TYPE,CREATE PROCEDURE,CREATE CLUSTER,CREATE OPERATOR,CREATE INDEXTYPE,CREATE TABLE

小插曲：列转行函数

select wm_concat(privilege) as privilege from dba_sys_privs t where t.grantee = ‘RESOURCE’;

或者

select listagg(privilege, ‘,’) within group( order by privilege) as privilege from dba_sys_privs t where t.grantee = ‘RESOURCE’;

DBA:数据库管理员。

授权命令

语法： grant connect, resource to 用户名;

例子： grant connect, resource to test;

撤销权限

语法： drop user 用户名 cascade;

例子： revoke connect, resource from test;

备注：

connect,resource角色没有创建view的权限,如需，则应再单独授权

grant create view to test;

四、创建/授权/删除角色

除了前面讲到的三种系统角色----connect、resource和dba，用户还可以在oracle创建自己的role。用户创建的role可以由系统权限、对象权限或两者的组合构成。为了创建role，用户必须具有create role系统权限。

1》创建角色

语法： create role 角色名;

例子： create role testRole;

2》删除角色

语法： drop role 角色名;

例子： drop role testRole;

3》授权角色

语法： grant select on mytable1 to 角色名;

例子： grant select on mytable1 to testRole;

4》撤销授权

语法： revoke select on mytable1 from 角色名;

例子： revoke select on mytable1 from testRole;

五、相关表

select * from v$database; --查看当前实例名

select * from v$session; --查看当前所有用户连接

select * from user_tables; – 查看当前用户能访问的表

select * from dba_tables where owner = ‘USERNAME’;

select * from user_users; – 查看当前用户信息

select * from dba_users where username = ‘XCY’; --查看数据库里面所有用户

select * from dba_roles; – 查看角色

select * from user_role_privs; – 查看当前用户的角色

select * from dba_role_privs t where t.grantee = ‘XCY’; – 查询grantee被授权的角色(角色也可以被授权角色，如’DBA’被授权有java_admin,olap_dba等角色)

select * from user_sys_privs; --查询用户被授予的系统权限

select * from user_tab_privs; – 查询用户被授予的表相关权限

select * from dba_sys_privs t where t.grantee = ‘CONNECT’; – 查询grantee(包含角色和用户)被授予的系统权限

select * from dba_tab_privs t where t.grantee = ‘XCY’; – 查询grantee(包含角色和用户)被授予的表相关权限(insert,delete,update,selete,read,use,execute等)

select * from role_sys_privs where role =‘OLAP_DBA’; --查询role所被授予的privilege

select * from role_tab_privs t where t.role=‘DBA’; --查询role所被授予的表相关privilege

查看所有表空间使用情况

select a.file_id “FileNo”,

a.tablespace_name “Tablespace_name”,

a.bytes “Bytes”,

a.bytes - sum(nvl(b.bytes, 0)) “Used”,

sum(nvl(b.bytes, 0)) “Free”,

sum(nvl(b.bytes, 0)) / a.bytes * 100 “%free”

from dba_data_files a, dba_free_space b

where a.file_id = b.file_id(+)

group by a.tablespace_name, a.file_id, a.bytes

order by a.tablespace_name;

完

happy coding!