1 Oracle數(shù)據(jù)庫用戶管理

創(chuàng)建用戶

create user username identified by passwd
default tablespace tablespace_name
temporary tablespace tablespace_name
quota  100M on tablespace_name[quota unlimited on users];

鎖/解鎖用戶

alter user username account lock;
alter user username account unlock;

獲取創(chuàng)建用戶的語句

set long 9999;
select dbms_metadata.get_ddl('USER','SCOTT') from dual;

修改配額

alter user tw quota 200M on users;

修改用戶默認表空間

alter user  username default tablespace tablespace_name temporary tablespace  tablespace_name  ;

常用拼接sql埠况，授權(quán)語句

select 'grant select on '||owner||'.'||table_name||'  to User2; ' from dba_tables where owner=upper('User1');

select 'grant '||privilege||' on '||owner||'.'||table_name||' to User2;' from dba_tab_privs where grantee=upper('User1');

2 程序用戶筷凤、開發(fā)昭殉、測試人員賬號管理

程序用戶

create user User01 identified by xxx 
default tablespace Tbs_dat_01
profile  default;

# 配額授權(quán)
alter user User01 quota unlimited on Tbs_dat_01;
alter user User01 quota 0 on system;
alter user User01 quota 0 on sysaux;
alter user User01 quota 0 on users;

# 用戶權(quán)限
grant connect,RESOURCE to User01;
grant CREATE VIEW to User01;
grant CREATE SYNONYM to User01;
grant DEBUG CONNECT SESSION to User01;
grant DEBUG ANY PROCEDURE to User01;
grant execute ANY PROCEDURE to User01;

# 刪除用戶
drop user  User01 cascade;

開發(fā)、測試人員只讀賬號

create user User02  identified by "xxx" profile user_profile   default tablespace Tbs_dat_01;
grant create session to User02;

# 將生產(chǎn)User1表授權(quán)給User02只讀用戶:
select 'grant select on '||owner||'.'||table_name||'  to User02; ' from dba_tables where owner=upper('User1');

# select any table ,select any dictionary 視情況授權(quán)

3 Profile管理

profile說明:

# 口令策略參數(shù)

FAILED_LOGIN_ATTEMPTS
PASSWORD_LOCK_TIME         ：用于指定指定賬戶被鎖定的天數(shù)(單位：天)嵌施。
PASSWORD_LIFE_TIME         :  指定口令的效期(單位：天)饲化。如果在達到效期前用戶還沒更換口令，它的口令將失效吗伤，這時必須由DBA 為它重新設(shè)置新的口令  
PASSWORD_GRACE_TIME        ：用于指定口令失效的寬限期(單位：天)
PASSWORD_REUSE_TIME         ：指定能夠重復使用一個口令前必須經(jīng)過的時間(單位：天)吃靠。
PASSWORD_REUSE_MAX         ：用于指定在重復使用口令之前必須對口令進行修改的次數(shù)。
PASSWORD_REUSE_TIME 和PASSWORD_REUSE_MAX 兩個參數(shù)只能設(shè)置一個足淆，另一個必須為UNLIMITED巢块。
PASSWORD_VERIFY_FUNCTION    ：指定驗證口令復雜度的函數(shù)

# 資源限制參數(shù)

CPU_PER_SESSION        ：限制每個會話所能使用的CPU 時間
SESSIONS_PER_USER    ：限制每個用戶所允許建立的最大并發(fā)會話數(shù)。
CONNECT_TIME            ：限制每個會話能連接到數(shù)據(jù)庫的最長時間巧号，超過這個時間會話將自動斷開族奢。
IDLE_TIME                     ：限制每個會話所允許的最長連續(xù)空閑時間，超過這個時間會話將自動斷開
LOGICAL_READS_PER _SESSION    ：限制每個會話所能讀取的數(shù)據(jù)塊數(shù)目
PRIVATE_SGA                ：每個會話分配的私SGA 區(qū)大械ず琛（以字節(jié)為單位
CPU_PER_CALL              ：用于指定每條SQL 語句可占用的最大CPU 時間越走，單位是百分之一秒。
LOGICAL_READS_PER_CALL        ：用于指定每條SQL 語句最多所能讀取的數(shù)據(jù)塊數(shù)目。

# 鎖定帳戶
FAILED_LOGIN_ATTEMPTS        : 參數(shù)指定在鎖定帳戶前嘗試登錄失敗的次數(shù)廊敌。
PASSWORD_LOCK_TIME           : 參數(shù)指定在經(jīng)歷指定的嘗試登錄失敗次數(shù)后鎖定帳戶的天數(shù)铜跑。

自定義profile: sys_system_profile、user_profile

create profile SYS_SYSTEM_PROFILE limit
            password_life_time 83
            password_grace_time 7
            password_reuse_max 5
            password_verify_function verify_function
            failed_login_attempts 6
            Password_lock_time 3;

create profile USER_PROFILE limit
            password_verify_function verify_function;

alter user sys profile sys_system_profile;
alter user system profile sys_system_profile;

# 程序骡澈，系統(tǒng)用戶不建議設(shè)置密碼過期策略：
alter profile  sys_system_profile  limit password_life_time unlimited;
# alter profile  user_profile limit password_life_time unlimited;

verify_function創(chuàng)建腳本:

CREATE OR REPLACE FUNCTION verify_function
                                        (  username varchar2,
                                           password varchar2,
                                       old_password varchar2
                                         )
  RETURN boolean IS 
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   db_name varchar2(40);
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
   i_char varchar2(10);
   simple_password varchar2(10);
   reverse_user varchar2(32);

