Application Specific Logon Trigger


We have a problem of multiple accounts accessing the same base schema. Currently, we use synonyms to solve this.

I would like to reduce the number of objects (including synonyms)

The Grand Design

The user would connect via a TNSNAMES alias called XE_APPL which SQL*Net (via the client and listener) directs to the APPL service on the XE Server which then connects the user to the internal MYAPPL service.

Anyone who connects to the MYAPPL internal service, has their CURRENT SCHEMA set to MYAPPL.

Service Names for Logon Trigger Example

Set Up the Database

For this exercise, I used Oracle Database XE (a free product).

Set up the Service

This consists of two (2) parts:

  1. Client-side connection parameters
  2. Server-side connection parameters
For Vista
1. From the Program Launcher, open the Oracle Database 10G Express Edition folder.
2. Right-click on Stop Database, and select Run as Administrator.
3. Click Continue on the dialog.
4. Wait for the OracleServiceXE service to stop. Close the command window.
5. From the Program Launcher, open the Accessories folder.
6. Right-click on Notepad, and select Run as Administrator.
7. Click Continue on the dialog.
8. Open the tnsnames file (see below) and perform the update. Save and close.
9. From the Program Launcher, open the Oracle Database 10G Express Edition folder.
10. Right-click on Start Database, and select Run as Administrator.
11. Click Continue on the dialog.
12. Wait for the OracleServiceXE service to start. (Ignore the message about the OracleXETNSListener already running.) Close the command window.

Updated c:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora:

 
XE_APPL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = APPL)
    )
  )

Create a service within the XE database, then start it:

connect / as sysdba
exec dbms_service.create_service( 'MYAPPL', 'APPL' );
exec dbms_service.start_service( 'MYAPPL' );

To verify that the service is now available:

>cd c:\oraclexe\app\oracle\product\10.2.0\server\BIN
>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 11-DEC-2008 03:52:33

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "APPL" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

Also, use the Run SQL Command:

SQL> connect system@xe_appl
Enter password:
Connected.

At this stage, there is nothing special about the XE_APPL alias over the XE one.

Security Model

Schema Purpose Account Locked? CREATE SESSION SELECT ON sys.dba_users
APPL_USER Test user No Yes No
APPL_SECURITY Holds the security programs for access by APPL_USER Yes No Yes
MYAPPL Target schema Yes No No

Set this security model up (passwords have been suppressed):

CONNECT / AS SYSDBA
CREATE USER appl_user IDENTIFIED BY "********" ACCOUNT UNLOCK;
GRANT CREATE SESSION TO appl_user;
CREATE USER appl_securiy IDENTIFIED BY "********" ACCOUNT LOCK;
CREATE USER myappl IDENTIFIED BY "*******" ACCOUNT LOCK;
GRANT SELECT ON sys.dba_users TO appl_security;

I have just introduced a big security risk by allowing the APPL_SECURITY user to view the encrypted passwords of all database users. Great for running a password-cracking program.

Against this, I have moved application code out of the SYS schema.

LOGON TRIGGER

The LOGON trigger is created for just the APPL_USER schema. Only this user will go through the trigger. In case of a bug, only this user will be affected.

CREATE OR REPLACE TRIGGER appl_security.logon
  AFTER LOGON
  ON appl_user.SCHEMA
  DECLARE
    --
    -- Constants
    --
    -- The SQL statement to change the current schema
    c_change_schema_sql
      CONSTANT
      VARCHAR2(512)
      NOT NULL
        := 'ALTER SESSION SET current_schema=';
    --
    -- Variables
    --
    -- Contains the internal service name from SYS_CONTEXT
    --  Default maximum length returned by SYS_CONTEXT is 256.
    --  The SYS_CONTEXT function can return a NULL value.
    l_service_name
      VARCHAR2(256)
        := NULL;
    --
    -- Cursors
    --
    -- Cursor for checking existence of user name
    CURSOR csr_user_exists(
        p_user_name
          VARCHAR2
      ) IS
        SELECT
            NULL
          FROM
            dba_users
          WHERE
            username = p_user_name
    ;
  BEGIN
    l_service_name := SYS_CONTEXT ( 'USERENV', 'SERVICE_NAME' );
    -- If the service name is 'XE' (because this is an XE database), or NULL, then
    --   simply exit the trigger without further ado.
    IF l_service_name IS NULL OR
       l_service_name = 'XE' THEN
      RETURN;
    END IF;
    -- If the supplied service is not the same as an
    --   existing user name, then simply exit the logon trigger.
    FOR l_user_found IN csr_user_exists( l_service_name )
    LOOP
      EXECUTE IMMEDIATE c_change_schema_sql || l_service_name;
    END LOOP;
  END;
/

Testing

Create the test data:

CONNECT / AS SYSDBA
CREATE TABLE myappl.visible TABLESPACE users AS SELECT * FROM dual;
CREATE TABLE myappl.invisible TABLESPACE users AS SELECT * FROM dual;
GRANT SELECT ON myappl.visible TO appl_user;

The idea is that once APPL_USER connects, the user will only see the VISIBLE table in the current schema.

First, connect with any special service selected. Should be the XE service by defauly.

CONNECT appl_user
Enter password:
Connected.
SQL> select table_name from user_tables;

no rows selected

SQL> desc visible
ERROR:
ORA-04043: object visible does not exist

SQL> desc invisible
ERROR:
ORA-04043: object invisible does not exist

SQL>

Now, connect using the XE_APPL alias:

SQL> CONNECT appl_user@xe_appl
Enter password:
Connected.
SQL> select table_name from user_tables;

no rows selected

SQL> desc visible
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 DUMMY                                              VARCHAR2(1)

SQL> desc invisible
ERROR:
ORA-04043: object invisible does not exist

SQL>

Conclusion

Works as expected. The security model is still enforced.

Advertisements

One thought on “Application Specific Logon Trigger

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s