Skip to main content
๐Ÿ““ Tibero Online Manual Click here ๐Ÿ““

Search

Welcome to Tibero GTS!

How to Restrict Access Using Logon Trigger

Document Type | Technical Information

Category | Security

Document Number | TSETI002

 

Overview

Logon Trigger is a feature that restricts database access based on specific conditions such as IP or username (USERNAME).
Starting from version S1419, the trigger has been improved to operate on accounts other than the SYS account.
To use this feature, parameters must be set in the tip file or applied via SQL commands.
 

Required Settings

  • Add _DDL_TRIGGER_ENABLE=Y to the tip file
  • Or execute the following command in SQL
alter system set _ddl_trigger_enable=y;
 
 
 
 
 

Method

Example of Creating a Logon Trigger

SQL> alter system set _ddl_trigger_enable=y;

System altered.

SQL> create or replace trigger valid_user
  2   after logon on database
  3   begin
  4   if SYS_CONTEXT('USERENV','SESSION_USER') not in ('TEST')
  5   then
  6   raise_application_error (-20001,'Login not allowed from this USER');
  7   end if;
  8   end;
  9 /

Trigger 'VALID_USER' created.

 

Operation Example

  • SYS account can connect

SQL> exit
Disconnected.
ora11g@tbr5:/home/tbr5 $ tbsql sys/tibero

tbSQL 5  

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Connected to Tibero.

 

  • TEST account can connect

SQL> exit
Disconnected.
ora11g@tbr5:/home/tbr5 $ tbsql test/test

tbSQL 5  

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Connected to Tibero.

 

  • Error occurs when other accounts connect

SQL> exit
Disconnected.
ora11g@tbr5:/home/tbr5 $ tbsql tibero/tmax

tbSQL 5  

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

TBR-7440: The user defined error is called.  
 
TBR-20001: Login not allowed from this USER.   
TBR-15163: Unhandled exception at SYS.VALID_USER,  line 6.

 

 

 

 

Related to