Oracle 用戶

1.關於創建用戶;
2.用戶配置文件;
3.創建用戶;
4.更改用戶;
5.刪除用戶;

1.關於創建用戶:
1.1 用戶名:創建數據庫用戶必須具有 Create user 系統權限,必須指定用戶名和密碼;用戶名字母大小寫不受限制,除非指定用戶名時使用了雙引號(如"tS"),這種形式創建的用戶使用時必須嚴格區分大小寫;
1.2 表空間:在未指定默認表空間時默認是:USERS表空間,表空間空間的使用默認是不允許寫入(我的理解是quota=0m);用戶創建的對象、DDL在未指定表空間時都會存儲在用戶默認表空間下;
1.3 臨時表空間: 在未指定臨時表空間時默認是:TEMP,當用戶執行的sql語句需要排序(sort)和join時需要使用臨時表空間;
1.4用戶配置文件(PROFILE):  PROFILE是控制和管理系統資源和密碼配置的系統文件,當數據庫用戶連接到數據庫時,數據庫加載用戶對應的配置文件參數值管理數據庫用戶資源使用和密碼參數配置情況,默認配置文件為:DEFAULT,有一定限制(如登錄錯誤次數,有效期),用戶可以根據業務需求創建用戶配置文件管理進程的資源使用和密碼參數配置,當一個進程所使用的資源超過配置文件設定值時,系統終止該進程,由 Process Monitor(PMON)後台進程清理該進程所使用的系統資源並返回給系統;

2.用戶配置文件:
2.1 查詢'DEFAULT' 用戶配置文件: 

   1:  OPS$SYWU@sydb>select profile,resource_name,resource_type,limit from dba_profiles where profile='DEFAULT';

   2:   

   3:  PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

   4:  ------------------------------ -------------------------------- -------- ----------------------------------------

   5:  DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED

   6:  DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED

   7:  DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED

   8:  DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED

   9:  DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

  10:  DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

  11:  DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED

  12:  DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED

  13:  DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED

  14:  DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10

  15:  DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180

  16:  DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

  17:  DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

  18:  DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

  19:  DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1

  20:  DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

可以根據系統業務情況自己創建用戶配置文件(要具有Create profile 系統權限):

   1:  OPS$SYWU@sydb>create profile test_profile limit

   2:    2  failed_login_attempts 3

   3:    3  logical_reads_per_session 1

   4:    4  /

   5:   

   6:  Profile created.

   7:   

   8:  OPS$SYWU@sydb>create profile general_profile limit 

   9:    2       COMPOSITE_LIMIT UNLIMITED

  10:    3       SESSIONS_PER_USER UNLIMITED

  11:    4       CPU_PER_SESSION UNLIMITED

  12:    5       CPU_PER_CALL UNLIMITED

  13:    6       LOGICAL_READS_PER_SESSION UNLIMITED

  14:    7       LOGICAL_READS_PER_CALL UNLIMITED

  15:    8       IDLE_TIME UNLIMITED

  16:    9       CONNECT_TIME UNLIMITED

  17:   10       PRIVATE_SGA UNLIMITED

  18:   11       FAILED_LOGIN_ATTEMPTS UNLIMITED

  19:   12       PASSWORD_LIFE_TIME UNLIMITED

  20:   13       PASSWORD_REUSE_TIME UNLIMITED

  21:   14      PASSWORD_REUSE_MAX UNLIMITED

  22:   15      PASSWORD_VERIFY_FUNCTION NULL

  23:   16       PASSWORD_LOCK_TIME UNLIMITED

  24:   17      PASSWORD_GRACE_TIME UNLIMITED 

  25:   18  /

  26:   

  27:  Profile created.

2.2 修改用戶配置文件參數(要具有 Alert Profile 系統權限):

   1:  OPS$SYWU@sydb>alter profile test_profile limit LOGICAL_READS_PER_SESSION unlimited;

   2:   

   3:  Profile altered.

2.3 刪除用戶配置文件(要具有 Drop Profile 系統權限),不能刪除默認配置文件'DEFAULT',當刪除已經分配給用戶的配置文件時,要加cascade子句,系統刪除配置文件並將關聯該配置文件的用戶配置文件修改為系統默認配置文件'DEFAULT':

   1:  SYS@sydb>select username,profile from dba_users where username='OPS$SYWU';

   2:   

   3:  USERNAME                       PROFILE

   4:  ------------------------------ ------------------------------

   5:  OPS$SYWU                       TEST_PROFILE

   6:   

   7:  SYS@sydb>drop profile test_profile cascade;

   8:   

   9:  Profile dropped.

  10:   

  11:  SYS@sydb>select username,profile from dba_users where username='OPS$SYWU';

  12:   

  13:  USERNAME                       PROFILE

  14:  ------------------------------ ------------------------------

  15:  OPS$SYWU                       DEFAULT

