Oracle用户和权限

Oracle用户和权限

Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。

用户

新建用户

使用pl/sql developer工具，并使用管理员身份登录

右键users，点击新建

新建用户界面

新建一个用户信息

查看对应的sql语句

sql语句

点击应用，新建成功

登录新建用户

点击左上角的小钥匙

输入用户信息

第一次登录提示输入新密码

登录，显示失败，没有登录权限

赋予用户登录权限

对应的sql语句

grant create session to BOB;
登录成功

重点

• 建立用户

CREATE USER aaron
IDENTIFIED BY soccer
DEFAULT TABLESPACE data
DEFAULT TEMPORARY TABLESPACE temp
QUOTA 15M ON data
QUOTA 10M ON users
PASSWORD EXPIRE;

• 修改密码

ALERT USER user IDENTIFIED {BY password | EXTERNALLY}

• 锁定/解锁用户

ALERT USER user ACCOUNT { LOCK | UNLOCK }

• 删除用户

 使用 CASCADE 子句删除模式中的所有对象
正连接到数据库服务器上的用户不能被删除
DROP USER aaron;
DROP USER aaron CASCADE;

• 获取用户信息

用户信息可通过两个数据字典获取
select * from DBA_USERS;
select * from DBA_TS_QUOTAS;

权限

两种用户权限：
系统权限：允许用户在数据库中执行某种特定的操作
对象权限：允许用户访问或操作某个对象

系统权限

系统权限可分为： 允许执行系统范围的操作。如, CREATE SESSION, CREATE TABLESPACE
允许管理属于某个用户模式中对象。如, CREATE TABLE 允许管理所有模式的对象。如, CREATE ANY TABLE

授予系统权限sql

GRANT {system_privilege|role}
[, {system_privilege|role} ]... TO {user|role|PUBLIC}   
[, {user|role|PUBLIC} ]... [WITH ADMIN OPTION]

例子1

GRANT CREATE SESSION, CREATE TABLE TO managers;

例子2

GRANT CREATE SESSION TO scott 
WITH ADMIN OPTION;

取消系统权限
例子1

REVOKE CREATE TABLE FROM karen;

例子2

REVOKE CREATE SESSION FROM scott;

WITH ADMIN OPTION;

例子

新建用户coc

-- Create the user 
create user coc
  identified by coc
  default tablespace USERS
  temporary tablespace TEMP;
-- Grant/Revoke system privileges 
grant create session to coc with admin option;

登录coc

登录管理system，回收coc登录权限

PS:coc已经将登录权限赋予了bob，system回收了coc，bob依然可以登录

对象权限

oracle数据库有几大对象，表、视图，序列、存储过程等

对象权限关系

授予对象权限相关语句

GRANT { object_privilege [(column_list)] 
[, object_privilege [(column_list)] ]...
|ALL [PRIVILEGES]}
ON  [schema.]object
TO  {user|role|PUBLIC}[, {user|role|PUBLIC} ]...
[WITH GRANT OPTION]

例子1

GRANT EXECUTE ON dbms_pipe TO public;

例子2

GRANT UPDATE(first_name, salary) ON employee TO karen WITH GRANT OPTION;

取消对象权限相关语句

REVOKE {object_privilege [, object_privilege ]...
| ALL [PRIVILEGES] }
ON  [schema.]object
FROM  {user|role|PUBLIC} [, {user|role|PUBLIC} ]...
[CASCADE CONSTRAINTS]

例子1

REVOKE execute ON dbms_pipe FROM scott;

WITH GRANT OPTION与系统权限类似

角色

角色：权限的命名集合。
说明：
Grant赋予角色权限；Revoke回收角色权限
可通过角色对其他角色或用户赋权
可包含系统权限和对象权限
角色名不能与现有的用户名重复
角色不属于某个用户，也不属于某个模式
角色的定义存储在数据字典中

建立角色相关sql
无密码

CREATE ROLE oe_clerk;

有密码

CREATE ROLE hr_clerk
            IDENTIFIED BY bonus;

有密码

CREATE ROLE hr_manager
            IDENTIFIED EXTERNALLY;

常用的角色

CONNECT，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，拥有CONNECT角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。

RESOURCE，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。

DBA，DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。用户SYSTEM拥有DBA角色。

为用户分配角色相关sql

GRANT role [, role ]... TO  {user|role|PUBLIC}[, {user|role|PUBLIC} ]... [WITH ADMIN OPTION]

例子1

GRANT oe_clerk TO scott;

例子2

GRANT hr_clerk TO hr_manager;

例子3

GRANT hr_manager TO scott WITH ADMIN OPTION;

从用户回收角色相关sql

例子1

REVOKE oe_clerk FROM scott;

例子2

REVOKE hr_manager FROM PUBLIC;

删除角色

删除角色:
角色及其相关的权限被撤销
从数据库中删除

DROP ROLE hr_manager;

获得角色信息

select * from DBA_ROLES t;
select * from DBA_ROLES_PRIVS t;
select * from ROLE_ROL_PRIVS t;
select * from DBA_SYS_PRIVS t;
select * from ROLE_SYS_PRIVS t;
select * from ROLE_TAB_PRIVS t;
select * from SESSION_ROLES t;

PS:Oracle的角色包括了一系列系统权限和普通对象权限，可以把权限授权给角色，把权限或者角色授权给用户。