There was a question on one of Oracle forums I participate on how to write an after login trigger preventing logins from a certain ip address when user is granted DBA as default role. While detecting client ip address in after logon trigger is easy (ora_client_ip_address) issue is role DBA, which is default role for the user, has ADMINISTER DATABASE TRIGGER privilege which allows logging in regardless of errors thrown by a login trigger (this is done purposely as a failsafe - otherwise we can end up with a database nobody can connect to). Sounded like fun, so this is what I came up with:
SQL> connect scott/tiger
Connected.
SQL> select granted_role,
2 default_role
3 from user_role_privs
4 where granted_role = 'DBA'
5 /
GRANTED_ROLE DEF
------------------------------ ---
DBA YES
SQL> create or replace
2 trigger no_welcome_to_scott
3 after logon on database
4 begin
5 if ora_login_user = 'SCOTT'
6 then
7 raise_application_error(
8 -20900,
9 'No trespassing, Scott!'
10 );
11 end if;
12 end;
13 /
Trigger created.
SQL> connect scott/tiger
Connected.
SQL> create or replace
2 trigger no_welcome_to_scott
3 after logon on database
4 declare
5 e exception;
6 pragma exception_init(e,-1092);
7 begin
8 if ora_login_user = 'SCOTT'
9 then
10 raise e;
11 end if;
12 end;
13 /
Trigger created.
SQL> connect scott/tiger
ERROR:
ORA-00600: internal error code, arguments: [opiodr: call 1], [], [], [], [],
[], [], [], [], [], [], []
Warning: You are no longer connected to ORACLE.
SQL>
SQL> connect scott/tiger
ERROR:
ORA-00600: internal error code, arguments: [opiodr: call 1], [], [], [], [],
[], [], [], [], [], [], []
SQL> connect u1/u1
Connected.
SQL>
As you can see, raising application error has no effect since user has DBA role and therefore has ADMINISTER DATABASE TRIGGER privilege, but faking ORA-01092: ORACLE instance terminated which is normally raised when instance was terminated abnormally does the trick.