第十六章: 用户管理

  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>