07/08/04 02:19:39
>>678
after alterトリガー使えばいいべ。
1. sysスキーマに監査用の表を作成
create table audit_password_change
(
username varchar2(10),
changed_by varchar2(10),
timestamp date
);
2. after alterトリガーをsysスキーマ内に作成
CREATE or REPLACE TRIGGER captuer_alter_user AFTER ALTER on database
BEGIN
IF (ora_dict_obj_type='USER' and (ora_dict_obj_name='SYSTEM' or ora_dict_obj_name='SYS') and ora_des_encrypted_password is not null) THEN
insert into audit_password_change
values (ora_dict_obj_name,
ora_login_user,
sysdate);
END IF;
END;
/
3. 実際にsysのパスワードを変更してみる。
SQL> show user
USER is "SYS"
SQL> alter user system identified by hogeboge;
4. 監査表をセレクト
SQL> select username, changed_by, to_char(timestamp,'YY/MM/DD HH24:MI:SS') changed_date from audit_password_change;
USERNAME CHANGED_BY CHANGED_DATE
---------- ---------- -----------------
SYSTEM SYS 07/08/03 13:13:51