Oracle数据库用户、权限与角色管理详解
本文详细介绍了Oracle数据库中的用户管理，包括创建和删除用户、认证方式以及sysdba的认证。此外，还讲解了权限管理，如系统权限、对象权限、空间配额以及角色管理，包括如何创建、赋予权限和回收。内容涵盖了用户属性、默认表空间、临时表空间、profile、权限回收、角色的创建和启用等关键概念。
1.1 用户管理
每个用户有一系列的属性，包括以下几个方面：
A. 唯一的用户名，必须字母开头，最多30字节，不能有特殊字符。
B. 认证： 通常使用密码认证
C. 默认表空间： 用户创建后，如果在用户下创建了对象，那么生成与用户同名的schema,
即schema与用户名的区别在于是否存在用户对象。10g之前，如果没有指定default tbs,
那么system会被作为默认表空间；10g或之后，在建库的时候就可以指定一个数据库级的
默认表空间，这样定义用户没有指定默认表空间的话，默认就使用这个数据库级的TBS.
可以通过下面的语句查询：
select * from database_properties ;
或者 select name,value$ from props$ where name like 'DEFAULT%'
D. 默认临时表空间： Oracle9i之前，如果没有指定，会使用system作为临时表空间，9i开始，
可以为数据库指定默认临时表空间，定义用户时没有指定的话，使用数据库默认的临时表空间。
E. profile(配置文件)，用于控制用户密码策略以及资源使用。
F. 用户组
G. 锁定状态： 可以将用户锁定和解锁 。
数据库创建后，会预先建立两个非常重要的用户：sys及system, sys是超级用户，权限太大，
所以登陆的时候需要加入as sysdba 增强安全性，因为以as sysdba登陆后，登陆的时间信息
会记录在审计文件中。system仅仅时候数据库管理员，没有sys那么大权限。
通常我们不使用sys及system等用户登入数据库进行管理，而是创建一个用户，赋予该用户DBA
角色权限，用该用户来进行日常管理工作。
1.1.1 创建和删除用户
CREATE USER sidney
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE;
认证方式： 密码认证，外部认证，全局认证
外部认证 -
密码认证大家都熟悉，这里介绍一下外部认证。使用外部认证需要使用参数os_authent_prefix，
如果操作系统存在一个用户susan, 而数据库中存在一个用户ops$susan, 则只要以susan用户登
陆OS, 当登陆数据库时，可以不用提供用户密码，即可以以ops$susan登陆到数据库。若此参数为
空，那么只要数据库中存在与操作系统同名的用户，该用户就可以通过外部认证登陆数据库，因
为不完全，oracle不建议采用该认证方式。
SQL> show parameter os_authent_prefix
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
全局认证 - 需要采用oracle的高级安全设置.
--------------
创建用户语法：
CREATE USER username
IDENTIFIED {BY password|EXTERNALLY}
[DEFAULT TABLESPACE tablespace_name]
[TEMPORARY TABLESPACE tablespace_name]
[QUOTA [n [K|M]] ON tablespace_name]
[PASSWORD EXPIRE]
[ACCOUNT LOCK|UNLOCK]
[PROFILE filename];
参数：
IDENTIFIED BY password:
用来指定用户的确认方式为数据库确认，password是为该用户指定的密码
IDENTIFIED EXTERNALLY:
指定确认方式为操作系统确认，并且生成一个用操作系统确认的用户
DEFAULT TABLESPACE:
用户默认的表空间，用于在该用户模式下创建的对象(如表、索引、簇、视图等)没有指
定表空间时数据库默认的存储表空间
TEMPORARY TABLESPACE:
临时表空间，用于存储排序等事务处理时的存储空间用户暂存段表空间
QUOTA [n [K|M]] [UNLIMITD] ON tsname:
指定表空间的限制配额，就是限制允许用户使用的表空间的数量(大小)，UNLIMITD
表示无大小限制
PASSWORD EXPIRE:
表明用户第一次登录时要修改密码
ACCOUNT LOCK|UNLOCK:
锁定用户,lock为锁定，unlock为未锁定或解锁，默认为不锁定(unlocked)
PROFILE filename:
用户的配置文件，用于限制用户对系统资源的使用，如果没有指定，则系统使用默
认的用户配置文件
举例：
1. 创建一个数据库验证的用户：
CREATE USER testUser
IDENTIFIED BY test
DEFAULT TABLESPACE basedata
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
PASSWORD EXPIRE
ACCOUNT UNLOCK
PROFILE DEFAULT;
2. 授予用户连接权限 (用户只有拥有CREATE SESSION 权限才可能与数据库建立连接)
GRANT CREATE SESSION TO testUser;
3. 修改用户信息：
ALTER USER testUser IDENTIFIED BY temp; -- 修改密码
ALTER USER testUser ACCOUNT LOCK; -- 锁定用户
4. 删除用户：
DROP USER testUser CASCADE; -- 级联删除，即将此用户拥有的全部对象一起删除
5. 查询用户信息：
SELECT * FROM ALL_USERS;
SELECT username, password, account_status FROM DBA_USERS;
1.1.1.2 sysdba的认证方式
对于sys用户来说，oracle提供了比较特殊的认证方式。 对于sys的认证就是oracle是否允许
我们以sysdba的权限登陆到数据库进行管理工作，因为数据库内建用户都放在数据字典中，数
据库没有开启之前无法访问数据字典，普通用户无法通过数据库验证身份，sys如果也放在数据
字典，同样无法登陆数据库，数据库启动都无法实现。
所以Oracle对sys用户认证提供两种方式： 操作系统认证和密码文件认证
A. 操作系统认证 -
操作系统用户A同时也是服务器数据库管理员组的成员(unix上是dba组，windows是ora_dba组，这些
用户组是在安装oracle软件的时候就建立了，不是dbca时),表示A已经具有操作系统权限，不再需要
提供sys的密码了，而是直接以sysdba权限登陆。比如：
SQL> connect / as sysdba
ORA_DBA描述是： Members can connect to the Oracle database as a DBA without a password
设置操作系统认证： sqlnet.ora
sqlnet.authentication_services=(NTS)
如果是NTS, 表示要进行操作系统验证，如果没有sqlnet.ora文件，或该文件没有记录该条目，也
表示进行操作系统认证。否则，如果记录为NONE,
sqlnet.authentication_services=(NONE)
如果是NONE, 表示不进行操作系统认证，即便是dba组成员登陆OS, 如果想以sys的身份登入还是
需要输入密码，这个密码在密码文件中。
B. 密码文件认证 -
密码文件中记录着 sys用户的口令以及其它"特权用户"的用户名／口令，通俗的说就是这个文件中
记录了当前具有sysdba/sysoper权限的用户名及其口令。创建数据库实例的时侯，Oracle系统会自动创建一个与实例对应的密码文件。
对于unix/linux而言，密码文件存放在$ORACLE_HOME/dbs目录下，对于windows而言，%ORACLE_HOME%\
database下，unix下密码文件格式为orapw<SID>.ora，如果环境变量中ORACLE_SID为大写，比如TEST,
那么密码文件中的<SID>部分也要大写，小写也一样要匹配，否则登入会报错。windows下面文件格式为
PWD<SID>.ora, SID部分也要遵循大小写规则。
sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户，所有oracle的数据
字典的基表和视图都存放在sys用户中，登陆时也只能用sysdba、sysoper这两个身份，不能用normal
身份system用户拥有普通角色权限， 只能用normal身份登陆， 除非你对它授予了sysdba的系统权限
或者syspoer系统权限。
创建密码文件 -
linux/unix下面(在dbs目录下)：
$orapwd file=orapwora10g.ora password=oracle entries=5
window下面(在database目录下)：
$orapwd file=PWDora10g.ora password=oracle entries=5
其中entries表示同时有几个用户拥有sysdba的权限，同时他们的密码都放在密码文件中。
建立密码文件后，必须重新启动数据库才能使它生效。
参数remote_login_passwordfile -
该参数用来控制能否使用密码文件验证，参数有三个值：
A. None, 远程用户无法通过网络方式以sysdba或sysoper的权限登陆到数据库。且授权或回收SYSDBA、SYSOPER权限会失败。
B. Exclusive, 默认值，表示该密码文件只能被一个实例使用，可以向密码文件添加新的用户，若密码丢失，用户同样无法通过密码认证的方式登陆到数据库。
C. Shared, 该密码文件可以被多个数据库共享 , 但是密码文件只能存放sys用户的密码，不能添加其他用户。任何使用这个密码文件的数据库都不能进行授权回收SYSDBA、SYSOPER权限的操作，但是这些数据库可以使用这个密码文件来进行验证。这种方式一般用于管理RAC数据库，或者同一台服务器上的多个不同数据库实例。
1.1.1.3 删除用户
SQL> drop user susan ; 如果有其他session以该身份登入，删除用户会失败，需要中断以该用户
登入实行的session , 然后等待PMON回收资源(查询v$session), 最后删除用户。
如果用户下面有对象存在，也会报错，需要加入cascade ;
SQL> drop user susan cascade ; 删除该用户及其下面的对象全部删除。
1.1.2 Profile与用户管理
可以使用Profile对用户所能使用的资源进行管理，profile存放在数据字典中，默认就有一个名为
default的profile . 可以通过dba_profiles 显示或者通过工具比如toad查看。
CREATE PROFILE DEFAULT LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;
从字段RESOURCE_TYPE中可以看出，profile管理的资源包括两类： password和kernel .
密码管理：
A. FAILED_LOGIN_ATTEMPTS 10 , 连续登陆10次不成功锁定账号
B. PASSWORD_LIFE_TIME UNLIMITED , 密码在多少天后失效，失效后必须修改密码
C. PASSWORD_REUSE_TIME UNLIMITED, 指定的天数内，设置的密码不能重复
D. PASSWORD_REUSE_MAX UNLIMITED, 指定的密码修改次数之内，密码不能重复
E. PASSWORD_LOCK_TIME UNLIMITED, 一旦登陆次数超过指定的值，则锁定账号多少天
F. PASSWORD_GRACE_TIME UNLIMITED, 密码失效缓冲期，期间可以登陆，但是每次都提示更改密码，
指定的天数内还不更改密码，超过天数后账号被锁定。
G. PASSWORD_VERIFY_FUNCTION NULL, 指定函数控制密码复杂度，函数样例在utlpwdmg.sql脚本中。
内部资源管理：
A. SESSIONS_PER_USER , 同一个用户最多能产生多少session
B. CPU_PER_SESSION , 每个session一次最多能持续占用CPU多长时间，百分之一秒为单位。
C. CPU_PER_CALL, 一次调用最多能持续占用cpu多长时间，比如执行SQL时，会有解析调用，执行调用等。
D. LOGICAL_READS_PER_SESSION, 每个session能够进行的逻辑读的最大个数
E. LOGICAL_READS_PER_CALL, 一次调用能够进行的逻辑读的最大个数
F. IDLE_TIME, session持续idle时间超过该参数指定的时间，则中断该session的连接。idel指
session既没有消耗CPU, 也没有等待I/O.
G. CONNECT_TIME, 持续connect时间超过该参数指定的时间，则中断该session的连接。
H. PRIVATE_SGA, 每个session 能够使用的pga的最大尺寸。
I. COMPOSITE_LIMIT, 该参数是基于以上的参数的权重而进行的控制。
在删除profile时，如果被删除的profile已经分配给用户了，那么该profile不能被删除，可以添加
cascade, 表示删除profile, 并将名为default的默认pfofile应用到那些受影响的用户上。
SQL> drop profile my_profile cascade ;
1.2 权限管理
系统权限和对象权限
1. 系统权限 - 是指用户能够做什么事情的权限，比如创建表等，10g中有100多个系统权限，其中很多
含有any的字样，比如 create table权限表示可以在自己的schema中建表，相应的，create any table
表示可以在其他schema中建表，所有系统权限存放在system_privilege_map的数据字典表中。
对于管理而言，有一些常用的系统权限：
A. sysdba, sysoper , 最重要也是最大的权限，能在库中做任何事情。
B. restricted session , 以restricted模式开启，通常在进行维护性工作时，不希望其他用户登入，
会采用这种方式。 默认只有sys具有该权限。
C. create 或 create any 开头的权限
D. drop 或 drop any 权限
E. create session , 用户要登陆数据库，至少要有该权限。
权限赋予 -
SQL> grant create session, create table to susan ;
加入admin option 表示该用户也可以把以该admin option方式获得的权限赋给其他用户。
SQL> grant create session, create table to susan with admin option ;
SQL> connect susan/passwd
SQL> grant create session to susan2 ;
权限回收 -
SQL> revoke create session, create table from susan ;
权限从susan回收后，不影响已经分配给susan2的权限。
空间配额quota -
SQL> alter user susan quota 100M on base_data ;
可以查询dba_ts_quotas 查看；
带有any的系统权限，能在其他schema下处理对象，不过不能操作sys所有的对象，这个
特性是由o7_dictionary_accessibility 所决定的，该参数默认为false，如果是true,
只要用户具有any权限，就能处理其他schema包括sys下的对象了。为了安全，一般建议，
我们应该确保此参数为false。
对象权限 -
alter , delete, update, select, insert, execute, index, references
将某个对象的权限赋予某个用户时，必须以对象所有者身份登入，然后将当前用户所具有的
对象的某个对象权限赋给另外的用户。
SQL> connect hr/passwd
SQL> grant select on employees to susan ;
SQL> grant index on employees to susan ;
SQL> connect sys/passwd
SQL> grant execute on dbms_output to susan ;
grant option -
SQL> connect / as sysdba
SQL> grant update on hr.employees to susan ;
SQL> grant select on hr.employees to susan with grant option ;
以上表示用户susan 可以将查询用户hr下的employees表的权限赋给其他用户。比如：
SQL> connect susan/passwd
SQL> grant select on hr.employees to susan2 ;
然而当我们从susan用户回收这个权限的时候，susan2上的此权限同样消失了。也就是说对象
权限的回收具有连带效应。
1.3 角色管理
角色可以理解为权限的集合，我们可以将某个角色赋给某个用户，那么这个用户就拥有了角色
所包含的所有权限； 也可以将角色赋给另外的角色。 为角色赋予角色时，不能形成闭环，比如
A赋予B,B赋予C, C又赋予A, 则报错。
可以从dba_roles中看到很多定义好的角色如 dba, resource 。
dba : 拥有大部分的管理权限，一般不建议通过sys或system管理数据库，而是建立一个新用户，
赋予dba权限就可以使用该用户对数据库进行日常管理了。
resource : 该角色拥有很多create开头的权限，比如 create table, create procedure等，在
oracle9i 中resource拥有unlimited tablespace的系统权限，但是到了10g不再具有。
创建角色 -
SQL> create role my_role ;
SQL> grant create session to my_role;
将角色赋给其他用户
SQL> grant my_role to susan ;
将角色从其他用户收回
SQL> revoke my_role from susan ;
我们也可以对角色使用不同的认证方式，表示在启用角色时，是否对启用的权限进行认证，使用下面
的命令启用角色权限：
alter role role_name not identified ;
alter role role_name identified { by <password> | using <package> | externally | globally} ;
not identified : 不认证，默认选择。
identified by <password> ： 启动角色时采用密码认证。
identified externally ： 启动角色时采用操作系统认证 。
identified globally ： 启动角色时采用Oracle的高级安全选项。
identified using <package> ： 只能通过执行特定的包来启用角色。
某个用户可以有多个角色，这些角色中可分为两类：
1. 默认角色： 这些角色在用户登陆时自动启用，如果我们为默认角色指定认证方式，比如密码
认证，那么启用该角色时不用输入密码，也即认证方式对默认角色来说无意义。
2. 非默认角色： 这些角色在用户登陆时禁用，需要发出set role来进行启用，启用时需要认证。
在设定用的角色时，我们指定哪些角色为默认角色，那么该用户剩下的其他角色就是非默认角色。如：
SQL> create role app_role identified by oracle ;
SQL> grant create session , resource to app_role ;
SQL> create role conn_role ;
SQL> grant create session to conn_role ;
SQL> grant app_role, conn_role to susan ;
将conn_role角色设置为susan的默认角色
SQL> alter user susan default role conn_role ;
也可以类似：
SQL> alter user susan default role all except app_role ;
表示除了app_role之外都是默认角色，如果想取消用户上的所有角色：
SQL> alter user susan default role none ;
可以启用susan上的另外一个角色：
SQL> set role app_role identified by oracle ;
with admin option 与 with grant option 区别
1、with admin option 用于系统权限授权，with grant option 用于对象授权。
2、给一个用户授予系统权限带上with admin option 时，此用户可把此系统权限授予其他用户或角色，
但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效.
如授予A系统权限create session with admin option, 然后A又把create session权限授予B, 但管
理员收回A的create session权限时，B依然拥有create session的权限.
而with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，管理员
收回用with grant option授权的用户对象权限时，权限会因传播而失效，
如grant select on table with grant option to A,A用户把此权限授予B，但管理员收回A的权限时，
B的 select on table 权限也随之失效。
一般对sysdba的管理有两种方式：操作系统认证和密码文件认证。
具体选择那一种认证方式取决于：你是想在Oracle运行的机器上维护数据库，还是在一台机器上管理分布
于不同机器上的所有的Oracle数据库。若选择在本机维护数据库，则选择操作系统认证可能是一个简单易行的
办法;若有好多数据库，想进行集中管理，则可以选择password文件认证方式。
使用操作系统认证方式的配置过程：
首先，在安装Oracle之前，建立一个DBA组，这一步不用说了，不然是装不上Oracle的。一般还建立一个名
为Oracle的用户，并把它加入到DBA组中。
第二步，设置REMOTE_LOGIN_PASSWORD为NONE。在Oracle8.1以后，该参数默认为EXCLUSIVE。一定要记得改过来。
第三步,用该用户名登录Unix，运行SQL*Plus或者SERVER MANAGER，输入以下命令：CONNECT INTERNAL
(CONNECT / AS SYSDBA)来登录到Oracle中。
使用PASSWORD文件认证方式的配置过程：
Oracle提供orapwd实用程序来创建password文件，运用orapwd建立该认证方式的具体步骤如下：
1. 使用Orapwd实用程序来创建一个PASSWORD文件。
Orapwd路径：D:ORACLEORA92BINorapwd.exe
使用方法：1、转到Orapwd所在目录。
语法：
Usage: orapwdfile=password=entries=
Where
File–nameofpasswordfile,
Password–passwordforSYS,
Entries–maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to(=)character.
Usage/jyrapwdfile=文件名password=internal用户密码entried=entries.
详细解释：
文件名要包含完整的全路径名，如果不指定，Oracle把它默认放置$ORACLE_HOME/bin
用户密码是用户SYS的密码。当然后来还可以再向里边加入别的超级用户。
Entries表示最大允许有的超级用户数目。这个是一个可选的。前两者是必须指定的。一般会把它设置的
比实际需要大一些，以免不够。
2. 把INIT.ORA中REMOTE_LOGIN_PASSWORD设置为EXCLUSIVE或SHARED.
使用EXCLUSIVE表示只有当前INSTANCE使用这个password文件。而且允许有别的用户作为sysdba登录进
系统里边，而若选择了SHARED，则表明不止一个实例使用这个密码文件，伴随着一个很强的约束：sysdba权
限只能授予sys和internal这两个用户名。
3. 用SQL*Plus或SERVER MANAGER运行下面命令登录进系统：CONNECT INTERNAL/密码。
== 注意点：==
1.当选择密码文件认证方式时，可以再向系统中加入其他超级用户。比如用以下语句把用户SCOTT加入
超级用户之中：(由具有sysdba权限的人执行)
SQL>GRANT SYSDBA TO SCOTT;
这样SCOTT用户就具有了sysdba权限。注意，此时SCOTT用户可以以两种身份登录：SCOTT , SYS.当SCOTT在
登录时没有输入AS SYSDBA时，SCOTT是作为普通用户登录的。而当登录时输入了AS SYSDBA时，此时SCOTT登
录进去的用户实际上为sys.
create user SCOTT identified by SCOTT;
grant sysdba to SCOTT;
select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SCOTT TRUE FALSE
$sqlplus /nolog
SQL> conn SCOTT/SCOTT as sysdba
SQL> show user
USER is "SYS"
SQL> exit
$sqlplus /nolog
SQL> conn SCOTT/SCOTT
ERROR:
ORA-01045: user A lacks CREATE SESSION privilege; logon denied
可以看出即使拥有sysdba权限，但是还是没有create session的权限。
2.当前系统中的具有sysdba权限的用户名可以从数据字典视图v$pwfile_user中查询得到：
SELECT * FROM V$PWFILE_USERS;
3. 系统中最大的具有sysdba权限的用户数由创建密码文件时的ENTRIES参数决定。当需要创建更多的具
有sysdba权限的用户时，就需要删除原有的密码文件，重新创建一个，在entries中输入足够大的数目。再启
动Oracle。 这时，所有原来被授权的超级用户都不再存在，需要重新授权。所以在重新创建密码文件前，先
要查询该视图，记下用户名，再在创建完密码文件后重新授权。
4.运行orapwd时，一定要在数据库关闭的情况下，且最好先备份原来的密码文件。在数据库运行时执行
orapwd，将不能远程以sysdba登录数据库，因为此时v$pwfile_user中具有sysdba权限的用户的记录已清空。
5.选择password文件认证方式时，如以oracle用户(属于dba组)在数据库所在主机运行connect internal
或connect / as sysbda也不需口令，connect a/a as sysdba即使用户a不存在也可登录数据库，因为这时还
是采用操作系统认证。
6.Internal用户密码忘记的处理方法：
有两种办法：
1.ALTER USER SYS IDENTIFIED BY新密码; //这同时也改变了Internal的密码,在Oracle8I中通过
2. 重新创建一个新的密码文件，指定一个新的密码。
备注：整理自网络
扫一扫
621
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
