本帖最后由 zcs0237 于 2013-6-7 22:36 编辑
oracle知识点精简总结系列持续增加中：
Oracle用户及用户配置文件精简总结
http://www.itpub.net/thread-1775065-1-1.html
Oracle的登陆认证方式精简总结
http://www.itpub.net/thread-1774785-1-1.html
实例恢复相关原理精简总结
http://www.itpub.net/thread-1761630-1-1.html
Oracle权限精简总结
http://www.itpub.net/thread-1775562-1-1.html
a.欢迎对本帖补充、建议、更正
b.测试环境rhel5.4+Ora10.2.0.1.0
c.为节省篇幅，部分输出结果做了精简
*************************************
第01部分 内置用户
SQL> select owner,count(*) from dba_segments group by owner;
MDSYS 125
TSMSYS 4
DMSYS 4
OUTLN 8
CTXSYS 74
OLAPSYS 249
HR 25
SYSTEM 355
EXFSYS 58
SCOTT 6
DBSNMP 25
ORDSYS 8
SYSMAN 760
OE 75
PM 40
SH 287
XDB 753
IX 26
SYS 1575
WMSYS 110
20 rows selected. //各用户下保存的对象数量
下面详细介绍最常用的SCOTT、 SYS、SYSTEM三个用户
01.1 用户SCOTT/TIGER
一、示例帐户，存储示例数据库
SQL> select OWNER,TABLE_NAME from all_tables where owner='SCOTT';
OWNER TABLE_NAME
------------------------ -------------
SCOTT DEPT
SCOTT EMP
SCOTT BONUS
SCOTT SALGRADE
SQL> select empno,ename,deptno,mgr from scott.emp where empno in(7499,7788);
EMPNO ENAME DEPTNO MGR
---------- ---------- ---------- ----------
7499 ALLEN 30 7698
7788 SCOTT 20 7566
二、有CONNECT和RESOURCE角色,默认未解锁
SQL> conn scott/tigger
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected. //登陆sys
SQL> alter user scott account UNLOCK;
User altered.
SQL> conn scott/tigger
ERROR:
ORA-01017: invalid username/password; logon denied
01.2 用户SYS/CHANGE_ON_INSTALL
任何用户以sysdba身份登录时都是sys，这一点，你登陆后执行show user可以验证，有点儿类似Linux里面的sudo。 SQL> conn scott/tiger as sysdba; Connected. SQL> show user; USER is "SYS" SQL> create table test(id int); Table created. SQL> select owner from dba_tables where table_name='TEST'; OWNER ------------------------------ SYS 一、实例的数据字典都在SYS下
数据库内很多重要的东西（数据字典表、内置包、静态数据字典视图等）都属于这个用户，sys用户必须以sysdba身份登录。
SQL> select count(*) from all_tables where owner='SYS';
COUNT(*)
----------
704
二、用数据库的超级用户，最高权限
sys用户拥有dba，sysdba，sysoper等。登陆em也只能sysdba，sysoper用这两个身份，不能用normal。 以sys用户登陆可查询到具有sysdba权限的用户，如：
SQL> show user
USER is "SYS"
SQL> select * from V_$PWFILE_USERS;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
TEST TRUE FALSE 三、不能以 NORMAL 登录
SQL> conn / as sysdba
Connected.
SQL> alter user sys identified by install;
User altered.
SQL> conn sys/install
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
01.3 用户SYSTEM/MANAGER
是数据库内置的一个普通管理员，你手工创建的任何用户在被授予dba角色后都跟这个用户差不多。
一、存有少量的实例对象
system用户用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。
二、次高权限，含AQ_ADMINISTRATOR_ROLE 和DBA角色
system用户拥有普通dba角色权限。system用户只能用normal身份登陆em，除非你对它授予了sysdba的系统权限或者syspoer系统权限。
SQL> conn system/manager
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn / as sysdba
Connected.
SQL> alter user system identified by manager;
User altered.
SQL> conn system/manager
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn system/manager as sysoper
Connected.
SQL> show user
USER is "PUBLIC"
SQL> select OWNER,count(*) from dba_segments where owner='SYSTEM' group by owner;
SYSTEM 355
*************************************
第02部分 用户管理
CONN / [AS {SYSDBA|SYSOPER}] CONN <user>[/<pass>][@<con_id>] [AS {SYSDBA|SYSOPER}] 一、 监视用户
1、查询用户基本配置信息
SQL>select username,default_tablespace,temporary_tablespace,account_status from dba_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ACCOUNT_STATUS
-------------------- -------------------- -------------------- --------------------
MGMT_VIEW SYSTEM TEMP OPEN
SYS SYSTEM TEMP OPEN
SYSTEM SYSTEM TEMP OPEN
DBSNMP SYSAUX TEMP OPEN
SYSMAN SYSAUX TEMP OPEN
SCOTT USERS TEMP OPEN
TEST USERS TEMP OPEN
ZCS USERS TEMP LOCKED(TIMED)
OUTLN SYSTEM TEMP EXPIRED & LOCKED
MDSYS SYSAUX TEMP EXPIRED & LOCKED
ORDSYS SYSAUX TEMP EXPIRED & LOCKED
EXFSYS SYSAUX TEMP EXPIRED & LOCKED
DMSYS SYSAUX TEMP EXPIRED & LOCKED
WMSYS SYSAUX TEMP EXPIRED & LOCKED
CTXSYS SYSAUX TEMP EXPIRED & LOCKED
ANONYMOUS SYSAUX TEMP EXPIRED & LOCKED
XDB SYSAUX TEMP EXPIRED & LOCKED
ORDPLUGINS SYSAUX TEMP EXPIRED & LOCKED
SI_INFORMTN_SCHEMA SYSAUX TEMP EXPIRED & LOCKED
OLAPSYS SYSAUX TEMP EXPIRED & LOCKED
TSMSYS USERS TEMP EXPIRED & LOCKED
BI USERS TEMP EXPIRED & LOCKED
PM USERS TEMP EXPIRED & LOCKED
MDDATA USERS TEMP EXPIRED & LOCKED
IX USERS TEMP EXPIRED & LOCKED
SH USERS TEMP EXPIRED & LOCKED
DIP USERS TEMP EXPIRED & LOCKED
OE USERS TEMP EXPIRED & LOCKED
HR USERS TEMP EXPIRED & LOCKED
29 rows selected.
2、查询用户SQL语句
SQL> select user_name, sql_text from v$open_cursor where USER_NAME='SCOTT';
USER_NAME SQL_TEXT
--------- -------------------------------------------
SCOTT SELECT USER FROM DUAL
二、增删改用户
场景：要导入一个用户的新数据，但还要保留原有用户。
SQL> create user zcs identified by zcs;
User created. // 建用户名zcs
SQL> select user#,name,password from user$ where name ='zcs';
no rows selected //查要更改的用户名
SQL> select user#,name,password from user$ where name ='ZCS';
USER# NAME PASSWORD
---------- -------------------- -----------------------
62 ZCS FAC360423812CA1A
SQL> update user$ set name='zcs1' where user#=62;
1 row updated. //更改用户名
SQL> commit;
Commit complete.
SQL> create user zcs identified by tiger;
ERROR at line 1: //提示用户冲突
ORA-01920: user name 'ZCS' conflicts with another user or role name
SQL> alter system checkpoint;
System altered. //强制写入数据文件
SQL> alter system flush shared_pool;
System altered. //清缓存字典信息，强制读更改后的数据
SQL> create user zcs identified by tiger;
User created. //再建相同的用户
SQL> grant connect,resource to zcs;
Grant succeeded.
SQL>drop user zcs1 CASCADE; //级联删除账户下的所有对象
三、限额（临时表空间不需限额）
A、特定表空间最大限额(KB或MB, 默认无限制的) B、UNLIMITED TABLESPACE权限：所有表空间无限额 SQL> conn / as sysdba;
Connected.
SQL> create user zcs1 identified by zcs quota 10M on users;
User created. //创建用户时，指定限额
SQL>select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ZCS1';
TABLESPACE_NAME USERNAME MAX_BYTES
------------------ -------- ----------
USERS ZCS1 1048576 //查询配额信息
SQL> alter user zcs1 quota 5M on users;
User altered. // 更改用户的表空间限额
SQL> select tablespace_name,username,max_bytes from DBA_TS_QUOTAS where username='ZCS1'; TABLESPACE_NAME USERNAME MAX_BYTES
------------------ -------- ----------
USERS ZCS1 5242880
*************************************
第03部分 用户资源配置文件
03.1 PROFILE基础知识
一、PROFILE的管理内容：资源限制、口令机制
SQL> select count(*) from dba_profiles;
COUNT(*)
----------
48
1、会话级资源限制：
CPU_PER_SESSION ：会话所能使用的CPU时间总量。单位是百分之一秒。
SESSIONS_PER_USER ：同一用户所能的开的最多会话数
CONNECT_TIME ：以分钟计算的会话持续连接时间
IDLE_TIME ：以分钟计算的空闲时间
LOGICAL_READS_PER_SESSION ：会话所能读取的数据块数。
PRIVATE_SGA ：在共享服务器模式下，每个用户连接所能使用的SGA中的空间，单位是字节。
如果开启了资源限制的会话，超出了上面这些限制，将会报出ORA-02391错误，ORACLE将同时断开会话的连接。
2、调用级资源限制：
主要针对每条命令的执行。每条命令的执行都是一次调用，调用级资源限制决定了每次调用所能使用的CPU时间和I/O数。调用级资源限制参数有上面两个：
CPU_PER_CALL ：每次调用所能占用的CPU时间，单位百分之一秒
LOGICAL_READS_PER_CALL ：每次调用所能读取的数据块数
二、如何管理一个PROFILE
1、启用资源限制
Alter system set RESOURCE_LIMIT=TRUE；
或
init<SID>.ora中设RESOURCE_LIMIT=true;startup force;
2、配置PROFILE
alter profile
3、分配给一个用户
create user zcs profile p1或Create user zcs profile p1
DROP PROFILE P1 ，zcs用户被自动加载DEFAULT PROFILE
03.2 口令限制与密码状态
PASSWORD_REUSE_TIME 3：3天内，能使用以前用过的老口令
PASSWORD_REUSE_MAX 3：3次设为口令后的串不能再被设为口令
一、open：正常状态
SQL> create user zcs identified by zcs; User created. SQL> grant connect,resource to zcs; Grant succeeded. SQL> select username,account_status from dba_users where username = 'ZCS'; USERNAME ACCOUNT_STATUS ------------- --------------- ZCS OPEN SQL> conn zcs/zcs Connected. 二、expired：需修改密码
SQL> conn / as sysdba Connected. SQL> alter user ZCS password expire; User altered. SQL> conn zcs/zcs ERROR: ORA-28001: the password has expired Changing password for zcs New password: Retype new password: Password changed Connected. 三、locked：手动禁用
SQL> conn / as sysdba Connected. SQL> alter user ZCS account lock; User altered. SQL> conn zcs/zcs ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. SQL> conn / as sysdba Connected. SQL> alter user ZCS account unlock; User altered. 四、expired (grace) ：即将过期
SQL> create profile p1 limit password_life_time 5 password_grace_time 5; Profile created. SQL> select PROFILE, RESOURCE_NAME, LIMIT from dba_profiles where PROFILE='P1' and RESOURCE_TYPE='PASSWORD'; PROFILE RESOURCE_NAME LIMIT --------- ------------ --------------------------- P1 FAILED_LOGIN_ATTEMPTS 3 P1 PASSWORD_LIFE_TIME DEFAULT P1 PASSWORD_REUSE_TIME DEFAULT P1 PASSWORD_REUSE_MAX DEFAULT P1 PASSWORD_VERIFY_FUNCTION DEFAULT P1 PASSWORD_LOCK_TIME 3 P1 PASSWORD_GRACE_TIME DEFAULT 7 rows selected. SQL> select username,account_status,profile from dba_users where username = 'ZCS'; USERNAME ACCOUNT_STATUS PROFILE -------------- --------------- ------------- ZCS OPEN DEFAULT SQL> alter user ZCS profile p1; User altered. SQL> select username,account_status,profile from dba_users where username = 'ZCS'; USERNAME ACCOUNT_STATUS PROFILE ----------------- ------------------ --------- ZCS OPEN P1 SQL> conn ZCS/ZCS Connected. 这是我们修改系统时间, 将日期往后修改几天
$ su Password: # date Tue Mar 22 18:42:26 CST 2005 # date '03281842' Mon Mar 28 18:42:00 CST 2005 # exit SQL> conn zcs/zcs ERROR: ORA-28002: the password will expire within 5 days Connected. SQL> conn / as sysdba Connected. SQL> select username,account_status,profile from dba_users where username = 'ZCS'; USERNAME ACCOUNT_STATUS PROFILE ----------- ---------------- --------------------- ZCS EXPIRED(GRACE) TEST 再将日期向后修改几天, 我们可以看到此时用户的状态已经改为Expired
SQL> host $ su Password: # date Sat Mar 23 15:10:19 CST 2013 # date '03311510' Sun Mar 31 15:10:00 CST 2013 # date Sun Mar 31 15:10:07 CST 2013 # exit exit $ exit exit SQL> select username,account_status,profile from dba_users where username = 'ZCS'; USERNAME ACCOUNT_STATUS PROFILE ------------ ----------------- ------------------ ZCS EXPIRED TEST SQL> conn zcs/zcs ERROR: ORA-28001: the password has expired Changing password for zcs New password: Retype new password: Password changed Connected. 五、locked(timed) ：因n次错误密码输入已被锁定
SQL> conn / as sysdba Connected. SQL> alter user ZCS profile default; User altered. SQL> drop profile p1; Profile dropped. SQL> create profile p1 limit password_lock_time 3 failed_login_attempts 3; Profile created. SQL> alter user zcs profile p1; User altered. SQL> conn zcs/zzz ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn zcs/zzz ERROR: ORA-01017: invalid username/password; logon denied SQL> conn zcs/zzz ERROR: ORA-01017: invalid username/password; logon denied SQL> conn zcs/zzz ERROR: ORA-28000: the account is locked | 
IP卡
狗仔卡
发表于 2013-3-24 19:20
收藏
提升卡
置顶卡
变色卡
显身卡
,好多都不知道呢
楼主
发表于 2013-3-26 14:30
