第十六章： 用户管理
1、schema ： user.object ，用户认证方式：os 认证，database 认证
2、建立 database认证的用户：
10:00:48 SQL> create user rose
10:14:46 2 identified by oracle
10:14:53 3 default tablespace users
10:14:58 4 temporary tablespace temp
10:15:08 5 quota 10m on users
10:15:18 6 password expire;
User created.
10:15:27 SQL> grant create session to rose;
Grant succeeded.
10:15:41 SQL> conn rose/oracle
ERROR:
ORA-28001: the password has expired
Changing password for rose
New password:
Retype new password:
Password changed
Connected.
10:15:47 SQL>
3、建立OS认证（操作系统认证）用户（sys 用户属于os 认证）
10:19:00 SQL> show parameter auth
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
10:19:00 SQL> show parameter auth
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
10:19:01 SQL> create user "OPS$ORACLE"
10:19:34 2 identified externally
10:19:42 3 profile default
10:19:49 4 default tablespace users
10:19:53 5 temporary tablespace temp
10:19:59 6 quota 10m on users;
User created.
10:20:07 SQL>
10:20:07 SQL> select username ,account_status from dba_users;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW OPEN
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
SYSMAN OPEN
SCOTT OPEN
ROSE OPEN
OPS$ORACLE OPEN
TOM OPEN
10:20:55 SQL> select username ,password ,PROFILE,DEFAULT_TABLESPACe,TEMPORARY_TABLESPACE from dba_users;
USERNAME PASSWORD PROFILE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------- ------------------------------ ---------- ------------------------------ ------------------------------
MGMT_VIEW 4F538DF5F344F348 DEFAULT SYSTEM TEMP
SYS 8A8F025737A9097A DEFAULT SYSTEM TEMP
SYSTEM 2D594E86F93B17A1 DEFAULT SYSTEM TEMP
DBSNMP FFF45BB2C0C327EC MONITORING SYSAUX TEMP
_PROFILE
SYSMAN 2CA614501F09FCCC DEFAULT SYSAUX TEMP
SCOTT F894844C34402B67 DEFAULT USERS TEMP
ROSE 1166A1F535AF6EFB DEFAULT USERS TEMP
OPS$ORACLE EXTERNAL DEFAULT USERS TEMP
10:23:05 SQL> grant create session to ops$oracle;
Grant succeeded.
10:23:14 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@work ~]$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)
[oracle@work ~]$ sqlplus / ----------登录不需要提供用户名和密码（oracle 必须属于os的dba组）
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 10 10:23:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
10:23:20 SQL>
10:23:20 SQL> show user;
USER is "OPS$ORACLE"
10:23:22 SQL>
4、quota 管理：（对象的最大存储空间，用户在表空间上建立对象，必须在相应的tablespace 上获得quota）
10:27:09 SQL> select TABLESPACE_NAME,USERNAME,bytes/1024,max_bytes/1024/1024 from dba_ts_quotas
10:27:11 2 where username='ROSE';
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024
------------------------------ ------------------------------ ---------- -------------------
USERS ROSE 0 10
------BYTES 已经使用过的配额，max_bytes 所分配的配额
10:28:18 SQL> grant create table to rose;
Grant succeeded.
10:28:23 SQL> grant select on scott.emp to rose;
Grant succeeded.
10:28:26 SQL> conn rose/rose
Connected.
10:28:29 SQL>
10:28:29 SQL> create table emp1 as select * from scott.emp;
Table created.
10:28:41 SQL> conn /as sysdba
Connected.
10:28:47 SQL>
10:28:47 SQL> select TABLESPACE_NAME,USERNAME,bytes/1024,max_bytes/1024/1024 from dba_ts_quotas
10:28:50 2 where username='ROSE';
TABLESPACE_NAME USERNAME BYTES/1024 MAX_BYTES/1024/1024
------------------------------ ------------------------------ ---------- -------------------
USERS ROSE 64 10
10:28:53 SQL>
-------回收quota
10:29:26 SQL> alter user rose quota 0 on users;
User altered.
10:30:01 SQL> select TABLESPACE_NAME,USERNAME,bytes/1024,max_bytes/1024/1024 from dba_ts_quotas
10:30:04 2 where username='ROSE';
no rows selected
10:29:30 SQL> conn rose/rose
Connected.
10:29:37 SQL>
10:29:37 SQL> insert into emp1 select * from emp1;
14 rows created.
10:29:51 SQL> /
28 rows created.
10:30:20 SQL> conn rose/rose
Connected.
10:31:51 SQL>
10:31:51 SQL> insert into emp1 select * from emp1;
56 rows created.
10:31:54 SQL> /
112 rows created.
10:31:56 SQL> /
224 rows created.
10:31:57 SQL> /
insert into emp1 select * from emp1
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
10:31:58 SQL> analyze table emp1 compute statistics;
Table analyzed.
10:32:16 SQL> select table_name,num_rows ,blocks,empty_blocks from user_tables;
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
EMP1 448 8 0
10:32:36 SQL>
5、删除用户：
10:33:56 SQL> select username,sid,serial# from v$session
10:34:08 2 where username is not null;
USERNAME SID SERIAL#
------------------------------ ---------- ----------
SYS 153 219
ROSE 159 55
10:34:15 SQL>
10:33:40 SQL>
10:33:40 SQL> drop user rose;
drop user rose
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
------强制关闭用户会话
10:34:15 SQL> alter system kill session '159,55';
System altered.
10:32:36 SQL> select * from emp1;
select * from emp1
*
ERROR at line 1:
ORA-00028: your session has been killed
10:35:23 SQL> drop user rose cascade;
User dropped.
10:36:18 SQL>
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/791805