本文介绍了Oracle数据库中用户管理的基础知识，包括用户创建、权限分配、角色管理和资源限制等核心概念。通过SQL命令示例详细讲解了如何进行用户授权、权限转移、角色创建以及如何利用profile对用户会话进行限制。
用户管理
口令及资源管理参数
SQL> select resource_name,limit fromdba_profiles where profile='DEFAULT';
【查询所有口令及资源管理参数】
RESOURCE_NAME LIMIT
------------------------------------------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED【用户最多能同时发起的会话连接个数】
CPU_PER_SESSION UNLIMITED【每次会话（连接）所占用的CPU总时（0.01s）】
CPU_PER_CALL UNLIMITED【每次调用（命令）所占用的CPU总时（0.01S）】
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED【会话中单次最长空闲时间（minutes）】
CONNECT_TIME UNLIMITED【每次会话的最大连接时长（minutes）】
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10【用户登录连续失败多少次后即所定用户】
PASSWORD_LIFE_TIME UNLIMITED【口令最长有效天数，应与下条联用，否则无意义】
RESOURCE_NAME LIMIT
------------------------------------------------------------------------
PASSWORD_REUSE_TIME UNLIMITED【多少天内旧口令不能重用(与下参数互斥)】
PASSWORD_REUSE_MAX 【重复使用旧口令前口令需被改变过的次数】
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED【用户被锁定天数，到期自动解锁】
PASSWORD_GRACE_TIME UNLIMITED【口令到期后宽限期天数（提示用户），超过宽限期限则强制修改口令（非禁止登录）】
16 rows selected.
对指定用户进行口令或资源限制，需将被限制参数组合成为profile，profile即资源管理参数概要文件，再将该profile分配给指定用户。
为指定用户分配profile后，仅对该用户的新生会话生效，而对该用户在分配profile前已登录的会话无效。
每个用户只能分配一个profile.
SQL> select distinct profile fromdba_profiles;
【查询系统中所有profile】
PROFILE
------------------------------
MONITORING_PROFILE
DEFAULT
SQL> create profile myprofile limitfailed_login_attempts 4 password_lock_time 2 password_life_time 7password_grace_time 3;
【建立profile并指定限制参数】
Profile created.
SQL> select resource_name,limit fromdba_profiles where profile='MYPROFILE';
【查看指定profile设置的口令和资源限制参数】
RESOURCE_NAME LIMIT
------------------------------------------------------------------------
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LIFE_TIME 7
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME 2
PASSWORD_GRACE_TIME 3
16 rows selected.
SQL> create user test identified bytest123 profile myprofile;
【建立用户时分配使用profile】
User created.
SQL> grant connect to test;
【授予用户会话权限以进行限制参数测试】
Grant succeeded.
SQL> select profile from dba_users whereusername='TEST';
【查看用户所使用的profile】
PROFILE
------------------------------
MYPROFILE
测试test 用户4 个限制参数
SQL> alter system set resource_limit=truescope=both;
【使用profile管理口令时，口令管理选项总是处于被激活状态，但若使用profile管理资源，必须要激活资源限制】
System altered.
SQL> alter profile myprofile limitsessions_per_user 3 connect_time 2 idle_time 1;
【修改profile，添加参数 若未重新指定，则原参数不被覆盖】
Profile altered.
注意：因oracle的检查最小时间间隔是5分钟左右，故用户的实际连接时间最多可能是设置值加5分钟。
测试：
Kitty用户最多能同时发起3个会话连接；
Kitty用户每次会话连接超过2分钟就被断开；
Kitty用户每次会话空闲时间超过1分钟就被断开。
SQL> alter user scott profile DEFAULT;
【修改用户使用的profile】
User altered.
SQL> drop profile myprofile;【删除profile】
drop profile myprofile
*
ERROR at line 1:
ORA-02382: profile MYPROFILE has usersassigned, cannot drop without CASCADE
SQL> drop profile myprofile cascade;
【删除已分配给用户的profile】
Profile dropped.
（已读）
用户创建及授权
SQL> create profile myprofile limitfailed_login_attempts 4 password_lock_time 2 password_life_time 7password_grace_time 3;
Profile created.
SQL> create tablespace testspacedatafile '/u01/test.dat' size 10M;
Tablespace created.
SQL> create tablespace tempspacedatafile '/u01/temp.dat' size 10M;
Tablespace created.
SQL> create user mary identified bymary123 default tablespace testspace temporary tablespace temp profilemyprofile;
【创建用户】
User created.
权限的3种类型：
1. 系统权限：如create table,alter view等。
2. 对象权限：如delete on table,insert on kitty.table等。
3. 角色权限：各类权限的一种组合。
权限的2种授予方式：
1. 将权限直接授予给用户
2. 先把权限授予给某角色，再把该角色（所拥有的权限）授给一个或多个用户。
SQL> grant create table to mary;
【将系统权限（建表）授予给用户】
Grant succeeded.
SQL> grant connect to mary;
【将角色权限（登录）授予给用户】
Grant succeeded.
SQL> create table kitty.test (i int);
Table created.
SQL> grant select on kitty.test topublic;
【将对象权限授予给所有人】
Grant succeeded.
SQL> grant update (sal) on scott.emp tomary;
【将对象中指定列的update或insert权限（不能指定select /delete权限）授予用户，提供列级安全控制（行级安全控制权限需由精细审计实现）。另注意revoke列级安全控制权限时不能指定列名 】
Grant succeeded.
SQL> select * from dba_sys_privs wheregrantee='MARY';
GRANTEE PRIVILEGE ADM
---------------------------------------------------------------------- ---
MARY CREATE TABLE NO
SQL> select * from dba_role_privs wheregrantee='MARY';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------------------------------------ --- ---
MARY CONNECT NO YES
SQL> select * from dba_tab_privs wheregrantee='MARY';
no rows selected
SQL> select * from dba_col_privs wheregrantee='MARY';
GRANTEE （被授予者） OWNER
------------------------------------------------------------
TABLE_NAME COLUMN_NAME
------------------------------------------------------------
GRANTOR 【授予者】 PRIVILEGE GRA
------------------------------ -------------------------------------------
MARY SCOTT
EMP SAL
SCOTT UPDATE NO
【查用户所拥有的系统权限，角色权限，对象权限及对象列权限】
SQL> create table test (i int);
create table test (i int)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TESTSPACE'
SQL> grant unlimited tablespace to mary;
【将系统权限（任意表空间上任意配额）直接授予给用户】
Grant succeeded.
SQL> alter user mary quota 10M on users;
【修改用户的空间配额为指定表空间上的指定大小】
User altered.
SQL> alter user mary quota unlimited onusers;
【修改用户的空间配额为指定表空间上的不限大小】
User altered.
[oracle@desktop241 ~]$ sqlplus mary/mary123
SQL*Plus: Release 10.2.0.5.0 - Productionon Wed Oct 17 21:27:43 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> create table test (i int);
Table created.
SQL> create user mary2 identified bymary2;
User created.
SQL> grant connect,resource to mary2;
Grant succeeded.
SQL> select * from dba_role_privs wheregrantee='MARY2';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------------------------------------ --- ---
MARY2 RESOURCE NO YES
MARY2 CONNECT NO YES
SQL> select * from dba_sys_privs wheregrantee='MARY2';
GRANTEE PRIVILEGE ADM
---------------------------------------------------------------------- ---
MARY2 UNLIMITED TABLESPACE NO
SQL> select * from dba_tab_privs wheregrantee='MARY2';
no rows selected
SQL> select * from dba_col_privs wheregrantee='MARY2';
no rows selected
SQL> select name fromsystem_privilege_map;
【查询系统权限，ANY 权限表示某用户在其他任何schema中（SYS除外）】
NAME
----------------------------------------
CREATE EXTERNAL JOB
CHANGE NOTIFICATION
READ ANY FILE GROUP
MANAGE ANY FILE GROUP
MANAGE FILE GROUP
EXEMPT IDENTITY POLICY
CREATE ANY SQL PROFILE
ADMINISTER ANY SQL TUNING SET
ADMINISTER SQL TUNING SET
ALTER ANY SQL PROFILE
DROP ANY SQL PROFILE
SQL> grant create any table to mary withadmin option;
Grant succeeded.
SQL> select * from dba_sys_privs wheregrantee='MARY';
GRANTEE PRIVILEGE ADM
---------------------------------------------------------------------- ---
MARY CREATE TABLE NO
MARY UNLIMITED TABLESPACE NO
MARY CREATE ANY TABLE YES
[oracle@desktop241 ~]$ sqlplus mary/mary123
SQL*Plus: Release 10.2.0.5.0 - Productionon Wed Oct 17 21:44:26 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> create table kitty.ttt(i int);
【mary用户具有any权限】
Table created.
SQL> grant create any table to john;
【mary用户具有转受权限】
Grant succeeded.
SQL> select * from dba_sys_privs wheregrantee='JOHN';
【证实john已被转受权限】
GRANTEE PRIVILEGE ADM
---------------------------------------------------------------------- ---
JOHN CREATE ANY TABLE NO
SQL> revoke create any table from mary;
【从mary处回收any系统权限】
Revoke succeeded.
SQL> select * from dba_sys_privs wheregrantee='JOHN';
【被转授给他人的系统权限并不会随之收回】
GRANTEE PRIVILEGE ADM
---------------------------------------------------------------------- ---
JOHN CREATE ANY TABLE NO
SQL> grant select on kitty.test to marywith grant option;
【赋予用户对象权限并允许转受他人】
Grant succeeded.
SQL> select * from dba_tab_privs wheregrantee='MARY';
GRANTEE OWNER
------------------------------ ------------------------------
TABLE_NAME GRANTOR
------------------------------------------------------------
PRIVILEGE GRA HIE
------------------------------------------- ---
MARY KITTY
TEST KITTY
SELECT YES NO
[oracle@desktop241 ~]$ sqlplus mary/mary123
SQL*Plus: Release 10.2.0.5.0 - Productionon Wed Oct 17 22:07:08 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> select * from kitty.test;【Mary用户具有对象权限】
no rows selected
SQL> grant select on kitty.test to john;
【mary用户具有转手权限
Grant succeeded.
SQL> select * from dba_tab_privs wheregrantee='JOHN';
【证实john 已被转手权限】
GRANTEE OWNER
------------------------------------------------------------
TABLE_NAME GRANTOR
------------------------------------------------------------
PRIVILEGE GRA HIE
------------------------------------------- ---
JOHN KITTY
TEST MARY
SELECT NO NO
SQL> revoke select on kitty.test frommary;
【从mary处回收select 对象权限】
Revoke succeeded.
SQL> select * from dba_tab_privs wheregrantee='JOHN';
【被转手给他人的对象权限会随之收回】
no rows selected
角色创建及授权
角色是一组权限的集合，用户将一组定制的权限一次性授予给角色，使得需将大批权限授予给多个用户时更加方便，同时在其后的权限管理中，通过对该角色权限的修改，即能达到多个用户的权限被同步进行修改的目的。
SQL> select * from dba_roles;
【查系统中所有角色，oracle默认的最主要5个定义角色包括】
Connect角色用于授予用户以链接会话的权限
Resource角色用于授予用户进行数据库对象的基本操作权限
Dba具有全部系统权限
Exp_full_database/imp_full_database具有卸出与装入数据库的权限
ROLE PASSWORD
------------------------------ --------
CONNECT NO
RESOURCE NO
DBA NO
SELECT_CATALOG_ROLE NO
EXECUTE_CATALOG_ROLE NO
DELETE_CATALOG_ROLE NO
EXP_FULL_DATABASE NO
IMP_FULL_DATABASE NO
RECOVERY_CATALOG_OWNER NO
GATHER_SYSTEM_STATISTICS NO
SQL> select * from role_sys_privs whererole='CONNECT';
ROLE PRIVILEGE ADM
------------------------------ -------------------------------------------
CONNECT CREATE SESSION NO
SQL> select * from role_sys_privs whererole='RESOURCE';
ROLE PRIVILEGE ADM
---------------------------------------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
SQL> select * from role_sys_privs whererole='DBA';
ROLE PRIVILEGE ADM
---------------------------------------------------------------------- ---
DBA CHANGE NOTIFICATION YES
DBA ADMINISTER ANY SQL TUNING SET YES
DBA ALTER ANY SQL PROFILE YES
DBA CREATE RULE YES
DBA EXPORT FULL DATABASE YES
【查看角色所拥有的系统权限】
SQL> create role role1;
Role created.
【创建角色
SQL> create role role2;
Role created.
SQL> grant connect,create table torole1;
【将预定义角色和权限授予给新建角色】
Grant succeeded.
SQL> grant insert,select on kitty.testto role2;
【对象权限（须单独授权）授予给新建角色
Grant succeeded.
SQL> grant insert (empno) on scott.empto role1;
【对象上指定列的插改权限授予角色（不能指定查删权限）】
Grant succeeded.
SQL> select * from role_sys_privs whererole='ROLE1';
【查询角色所拥有的系统权限】
ROLE PRIVILEGE ADM
---------------------------------------------------------------------- ---
ROLE1 CREATE TABLE NO
SQL> select * from role_tab_privs where role='ROLE2';
【查询角色所拥有的对象权限及对象上指定列权限】
ROLE OWNER
------------------------------------------------------------
TABLE_NAME COLUMN_NAME
------------------------------------------------------------
PRIVILEGE GRA
-------------------------------------------
ROLE2 KITTY
TEST
SELECT NO
ROLE2 KITTY
TEST
INSERT NO
ROLE OWNER
------------------------------------------------------------
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
PRIVILEGE GRA
-------------------------------------------
SQL> select * from role_role_privs whererole in ('ROLE1','ROLE2');
【查看角色所拥有的角色权限】
ROLE GRANTED_ROLE ADM
------------------------------------------------------------ ---
ROLE1 CONNECT NO
SQL> grant role1,role2 to mary;
【将新建角色授予给用户】
Grant succeeded.
SQL> revoke role2 from mary;
【从用户处收回角色】
Revoke succeeded.
SQL> drop role role1;
【删除角色】
Role dropped.
【删除角色后，原拥有该角色的用户即失去该角色所拥有的所有权限】
角色使用注意的事项：
DBA创建3个角色：A,B和C；
先由DBA将权限resource授予给角色A
再将角色A授予角色B，再将角色B授予角色C
1. 不支持角色循环授权：角色C授予角色A时系统报错
2. 删除角色A：角色B和C的resource权限同时也被收回
3. 删除角色A：但角色B和角色C依然存在
修改用户
除用户名外其他各项均可以修改
SQL> alter user kitty identified by kitty123 default tablespacetestspace temporary tablespace temp profile my_profile;
SQL> alter user kitty account lock;
【用户立即锁定】
User altered.
SQL> alter user kitty account unlock;
【用户解锁】
User altered.
SQL> alter user kitty password expire;
【强制用户立即修改口令】
User altered.
SQL> connect kitty/kitty123
ERROR: ORA-28001: the password has expired
Changing password for kitty
New password:
SQL> conn sys/song as sysdba
Connected.
【转换身份登录】
SQL> select username,account_status fromdba_users;
【查用户状态】
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MARY OPEN
RMAN OPEN
MARY2 OPEN
TEST OPEN
SCOTT OPEN
JOHN OPEN
KITTY EXPIRED
HR EXPIRED & LOCKED
SYSTEM OPEN
SYS OPEN
MGMT_VIEW OPEN
监视用户
SQL> show user;
USER is "SYS"【查看当前登录用户名】
SQL> select username,sid,serial# fromv$session;
【查询用户会话信息】
USERNAME SID SERIAL#
------------------------------ --------------------
MARY 137 50
138 192
140 286
144 325
145 2
146 290
147 382
153 7
155 1
156 1
SYS 158 8
USERNAME SID SERIAL#
------------------------------ --------------------
160 1
161 1
162 1
SQL> alter system kill session'sid,serial';
【中断用户会话】
SQL> alter system kill session '137,50';
System altered.
删除用户（当前正连接的用户不能删除）
SQL> drop user kitty;
drop user kitty
*【用户未创建对象时】
ERROR at line 1:
ORA-01922: CASCADE must be specified todrop 'KITTY'
SQL> drop user kitty cascade;
【用户已创建对象时】
User dropped.
Sysdba & sysoper
相同点：
两者都是系统权限（查system_privilege_map可见），都可启停数据库。归档日志，create spfile，使用两者无需启动实例。
不同点：
1：前者登录时使用的schema是sys(show user可见)，后者登录时使用的schema是public（show user 可见）。
3. 前者有查看普通用户数据（select* from scott.emp）的权限，建删库（startup nomount;create/drop database iii;）的权限，改字符集（startup mount;alter system enablerestricted session;alter database character set zhs16gbk;）的权限，及执行不完全恢复的权限，而后者没有。
Sysdba(and sysoper)vs dba
相同点：
3者都是oracle9引入的，代替oracle8的internal和svrmgrl;
不同点：
1. 前者是一种特殊的系统权限（查system _privilege_map可见）；后者是一种角色权限（查dba_roles可见），且不包括sysdba/sysoper系统权限。
2. 对于前者，因为grant sysdba/sysoper to user后权限是保存在口令文件V$pwfile_users中的所以使用时无需启动实例；对于后者，因为grant dba to user后权限是保存在系统数据字典dba_role_privs中的所以依赖于实例启动后才能使用。
3. 前者是一种登录认证时的身份标识，用来执行数据库管理任务如启停DB，create spfile及恢复DB等库外操作（而后者无法实现）；后者是对DB内对象的操作权限的集合。
4. 任何用户均可以前者身份登录，登录后show user是sys/public而不是用户自己；对于后者，表示用户进入到DB中后具有DBA权限，登录后show user是用户自己。（因为前者以sysdba登录后实际是sys用户而后者登录仍是dba权限的自己，因此前者权限大于后者）
扫一扫
6135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