2.4 查詢當前Session 用戶配置文件系統參數的設置值:

   1:  OPS$SYWU@sydb>select resource_name,limit from user_resource_limits;

   2:   

   3:  RESOURCE_NAME                    LIMIT

   4:  -------------------------------- ----------------------------------------

   5:  COMPOSITE_LIMIT                  UNLIMITED

   6:  SESSIONS_PER_USER                UNLIMITED

   7:  CPU_PER_SESSION                  UNLIMITED

   8:  CPU_PER_CALL                     UNLIMITED

   9:  LOGICAL_READS_PER_SESSION        UNLIMITED

  10:  LOGICAL_READS_PER_CALL           UNLIMITED

  11:  IDLE_TIME                        UNLIMITED

  12:  CONNECT_TIME                     UNLIMITED

  13:  PRIVATE_SGA                      UNLIMITED

2.5 查詢當前Session 用戶配置文件密碼參數的設置值:

   1:  OPS$SYWU@sydb>select resource_name,limit from user_password_limits;

   2:   

   3:  RESOURCE_NAME                    LIMIT

   4:  -------------------------------- ----------------------------------------

   5:  FAILED_LOGIN_ATTEMPTS            10

   6:  PASSWORD_LIFE_TIME               180

   7:  PASSWORD_REUSE_TIME              UNLIMITED

   8:  PASSWORD_REUSE_MAX               UNLIMITED

   9:  PASSWORD_VERIFY_FUNCTION         NULL

  10:  PASSWORD_LOCK_TIME               1

  11:  PASSWORD_GRACE_TIME              7

3.創建用戶:
3.1 創建一個缺省用戶(要具有 Create User 系統權限):

   1:   OPS$SYWU@sydb>create user test 

   2:    2  identified by test

   3:    3  /

   4:   

   5:  User created.

   6:  OPS$SYWU@sydb>grant create session,resource to test;

   7:   

   8:  Grant succeeded.

系統使用缺省的默認表空間:Users(對該表空間的空間沒有使用權限),默認臨時表空間:temp,默認的用戶配置文件:DEFAULT,缺省用戶不具有任何權限,需要賦於權限後才能使用;

   1:  OPS$SYWU@sydb>select username,default_tablespace,temporary_tablespace,profile,account_status from dba_users where username='TEST';

   2:   

   3:  USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           PROFILE                        ACCOUNT_STATUS

   4:  ------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------------

   5:  TEST                           USERS                          TEMP                           DEFAULT                        OPEN

3.2 創建一個自定義非缺省用戶:

   1:  OPS$SYWU@sydb>create user test1

   2:    2  identified by test1

   3:    3  default tablespace tbs01

   4:    4  temporary tablespace temp

   5:    5  quota unlimited on tbs01

   6:    6  quota 100m on tbs02

   7:    7  profile general_profile

   8:    8  /

   9:   

  10:  User created.

  11:  OPS$SYWU@sydb>grant create session,resource to test1;

  12:   

  13:  Grant succeeded.

3.3 用戶表空間空間使用情況:

   1:  OPS$SYWU@sydb>conn test1/test1

   2:  TEST1@sydb>select tablespace_name,bytes,max_bytes,blocks,max_blocks from user_ts_quotas;

   3:   

   4:  TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS

   5:  ------------------------------ ---------- ---------- ---------- ----------

   6:  TBS02                                   0  104857600          0      12800

   7:  TBS01                                   0         -1          0         -1

MAX_BYTES =-1 表示unlimited;非-1表示用戶在該表空間上可以使用的空間最大值;

4.更改用戶:
可以在用戶創建後更改默認表空間、臨時表空間、空間限額、用戶配置文件、密碼,必須具有 Alter User 系統權限:
4.1 更改用戶默認表空間:

   1:  OPS$SYWU@sydb>alter user test1 default tablespace tbs02;

   2:   

   3:  User altered.

更改默認表空間同時更改空間限額:

   1:  OPS$SYWU@sydb>alter user test1 default tablespace tbs02 quota unlimited on tbs02;

   2:   

   3:  User altered.

