disallow user to change password

----------
Forwarded message ----------
From: XIE WEN-MFK346
<wenxie at motorola.com>
Date:
2008/10/23
Subject: 禁止用户修改自己的密码
To:
xiewenxiewen at gmail.com















使用密码校验函数实现

















建一个用户
conn
/ as sysdba
create user b identified by b;
grant
connect,resource to b;












用户可以随便修改自己的密码
conn
b/b
alter user b identified by bb;
conn b/bb












建一个密码校验函数
conn
/ as sysdba





create or replace
function pwd_verify_func (
username varchar2,

password varchar2,
old_password varchar2
) return
boolean
is
begin
if user in ('SYS','SYSTEM') then

return true;
else
return false;

end if;
end;
/






只允许SYS,SYSTEM用户修改密码






建用户概要文件(profile)
create
profile pwd_profile limit password_verify_function
pwd_verify_func;
alter user b profile pwd_profile;












用户企图修改自己的密码
conn
b/bb
alter user b identified by b;











报错ORA-28221





!oerr ora 28221











必须执行REPLACE语句





alter user b
identified by b replace bb;











用户不能修改自己的密码了











用SYS用户可以修改
conn
/ as sysdba
alter user b identified by b;












修改一下函数中的提示信息





create or replace
function pwd_verify_func (
username varchar2,

password varchar2,
old_password varchar2
) return
boolean
is
begin
if user in ('SYS','SYSTEM') then

return true;
else

raise_application_error(-20001, 'You are not allowed to change the
password');
end if;
end;
/












conn b/b





alter user b
identified by bb replace b;























参考:







Password
Complexity Verification
@?/rdbms/admin/utlpwdmg.sql




























Xie Wen (谢文)




Network &

Operations,

Multimedia Applications & Services (MDB)

MOTOROLA Inc.

NO.104 mail box,

8th floor, Motorola Tower,

No.

1 Wang Jing East Road, Chao Yang District,

Beijing 100102 P. R.

China

e-mail wenxie at motorola.com








或者使用数据库级的触发器
Stopping a user from changing his own Oracle database password



-fin-