1 Oracle数据库用户管理

创建用户

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;

获取创建用户的语句

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，授权语句

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 程序用户、开发、测试人员账号管理

程序用户

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

# 配额授权
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;

# 用户权限
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;

开发、测试人员只读账号

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

# 将生产User1表授权给User02只读用户:
select 'grant select on '||owner||'.'||table_name||'  to User02; ' from dba_tables where owner=upper('User1');

# select any table ,select any dictionary 视情况授权

3 Profile管理

profile说明:

# 口令策略参数

FAILED_LOGIN_ATTEMPTS
PASSWORD_LOCK_TIME         ：用于指定指定账户被锁定的天数(单位：天)。
PASSWORD_LIFE_TIME         :  指定口令的效期(单位：天)。如果在达到效期前用户还没更换口令，它的口令将失效，这时必须由DBA 为它重新设置新的口令  
PASSWORD_GRACE_TIME        ：用于指定口令失效的宽限期(单位：天)
PASSWORD_REUSE_TIME         ：指定能够重复使用一个口令前必须经过的时间(单位：天)。
PASSWORD_REUSE_MAX         ：用于指定在重复使用口令之前必须对口令进行修改的次数。
PASSWORD_REUSE_TIME 和PASSWORD_REUSE_MAX 两个参数只能设置一个，另一个必须为UNLIMITED。
PASSWORD_VERIFY_FUNCTION    ：指定验证口令复杂度的函数

# 资源限制参数

CPU_PER_SESSION        ：限制每个会话所能使用的CPU 时间
SESSIONS_PER_USER    ：限制每个用户所允许建立的最大并发会话数。
CONNECT_TIME            ：限制每个会话能连接到数据库的最长时间，超过这个时间会话将自动断开。
IDLE_TIME                     ：限制每个会话所允许的最长连续空闲时间，超过这个时间会话将自动断开
LOGICAL_READS_PER _SESSION    ：限制每个会话所能读取的数据块数目
PRIVATE_SGA                ：每个会话分配的私SGA 区大小（以字节为单位
CPU_PER_CALL              ：用于指定每条SQL 语句可占用的最大CPU 时间，单位是百分之一秒。
LOGICAL_READS_PER_CALL        ：用于指定每条SQL 语句最多所能读取的数据块数目。

# 锁定帐户
FAILED_LOGIN_ATTEMPTS        : 参数指定在锁定帐户前尝试登录失败的次数。
PASSWORD_LOCK_TIME           : 参数指定在经历指定的尝试登录失败次数后锁定帐户的天数。

自定义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;

# 程序，系统用户不建议设置密码过期策略：
alter profile  sys_system_profile  limit password_life_time unlimited;
# alter profile  user_profile limit password_life_time unlimited;

verify_function创建脚本:

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 用户管理中常见问题

当不知道数据库用户密码情况下，需要同步密码到另外一个用户时，可以查看密文并同步：

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

附:生成随机密码函数及用法:

函数创建

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)