Oracle DBA課程系列筆記（16）

第十六章：用戶管理

? 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

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';????????????????????????????????????????????????????????????????????????????????????????????????????

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>

本文轉自客居天涯 51CTO博客，原文鏈接：http://blog.51cto.com/tiany/791805，如需轉載請自行聯系原作者