OraFAQ Forum: Security » Bypassing ADMINISTER DATABASE TRIGGER

Home » RDBMS Server » Security » Bypassing ADMINISTER DATABASE TRIGGER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bypassing ADMINISTER DATABASE TRIGGER [message #628874]

Sat, 29 November 2014 09:45

Solomon Yakobson
Messages: 3303
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.

Report message to a moderator

Re: Bypassing ADMINISTER DATABASE TRIGGER [message #628876 is a reply to message #628874]

Sat, 29 November 2014 12:09

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Funny, same error.
Confirmed in 10.2.0.4 and 11.2.0.1.

Trace files show similar code paths.

Report message to a moderator

Re: Bypassing ADMINISTER DATABASE TRIGGER [message #633150 is a reply to message #628876]

Wed, 11 February 2015 11:14

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

Report message to a moderator

Re: Bypassing ADMINISTER DATABASE TRIGGER [message #633205 is a reply to message #633150]

Thu, 12 February 2015 04:15

Lalit Kumar B
Messages: 3174
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>

Report message to a moderator

Previous Topic:	Oracle 12C Cloud control instalation issue (unable to deployee agent software)
Next Topic:	name of computer

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 21:00:16 CDT 2025