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.
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:
- Client-side connection parameters
- Server-side connection parameters
|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.|
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.
|Schema||Purpose||Account Locked?||CREATE SESSION||SELECT ON sys.dba_users|
|APPL_SECURITY||Holds the security programs for access by APPL_USER||Yes||No||Yes|
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.
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; /
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>
Works as expected. The security model is still enforced.