4.2 更改用戶臨時表空間:

   1:  OPS$SYWU@sydb>alter user test1 temporary tablespace temp;

   2:   

   3:  User altered.

不能在臨時表空間上設置空間限額,臨時表空間的使用情況可以查詢v$tempfile和dba_temp_files 視圖:

   1:  OPS$SYWU@sydb>select file_name,tablespace_name,bytes,blocks,status,maxbytes,maxblocks,user_bytes,user_blocks from dba_temp_files;

   2:   

   3:  FILE_NAME     TABLESPACE_NAME                     BYTES     BLOCKS STATUS    MAXBYTES  MAXBLOCKS USER_BYTES USER_BLOCKS

   4:  ------------- ------------------------------ ---------- ---------- ------- ---------- ---------- ---------- -----------

   5:  /u01/app/orad TEMP                            378535936      46208 ONLINE  3.4360E+10    4194302  377487360       46080

   6:  ata/sydb/temp

   7:  01.dbf

4.3 更改用戶配置文件(要具有Alter Profile系統權限):

   1:  OPS$SYWU@sydb>alter user test1 profile default;

   2:   

   3:  User altered.

4.4 更改用戶密碼(要具有Alter User系統權限):

   1:  OPS$SYWU@sydb>alter user test1 identified by test1;

   2:   

   3:  User altered.

也可以通過password 命令修改:

   1:  OPS$SYWU@sydb>password test1

   2:  Changing password for test1

   3:  New password: 

   4:  Retype new password: 

   5:  Password changed

sys 用戶密碼也可以像這樣修改,但通常情況下sys密碼丟失或忘記,並且沒有其它用戶具有Alter User 系統權限時不得不通過在DBA用戶組下通過ORAPWD重新建立密碼文件;

   1:  [sywu@wusuyuan dbs]$ orapwd file=/u01/app/product/11.2.0/db_1/dbs/orapwsydb entries=10 force=y

   2:   

   3:  Enter password for SYS: 

Windows 下密碼文件在ORACLE_HOME/database/ ,密碼文件名稱格式為:pwdsid.ora;SO,Alter User 系統權限很大,應避免生成庫上使用;

以上更改用戶命令也可以這樣:

   1:  OPS$SYWU@sydb>alter user test1

   2:    2    identified by test1

   3:    3    default tablespace tbs01

   4:    4    quota unlimited on tbs01

   5:    5    quota 10m on users

   6:    6    temporary tablespace temp

   7:    7    profile test_profile 

   8:    8    /

   9:   

  10:  User altered.

5.刪除用戶:
刪除用戶必須具有Drop User系統權限,如果有主外鍵或其它用戶對象關聯時原則上應先檢查這些數據確認是否有用,確認後刪除對象關聯關系;也可以在刪除用戶時使用CASCADE語句刪除對象關聯關系並刪除用戶;可以通過檢查系統視圖確認關聯對象;

   1:   OPS$SYWU@sydb>SELECT OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER LIKE 'OPS$SYWU';

當有會話連接該用戶時,必須先斷開連接才能刪除用戶;

   1:  OPS$SYWU@sydb>drop user test1;

   2:  drop user test1

   3:  *

   4:  ERROR at line 1:

   5:  ORA-01940: cannot drop a user that is currently connected

可以這樣處理,先查詢出用戶會話信息:

   1:   OPS$SYWU@sydb>select sid,serial# ,username from v$session where username='TEST1';

   2:   

   3:         SID    SERIAL# USERNAME

   4:  ---------- ---------- ------------------------------

   5:          16         41 TEST1

在數據庫系統上殺死該會話進程:

   1:  OPS$SYWU@sydb>alter system kill session '16,41';

   2:   

   3:  System altered.

如果這樣殺不死,可以先查詢出進程信息,然後在操作系統上殺死進程:

   1:  OPS$SYWU@sydb>select spid, osuser,s.sid,s.username,s.program from

   2:  v$process p, v$session s where p.addr=s.paddr and s.username='TEST1';

linux 下用 kill殺死進程:

   1:  [sywu@wusuyuan ~]$ kill -s 9 2310

Windows 下用 oraKill(注:要在數據庫端操作):
orakill sid spid;
sid 是:實例名,spid 是進程ID(注意中間不要加逗號);
最後在刪除用戶:
OPS$SYWU@sydb>drop user test1 cascade;