Oracle 限制用户登录

—create trigger

Create Or Replace Trigger DENY_LOGIN  
 After Logon On Database
Declare
 v_Program Varchar2(48);
 v_Message Varchar2(1000);
 v_deny_Client Exception;
 v_deny_User Exception;
 v_deny_Ip Exception;
Begin
 Select Program Into v_Program From V$session Where Audsid = Sys_Context('USERENV', 'SESSIONID') And Rownum < 2;
 If  Lower(v_Program) = 'plsqldev.exe' Then
     Raise v_deny_Client;
 End If;
 If  User In ('AA','aa','MASTER','master') Then
     Raise v_deny_User;
 End If;
 If Sys_Context('USERENV', 'ip_address') = '192.168.37.1' Then
     Raise v_deny_Ip;
 End If;
Exception
 When v_deny_Client Then
   v_Message := 'Sorry！You cannot access database using this software client!';
   Raise_Application_Error(-20001, v_Message);
 When v_deny_User Then
   v_Message := 'Sorry！Database deny you('||User||') access,Contact DBAs please!';
   Raise_Application_Error(-20002, v_Message);
 When v_deny_Ip Then
   v_Message := 'Sorry！Database deny you('||Sys_Context('USERENV', 'SESSIONID') ||') access,Contact DBAs please!';
   Raise_Application_Error(-20003, v_Message);
 When Others Then
   v_Message := 'ERROR – NOT_LOGON TRIGGER- Please Contact Your DBA!!' || Sqlerrm;
   Raise_Application_Error(-20004, v_Message);
End;
/