DDL Trigger中再试行DDL

再create user时触发的trigger里面要执行alter user

前日看铺子群里商量一个须求,便是审计方必要数据库的客户创造之后要强制顾客校订口令,其实在建客户语句后,通过alter
user语句让口令过期就好了,不过审计方必要用trigger完结?难道是从9i一代遗留下来的规行矩步?

create TRIGGER indexTrigger
ON ALL SERVER
–FOR DROP_INDEX ,ALTER_INDEX,create_index
FOR DROP_INDEX
AS
declare @original_login sysname ,@login_name sysname,@user_name
sysname,@eventtype nvarchar(100),@commandText nvarchar(max),@dbname
sysname
select
@original_login=ORIGINAL_LOGIN(),
@login_name=SUSER_NAME(),
@user_name=CURRENT_USER,
@dbname=EVENTDATA().value(‘(/EVENT_INSTANCE/DatabaseName)[1]’,
‘sysname’) ,
@eventtype= EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’,
‘nvarchar(100)’) ,
@commandText=
EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)

在Create User时触发Trigger并获取客户新闻:How to Track CREATE USEPRADO /
DROP USE智跑 Statements Using A Trigger (Doc ID 339558.1)  

if not exists(select 1 from tempdb.sys.tables where
name=’indexTrigger’)
begin
    create table tempdb.DBO.indexTrigger(
        original_login sysname ,
        login_name sysname,
        user_name sysname,
        eventtype nvarchar(100),
        commandText nvarchar(max),
        dbname sysname,
        createtime datetime default getdate()
    )
end
insert
tempdb.dbo.indexTrigger([original_login],[login_name],[user_name],[eventtype],[commandText],dbname)
select
@original_login,@login_name,@user_name,@eventtype,@commandText,@dbname

还恐怕会遇上七个难题,再create user时触发的trigger里面要实践alter user
<username> password expire语句,就能触发ORA-30511错误,消除方式 :

 

https://dioncho.wordpress.com/2009/03/09/executing-ddl-in-the-ddl-trigger/

EVENTDATA
(Transact-SQL)