CREATE OR REPLACE TRIGGER allen
AFTER LOGON ON database
BEGIN
execute immediate ' alter user raddisonlee account unlock ';
commit;
END;
-------- ok ,it pass in the sys account ! no any error message .... but when i change another account to logon oracle ,it is failture ! error code under :
ERROR:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-30511: 系统触发器中无效的 DDL 操作
ORA-06512: 在line 2
Since you have 8.1.6, you can use an ON CREATE trigger to do this perhaps.
Something like:
create or replace trigger do_grant
after CREATE on schema
declare
l_str varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
l_str := 'execute immediate "grant select on ' ||
ora_dict_obj_name ||
' to scott";';
dbms_job.submit( l_job, replace(l_str,'"','''') );
end if;
end;
/
That, when run in a given user account will schedule a job to grant SELECT on
any table created to SCOTT. We use dbms_job to run the grant in the background
because this is executing as part of a larger data dictionary transaction and we
cannot commit (nor can we use an autonomous transaction - the table is not yet
"fully" created)...
If you have job_queue_processes set to >0 and job_queue_interval set to some
reasonable number (I use 60 for 1 minute intervals), then scott will have access
to any table about 1 minute AFTER its created....
也就是说,在一个给定的用户帐户下运行时,将安排一个作业,授予SELECT权限对此用户上
创建的任何表给scott。我们使用DBMS_JOB在后台运行grant 是因为这是在执行一个较大的数据字典事务的一部分,我们
不能commit(也可以使用自治事务-该表是尚未 “完全”创建的)...
如果您已经JOB_QUEUE_PROCESSES设置为>0且JOB_QUEUE_INTERVAL设置一些
合理数量的(我用60,1分钟间隔),那么scott将 可以访问任何表在其创造了约一分钟后....