BEGIN 
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
   END IF;


   -- Check if the password is same as the username or username(1-100)
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20002, 'Password same as or similar to user');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to user name ');
      END IF;
    END LOOP;

   -- Check if the password is same as the username reversed
   
   FOR i in REVERSE 1..length(username) LOOP
     reverse_user := reverse_user || substr(username, i, 1);
   END LOOP;
   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
     raise_application_error(-20003, 'Password same as username reversed');
   END IF;

   -- Check if the password is the same as server name and or servername(1-100)
   select name into db_name from sys.v$database;
   if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
      raise_application_error(-20004, 'Password same as or similar to server name');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to server name ');
      END IF;
    END LOOP;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
      raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password is the same as oracle (1-100)
    simple_password := 'oracle';
    FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if simple_password || i_char = NLS_LOWER(password) THEN
        raise_application_error(-20007, 'Password too simple ');
      END IF;
    END LOOP;

   -- Check if the password contains at least one letter, one digit 
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;

   IF isdigit = FALSE THEN
      raise_application_error(-20008, 'Password must contain at least one digit, one character');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20009, 'Password must contain at least one \
              digit, and one character');
   END IF;


   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);

     differ := abs(differ);
     IF differ < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;

       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;

       IF differ < 3 THEN
         raise_application_error(-20011, 'Password should differ from the \
            old password by at least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;   
   RETURN(TRUE);
END;
/

4 用戶管理中常見問題

當不知道數(shù)據(jù)庫用戶密碼情況下锅纺，需要同步密碼到另外一個用戶時，可以查看密文并同步：

SQL> select NAME,PASSWORD from sys.user$ where name in upper('USER1');

NAME                           PASSWORD
------------------------------ ------------------------------
USER1                          E2345A7546479F56

# 備份user2的密碼:
SQL> select NAME,PASSWORD from sys.user$ where name in upper('USER2');

NAME                           PASSWORD
------------------------------ ------------------------------
USER2                          1B6DE6A8B54F0C49

# 修改User2的密碼
SQL> alter user USER2 identified by values 'E2345A7546479F56';

User altered.

SQL> select NAME,PASSWORD from sys.user$ where name in upper('USER2');

NAME                           PASSWORD
------------------------------ ------------------------------
USER2                          E2345A7546479F56

附:生成隨機密碼函數(shù)及用法:

函數(shù)創(chuàng)建

CREATE OR REPLACE FUNCTION RANDOM_PASSWORD (IN_TEMPLATE IN VARCHAR2)  
RETURN VARCHAR2 IS  
                  LC$CRITERIA VARCHAR2(1);  
                  LC$PASSWORD VARCHAR2(500);  
                  LC$PATTERN VARCHAR2(500);  
                  LN$INDX NUMBER;  
BEGIN  
 /*
 1-Character should be UPPERCASE     =====> Abbreviation [U]  
 2- Character should be LOWERCASE      =====> Abbreviation [L]  
 3- Character should be NUMBER         =====> Abbreviation [N]  
 4- Character should be any character     =====> Abbreviation [A]  
 5- Character should be NON-ALPHANUMERIC character =====> Abbreviation [S]
 */  
     LC$CRITERIA := '';  
     LC$PASSWORD := '';  
     FOR I IN 1.. LENGTH(IN_TEMPLATE) LOOP  
       LC$CRITERIA := SUBSTR(IN_TEMPLATE,I,1);  
       IF UPPER(LC$CRITERIA ) = 'U'    THEN   
         LC$PATTERN := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZ]';  
       ELSIF UPPER(LC$CRITERIA ) = 'L' THEN   
          LC$PATTERN := q'[abcdefghijklmnopqrstuvwxyz]';  
       ELSIF UPPER(LC$CRITERIA ) = 'N' THEN   
          LC$PATTERN := q'[0123456789]';  
       ELSIF UPPER(LC$CRITERIA ) = 'A' THEN   
          LC$PATTERN := q'[0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]';  
       ELSIF UPPER(LC$CRITERIA ) = 'S' THEN   
          LC$PATTERN := q'[~!@#$%^&*()_+-}{|":;?.,<>[]/\]';       
       ELSE  
          LC$PATTERN := q'[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789]';  
       END IF;  
       LN$INDX := TRUNC( LENGTH(LC$PATTERN) * DBMS_RANDOM.VALUE) + 1;  
       LC$PASSWORD := LC$PASSWORD || SUBSTR(LC$PATTERN,LN$INDX,1);  
   END LOOP;  
   RETURN LC$PASSWORD;  
END RANDOM_PASSWORD;  
/

使用方法:

SELECT RANDOM_PASSWORD ('ULASULLS') FROM DUAL;

python隨機生成密碼腳本:

#!/usr/bin/env python
# -*- coding:utf-8 -*-

import random, string

def GenPassword(length):
    numOfNum = random.randint(1,length-1)
    numOfLetter = length - numOfNum
    slcNum = [random.choice(string.digits) for i in range(numOfNum)]
    slcLetter = [random.choice(string.ascii_letters) for i in range(numOfLetter)]
    slcChar = slcNum + slcLetter
    random.shuffle(slcChar)
    genPwd = ''.join([i for i in slcChar])
    return genPwd

if __name__ == '__main__':
    print GenPassword(8)