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)
