Home » RDBMS Server » Security » Bypassing ADMINISTER DATABASE TRIGGER
Bypassing ADMINISTER DATABASE TRIGGER [message #628874] Sat, 29 November 2014 09:45 Go to next message
Solomon Yakobson
Messages: 2858
Registered: January 2010
Location: Connecticut, USA
Senior Member
There was a question on one of Oracle forums I participate on how to prevent user with default DBA role from logging from a certain ip address using after logon trigger. 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 ADMINISTER DATABASE TRIGGER privilege, but faking ORA-01092: ORACLE instance terminated, which is normally raised when instance was terminated abnormally does the trick.

SY.
Re: Bypassing ADMINISTER DATABASE TRIGGER [message #628876 is a reply to message #628874] Sat, 29 November 2014 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Funny, same error.
Confirmed in 10.2.0.4 and 11.2.0.1.

Trace files show similar code paths.

Re: Bypassing ADMINISTER DATABASE TRIGGER [message #633150 is a reply to message #628876] Wed, 11 February 2015 11:14 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'd be curious as to supports view, this does some seriously weird stuff after some experimenting.

Sys will still dig you out the hole though, no matter what you do (I stand to be corrected) - I can't find any way to make it run triggers on a startup - they dont exist to the DB at the point you "connect" and opening...well, that's after logon, isn't it Smile
Re: Bypassing ADMINISTER DATABASE TRIGGER [message #633205 is a reply to message #633150] Thu, 12 February 2015 04:15 Go to previous message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
Tried and got same result on 12.1.0.1,

SQL> select  granted_role,
  2              default_role
  3        from  user_role_privs
  4        where granted_role = 'DBA'
  5      /

GRANT DEF
----- ---
DBA   YES

SQL>
SQL>
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@pdborcl!'
 10                                       );
 11            end if;
 12    end;
 13  /

Trigger created.

SQL>
SQL> connect scott@pdborcl/tiger
Connected.
SQL>
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>
SQL> CONNECT scott@pdborcl/tiger
ERROR:
ORA-00600: internal error code, arguments: [opiodr: call 1], [], [], [], [], [], [], [], [], [],
[], []


Warning: You are no longer connected to ORACLE.
SQL> conn lalit@pdborcl/lalit
Connected.
SQL>
Previous Topic: Oracle 12C Cloud control instalation issue (unable to deployee agent software)
Next Topic: name of computer
Goto Forum:
  


Current Time: Sun Jun 16 19:35:44 CDT 2019