Overriding the USER_TABLES and USER_VIEWS Public Synonyms

Oracle Database logo

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!

Error when Migrating SOAP Web Services from Adobe ColdFusion 8 to CF 11

ColdFusion logo

After moving a CF 8 app to CF 11, I ran into a difficulty in trying to set up the Web Service in the CF 11 Administrator.

CF Administrator error message

Since I couldn’t get the CF 11 server to accept a new entry in the Web Services list, I had to do a little more troubleshooting using what was already there. I changed the HOSTS file on the CF 11 server such that the name old server running the Web Service successfully on CF 8 would temporarily point at the new CF 11 server. This caused an interesting error!

The code that came back is below – slightly edited for readability and also to make if render in a browser correctly:

<!-- AxisFault faultCode: {http://xml.apache.org/axis/}HTTP faultSubcode: faultString: (401)Unauthorized faultActor: faultNode: faultDetail: {}:return code: 401 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>IIS 8.5 Detailed Error - 401.2 - Unauthorized</title> <style type="text/css"> <!-- body{margin:0;font-size:.7em;font-family:Verdana,Arial,Helvetica,sans-serif;} code{margin:0;color:#006600;font-size:1.1em;font-weight:bold;} .config_source code{font-size:.8em;color:#000000;} pre{margin:0;font-size:1.4em;word-wrap:break-word;} ul,ol{margin:10px 0 10px 5px;} ul.first,ol.first{margin-top:5px;} fieldset{padding:0 15px 10px 15px;word-break:break-all;} .summary-container fieldset{padding-bottom:5px;margin-top:4px;} legend.no-expand-all{padding:2px 15px 4px 10px;margin:0 0 0 -12px;} legend{color:#333333;;margin:4px 0 8px -12px;_margin-top:0px; font-weight:bold;font-size:1em;} a:link,a:visited{color:#007EFF;font-weight:bold;} a:hover{text-decoration:none;} h1{font-size:2.4em;margin:0;color:#FFF;} h2{font-size:1.7em;margin:0;color:#CC0000;} h3{font-size:1.4em;margin:10px 0 0 0;color:#CC0000;} h4{font-size:1.2em;margin:10px 0 5px 0; }#header{width:96%;margin:0 0 0 0;padding:6px 2% 6px 2%;font-family:"trebuchet MS",Verdana,sans-serif; color:#FFF;background-color:#5C87B2; }#content{margin:0 0 0 2%;position:relative;} .summary-container,.content-container{background:#FFF;width:96%;margin-top:8px;padding:10px;position:relative;} .content-container p{margin:0 0 10px 0; }#details-left{width:35%;float:left;margin-right:2%; }#details-right{width:63%;float:left;overflow:hidden; }#server_version{width:96%;_height:1px;min-height:1px;margin:0 0 5px 0;padding:11px 2% 8px 2%;color:#FFFFFF; background-color:#5A7FA5;border-bottom:1px solid #C1CFDD;border-top:1px solid #4A6C8E;font-weight:normal; font-size:1em;color:#FFF;text-align:right; }#server_version p{margin:5px 0;} table{margin:4px 0 4px 0;width:100%;border:none;} td,th{vertical-align:top;padding:3px 0;text-align:left;font-weight:normal;border:none;} th{width:30%;text-align:right;padding-right:2%;font-weight:bold;} thead th{background-color:#ebebeb;width:25%; }#details-right th{width:20%;} table tr.alt td,table tr.alt th{} .highlight-code{color:#CC0000;font-weight:bold;font-style:italic;} .clear{clear:both;} .preferred{padding:0 5px 2px 5px;font-weight:normal;background:#006633;color:#FFF;font-size:.8em;} --> </style> </head> <body> <div id="content"> <div class="content-container"> <h3>HTTP Error 401.2 - Unauthorized</h3> <h4>You are not authorized to view this page due to invalid authentication headers.</h4> </div> <div class="content-container"> <fieldset><h4>Most likely causes:</h4> <ul> <li>No authentication protocol (including anonymous) is selected in IIS.</li> <li>Only integrated authentication is enabled, and a client browser was used that does not support integrated authentication.</li> <li>Integrated authentication is enabled and the request was sent through a proxy that changed the authentication headers before they reach the Web server.</li> <li>The Web server is not configured for anonymous access and a required authorization header was not received.</li> <li>The "configuration/system.webServer/authorization" configuration section may be explicitly denying the user access.</li> </ul> </fieldset> </div> <div class="content-container"> <fieldset><h4>Things you can try:</h4> <ul> <li>Verify the authentication setting for the resource and then try requesting the resource using that authentication method.</li> <li>Verify that the client browser supports Integrated authentication.</li> <li>Verify that the request is not going through a proxy when Integrated authentication is used.</li> <li>Verify that the user is not explicitly denied access in the "configuration/system.webServer/authorization" configuration section.</li> <li>Create a tracing rule to track failed requests for this HTTP status code. For more information about creating a tracing rule for failed requests, click <a href="http://go.microsoft.com/fwlink/?LinkID=66439">here</a>. </li> </ul> </fieldset> </div> <div class="content-container"> <fieldset><h4>Detailed Error Information:</h4> <div id="details-left"> <table border="0" cellpadding="0" cellspacing="0"> <tr class="alt"><th>Module</th><td>&nbsp;&nbsp;&nbsp;IIS Web Core</td></tr> <tr><th>Notification</th><td>&nbsp;&nbsp;&nbsp;AuthenticateRequest</td></tr> <tr class="alt"><th>Handler</th><td>&nbsp;&nbsp;&nbsp;ISAPI-dll</td></tr> <tr><th>Error Code</th><td>&nbsp;&nbsp;&nbsp;0x80070005</td></tr> </table> </div> <div id="details-right"> <table border="0" cellpadding="0" cellspacing="0"> <tr class="alt"><th>Requested URL</th><td>&nbsp;&nbsp;&nbsp;http://mywebsite:80/jakarta/isapi_redirect.dll</td></tr> <tr><th>Physical Path</th><td>&nbsp;&nbsp;&nbsp;D:\ColdFusion11\config\wsconfig\1\isapi_redirect.dll</td></tr> <tr class="alt"><th>Logon Method</th><td>&nbsp;&nbsp;&nbsp;Not yet determined</td></tr> <tr><th>Logon User</th><td>&nbsp;&nbsp;&nbsp;Not yet determined</td></tr> </table> <div class="clear"></div> </div> </fieldset> </div> <div class="content-container"> <fieldset><h4>More Information:</h4> This error occurs when the WWW-Authenticate header sent to the Web server is not supported by the server configuration. Check the authentication method for the resource, and verify which authentication method the client used. The error occurs when the authentication methods are different. To determine which type of authentication the client is using, check the authentication settings for the client. <p><a href="http://go.microsoft.com/fwlink/?LinkID=62293&amp;amp;IIS70Error=401,2,0x80070005,9600">View more information &amp;raquo;</a></p> <p>Microsoft Knowledge Base Articles:</p> <ul><li>907273</li><li>253667</li></ul> </fieldset> </div> </div> </body> </html> {http://xml.apache.org/axis/}HttpErrorCode:401 

When rendered, this code would look like this:

HTTP 401.2 error from IIS

This reminded me of a problem I’d had before!

Sure enough, Anonymous Access was not enabled on the jakarta virtual directory. After enabling that and disabling Windows Authentication on that folder, everything worked properly!

Return URL with JavaScript that Prevents Loading of Cached Pages

HTML and JS logos

If you want to force a page to reload from the server rather than getting a cached copy, one way to do it is to append a query string to the URL.

If you want your web application to generate URLs that have this functionality, the code below can be implemented using JavaScript. Inside a file called BrowserUtility.js, insert this code.

function BrowserUtility()
{
	this.addNoCacheTimestamp =
		function(sUrl)
		{
			var ts = 'noCacheTS=' + (new Date()).getTime();
			sUrl = sUrl.trim();
			var nQIndex = sUrl.indexOf('?');
			sUrl+=(nQIndex==-1)?'?':(nQIndex===sUrl.length-1)?'':'&';
			return sUrl+=ts;
		};
};

var browserUtility = new BrowserUtility();

Once this is loaded, the function can be called like this, where the variable sUrl stores a URL:

sUrl = browserUtility.addNoCacheTimestamp(sUrl);

If the variable sUrl initially contained “http://example.com”, the returned string would be something like “http://example.com?noCacheTS=201808081203”.

Another option, if you did not want to use the current time as the query string value, would be to use a random number function instead.

(This is a snippet of code that I didn’t write – it was authored by my friend and colleague Jeff Konicky.)

Executing Stored Procedures in Loops Using PL/SQL Tables

Oracle Database PL/SQL

I needed to be able to run a stored procedure once for each item in a list. In Microsoft SQL Server, I would probably do this with a table variable and a cursor, and the code would look something like this:

DECLARE @wo_table TABLE (wo_id VARCHAR(10) NOT NULL);
DECLARE @wo_id VARCHAR(10);

INSERT INTO @wo_table (wo_id) VALUES
('123456')
,('234567')
,('345678')
,('456789')
,('567890');

DECLARE wo_cursor CURSOR FOR
SELECT wo_id FROM @wo_table;

OPEN wo_cursor;
FETCH NEXT FROM wo_cursor INTO @wo_id;
WHILE @@FETCH_STATUS=0
BEGIN
	EXEC dbo.MyStoredProc @wo_id;
	FETCH NEXT FROM wo_cursor INTO @wo_id;
END;

CLOSE wo_cursor;
DEALLOCATE wo_cursor;

However, PL/SQL does not have table variables. While an option like a global temporary table could work, I decided that a PL/SQL table was a better solution.

Declare the PL/SQL table type, instantiate the PL/SQL table with the list of values (strings, in this case), then loop over the PL/SQL table, running the stored proc with each value. Notice that the “EXECUTE” keyword is missing. It is not only unnecessary in this context; it will not work here.

Below is the completed code:

DECLARE
  TYPE wo_table_type IS TABLE OF VARCHAR2(10);
  wo_table wo_table_type := wo_table_type(
    '123456'
    ,'234567'
    ,'345678'
    ,'456789'
    ,'567890');
  
BEGIN 
  FOR i IN 1..wo_table.COUNT LOOP         
    PackageName.MyStoredProc(wo_table(i));
  END LOOP;  
END;