To increase the level of security on one of the applications I support, I had to create a new user (we’ll call this one APPUSERREAD) in our Oracle Database that had read-only access to the previous application user schema (and this one will be APPUSER).
One of the challenges at this point was to avoid changing the queries that were hard-coded into the application. Unfortunately, these queries referenced tables without the schema name, assuming the default schema.
So, the first step was to alter the default schema of the APPUSERREAD account each time a session was created. Aside from adding the required code into the application, it seemed that a trigger would be the best option to do this.
The code below creates a trigger for the new schema that resets the default schema at each logon. This would have to be run while logged on as APPUSERREAD.
CREATE OR REPLACE TRIGGER LOGON_TRG
AFTER LOGON ON SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = APPUSER';
EXCEPTION
WHEN OTHERS
THEN NULL; -- prevent a login failure due to an exception
END;
Though this fixes the problem of omitting references to the APPUSER schema in the queries, we weren’t done yet.
As it turns out, the code also runs SELECT statements against two public synonyms: USER_TABLES and USER_VIEWS.
Even changing the default schema will not change the results of a query against these views.
Selecting USER_TABLES returns all tables owned by the logged on user (not schema), and USER_VIEWS similarly returns all views owned by the user.
To get something similar to the contents of the USER_TABLES for the APPUSER account when logged on as APPUSERREAD (or any other account), this query will give you what you need:
SELECT * FROM ALL_TABLES WHERE OWNER = 'APPUSER';
For USER_VIEWS, the query is similar:
SELECT * FROM ALL_VIEWS WHERE OWNER = 'APPUSER';
The only problem is that there is an OWNER column present in both of these result sets, whereas the OWNER is known as the current user for USER_TABLES and USER_VIEWS.
The solution was to create views in the original schema that only had the needed columns – and in the correct order – such that the new views mirrored the public synonyms.
To get the list of columns and their order, I used the LISTAGG command to return a comma-delimited list that would become the column list for each new view.
For USER_TABLES:
SELECT LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_ID) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='USER_TABLES' ORDER BY COLUMN_ID;
For USER_VIEWS:
SELECT LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_ID) FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='USER_VIEWS' ORDER BY COLUMN_ID;
Using the results from these queries, I built new views:
APPUSER.USER_TABLES:
CREATE VIEW APPUSER.USER_TABLES AS SELECT TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME, STATUS, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, DEGREE, INSTANCES, CACHE, TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED, IOT_TYPE, TEMPORARY, SECONDARY, NESTED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ROW_MOVEMENT, GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING, CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED, READ_ONLY, SEGMENT_CREATED, RESULT_CACHE, CLUSTERING, ACTIVITY_TRACKING, DML_TIMESTAMP, HAS_IDENTITY, CONTAINER_DATA, INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION, INMEMORY_DUPLICATE FROM ALL_TABLES WHERE OWNER = 'APPUSER';
APPUSER.USER_VIEWS:
CREATE VIEW APPUSER.USER_VIEWS AS SELECT VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME, EDITIONING_VIEW, READ_ONLY, CONTAINER_DATA, BEQUEATH, ORIGIN_CON_ID FROM ALL_VIEWS WHERE OWNER = 'APPUSER';
Since the default schema of APPUSERREAD is now APPUSER, a call to USER_TABLES or USER_VIEWS without specifying the schema, will retrieve these instead of the public synonyms.
Problem solved!
