Thursday, 22 March 2018

SQLDeveloper: User Defined Extensions and ForeignKey query revised

It was so fun: yesterday I wrote  a small article on creating a query on Foreign Keys refering a certain table. A post with content that I made up dozens of times in my Oracle carreer. And right away I got 2 good comments. One was on the blog itself.

And of course Anonymous is absolutely right. So I added 'U' as a constraint type option.

The other comment was from my much appreciated colleague Erik. He brought this to another level, by pointing me out how to add this as a User Defined Extension in SQL Developer.

I must say I was already quite pleased with the Snippets in SQLDeveloper. So I already added the query as a snippet:
But the tip of Erik is much cooler.
He refered to a tip by Sue Harper that explains this (What, it's been in there since 2007?!).

Now what to do? First create an xml file, for instance referred_by_fks.xml,  with the following content:
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK References]]></title>
            <![CDATA[select fk.owner,
from all_constraints  fk
join all_constraints rpk on rpk.constraint_name = fk.r_constraint_name 
where fk.constraint_type='R'
and rpk.constraint_type in('P','U')
and rpk.table_name = :OBJECT_NAME
and rpk.owner = :OBJECT_OWNER
order by fk.table_name, fk.constraint_name;]]>

Note that I updated my query a bit.

Then to add the extension to SQL Developer:
  • Open the prefereces via: Tools > Preferences
  • Navigate to Database > User Defined Extensions
  • Click "Add Row" button
  • In Type choose "EDITOR", Location is where you saved the xml file above
  • Click "Ok" then restart SQL Developer

Now, if you click on a table in the navigater, you will have an extra tab on your table editor:

Cool stuff! And it's been there for ages!

Wednesday, 21 March 2018

Which tables have foreign keys refering to a particular table?

Ok, this time a quick not so exciting post. Actually, I find my self recreating a query again, that I created many times in my carreer. So, why not post it?

Last year, I published my Darwin Object Type Accelerator (Dotacc). It allows you to generate objects from a datamodel. What it also does is create collection types for tables that refer to the tabel you want to generate an object for. For some you want that, but for others you don't. Simply because you don't need them to be queried along. Therefor, I added functionality to disable those.

But then comes the question: which are the tables with their foreignkey constraints that refer to this particular table?

The answer is in the ALL_CONSTRAINTS view (with the variants of DBA_% and USER_%).
There are several types of constraints:
  • C: Check constraints
  • R: Referential -> the particular foreign keys
  • P: Primary Key
  • U: Unique Key
I'm interested in the Foreign keys, thus those where constraint_type='R'. But those refer not to a table but to another constraint. So, I need to get the primary key, constraint_type='P', of the table that I want to query and join those together.

That get's me:
select fk.* 
from all_constraints  fk
join all_constraints rpk on rpk.constraint_name = fk.r_constraint_name 
where fk.constraint_type='R'
and rpk.constraint_type in ('P', 'U')
and rpk.table_name = 'DWN_MY_TABLE';

Thursday, 8 March 2018

Set the minimum password length on your default authenticator in Weblogic

End of last year I wrote how to create a demo community of users in your Weblogic using wlst.
Using these scripts I wanted to do the same at my current customer: creating test users in the DefaultAuthenticator. However, I faced that the minimum password length was 8, while one of the user failed creation, because the password was the same as the user, and only 5 characters long.

So I need to change the password validator. And preferably using WLST (of course). Now, the password validator of de authenticator can also be found through the console. However, the Weblogic realm also has a system password validator. Both have a default length of 8.

Let me show you some snippets (that you can add to the create users script, or your own purpose), on how to change the minimum password length.

First a method to get the default realm:
def getRealm(name=None):
  if name == None:
    realm = cmo.getSecurityConfiguration().getDefaultRealm()
    realm = cmo.getSecurityConfiguration().lookupRealm(name)
  return realm

With that you can get the authenticator:
def getAuthenticator(realm, name=None):
  if name == None:
    authenticator = realm.lookupAuthenticationProvider("DefaultAuthenticator")
    authenticator = realm.lookupAuthenticationProvider(name)
  return authenticator

With a realm an an authenticator, we can change the password length:
def setMinPasswordLengthOnDftAuth(minPasswordLength):
    # Get Realm and Authenticator
    realm = getRealm()
    authenticator = getAuthenticator(realm)
    print('Succesfully set minimum password length to '+minPasswordLength+ ' on '+authenticator.getRealm().getName()+'.')
    print('For '+ authenticator.getName() +': '+str(authenticator.getMinimumPasswordLength()))
    print('For SystemPasswordValidator of '+getRealm().getName()+': '+ str(passwordValidator.getMinPasswordLength()))
  except WLSTException:
    message="Failed to update minimum password length!"
    print (message)
    raise Exception(message)

The minimum password length from the authenticator can be set directly. From the realm this function looks up the SystemPasswordValidator. And on that it set the minimum password length.

This function goes to edit mode, saves and activates the changes. But if you want to add users, you need to get wlst into domainConfig() mode.

Other password validator property setters are:
  • setMinPasswordLength()
  • setMaxPasswordLength()
  • setMaxConsecutiveCharacters()
  • setMaxInstancesOfAnyCharacter()
  • setMinAlphabeticCharacters()
  • setMinNumericCharacters()
  • setMinLowercaseCharacters()
  • setMinUppercaseCharacters()
  • setMinNonAlphanumericCharacters()
  • setMinNumericOrSpecialCharacters()
  • setRejectEqualOrContainUsername(true)
  • setRejectEqualOrContainReverseUsername(true) 
See the docs for more.

Friday, 9 February 2018

Weblogic 12c + SAML2: publish your metadata over an URL

This week I got to do a SAML2 implementation again for APEX against ADFS. Actually the same setup as last year. One pitfall I fell into with open eyes, was the Redirect URI on the 'Web SSO Partner Provider'. I entered /ords/f*, but it had to be with out the wild-card: /ords/f. But that aside.

At one step in the setup of a SAML2 configuration is that you have to publish the metadata, by clicking a button. Some SAML2 capabable middleware solutions can publish the metadata over an URL. ADFS does support a URL to get the metadata from the Service Provider, being Weblogic12c servicing your application. This prevents that you need to hand over the xml file every time you change/update your configuration. For instance because of expired certificates. How nice would it be if Weblogic supported this?

Well, actually, you can! Sort of... Weblogic does support to service a document-folder, like the htdocs folder of Apache. To do so, you need to create a war file, with only a weblogic.xml file that couples a context-root to a certain folder. And apparently Glassfish can do so too!

When you install ORDS on Weblogic, following the steps, you generate an i.war that is actually the example for this post. You could extract that file and adapt it for this purpose. But I wanted to be able to generate it. Doing so I could reuse this for several other purposes if I would need to.

So I started with a new Saml2MetaData project folder and created a src folder, with a WEB-INF folder beneath it.
Then I copied the three deployment descriptors:
  • sun-web.xml
  • web.xml
  • weblogic.xml
 The sun-web.xml (not being the travel company):
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sun-web-app PUBLIC "-//Sun Microsystems, Inc.//DTD GlassFish Application Server 3.0 Servlet 3.0//EN" "">
 <!-- This element specifies the context path the static resources are served from --> 
 <!-- This element specifies the location on disk where the static resources are located -->
 <property name="alternatedocroot_1" value="from=/* dir=${samlMetaData.home}"/>

As you can see I placed the ${samlMetaData.contextRoot} the property in the context-root-tag and the property named alternatedocroot_1 got the directory reference containing the ${samlMetaData.home}.

The web.xml is there for completeness, but does not contain a directory reference:
<?xml version="1.0" encoding="UTF-8"?>
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 <!-- This Web-App leverages the alternate doc-root functionality in WebLogic and GlassFish to serve static content 
      For WebLogic refer to the weblogic.xml file in this folder
      For GlassFish refer to the sun-web.xml file in this folder

And then the weblogic.xml: including the same properties referencing the context-root and folder:
<weblogic-web-app xmlns="">
 <!-- This element specifies the context path the static resources are served from -->
  <!-- This element specifies the location on disk where the static resources are located -->

Then I need an ANT build file that copies these files replacing the properties. I would have done it with WLST if I had found a way to wrap the lot into a war file, that quickly. But ANT does the job well. First I need a file, that denotes the properties values:

And then the ANT build.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<project name="SamlMetaData" basedir="." default="build">
   <property file="" />
   <!-- Clean & Init -->
   <target name="clean">
      <echo>Delete build and dist folder</echo>
      <delete dir="${build.dir}" />
      <delete dir="${dist.dir}" />
   <target name="init" depends="clean">
      <echo>Create build and dist folder</echo>
      <mkdir dir="${build.dir}" />
      <mkdir dir="${dist.dir}" />
   <!-- war the project -->
   <target name="war">
      <property name="war.dir" value="${dist.dir}/${}" />
      <property name="war.file" value="${war.dir}/${}.war" />
      <echo>Create war file ${war.file} from ${build.dir}</echo>
      <mkdir dir="${war.dir}" />
      <jar destfile="${war.file}" basedir="${build.dir}">
         <manifest />
   <!-- Build the war file -->
   <target name="build" depends="init">
      <echo>Copy ${src.dir} to  ${build.dir}, expanding properties</echo>
      <copy todir="${build.dir}">
         <fileset dir="${src.dir}" />
            <expandproperties />
      <ant target="war" />

Run this with ANT andit will create a build and a dist  folder with the war file.
This can be deployed to Weblogic that results in a context root as configured in the Everything placed in the folder as configured in the samlMetaData.home folder can be fetched through Weblogic.

So just publish your metadata to that folder and the IdentityProvider can get it auto-magically.
And of course you can use this for any other static file provisioning through Weblogic.

How to install the Notepad++ 64-bit plugin manager

I'm a Notepad++ fan for years. And as soon as a 64-bit version arose I adopted it.
But since a few months I have a new laptop, and I apparently didn't get the plugin manager with the latest new install.  And only now I took the opportunity to sort it out and write about it.

I found that the plugin manager is available since April 2017 on GitHub, version 1.49. I downloaded the zip from the mentioned location, I choose the _x64 version:
Then unzipped it into my Notepad++ folder:

Then started Notepad++ and the plugin manager appears:

So now I can format my XML files again...

Tuesday, 23 January 2018

SoapUI: validate a date field in response with current date

Once in a while you need to validate a service that has dates in the response. Although SoapUI has xpath and xquery match assertions, validate against strings is quite difficult. How to do a date comparison against for instance the current date?

You can do it with a script assertion:
And the content of this can be:
def groovyUtils = new
// Set Namespaces
def holder = groovyUtils.getXmlHolder(messageExchange.responseContent)
//holder.namespaces["soapenv"] = ""
def dateFoundStr = holder.getNodeValue("/Results/ResultSet/Row[1]/DATE_FOUND")
def dateFound = new Date().parse("yyyy-MM-dd hh:mm:ss", dateFoundStr)
dateFoundStr = dateFound.format("yyyy-MM-dd")
//Current Date
def date = new Date()
def currentDate=date.format("yyyy-MM-dd")
assert dateFoundStr == currentDate

First we need to fetch and parse the response content using the holder variable, parsing the messageExchange.responseContent using groovyUtils.getXmlHolder.

Second, the particular date is found, here dateFound as a field from a JDBC response. A JDBC response does not have namespaces, but from a SOAP response it helps to declare namespaces. For an example see the commented line for holder.namespaces["soapenv"].

Third, I parse the found date, which is a string as fetched from the xml, to a date time, then format it to a string to get only the date part. This could be done simply using substring methods, but I wanted to try this. And get and formatthe currentDate as a string.

In the end just do assert with a comparison of both values.

There you go.

Monday, 22 January 2018

Modify your in wlst

In 2016 I did several posts on automatic installs of Fusion MiddleWare, including domain creation using wlst.

With weblogic 12c you automatically get a pre-configured per-domain nodemanager. But you might find the configuration not completely suiting your whishes.

It would be nice to update the file to with your properties in the same script.

Today I started with upgrading our Weblogic Tuning and Troubleshooting training to 12c, and one of the steps is to adapt the domain creation script. In the old script, the AdminServer is started right way, to add the managed server to the domain. In my before mentioned script, I do that offline. But since I like to be able to update the file I figured that out.

Earlier, I created  a function to just write a new property file:
# Create a NodeManager properties file.
def createNodeManagerPropertiesFile(javaHome, nodeMgrHome, nodeMgrType, nodeMgrListenAddress, nodeMgrListenPort):
  print ('Create Nodemanager Properties File for home: '+nodeMgrHome)
  print (lineSeperator)
  fileNew=open(nmProps, 'w')
  fileNew.write('#Node manager properties\n')
  fileNew.write('#%s\n' % str(
  fileNew.write('DomainsFile=%s/%s\n' % (nodeMgrHome,''))
  fileNew.write('NodeManagerHome=%s\n' % nodeMgrHome)
  fileNew.write('JavaHome=%s\n' % javaHome)
  fileNew.write('ListenAddress=%s\n' % nodeMgrListenAddress)
  fileNew.write('ListenPort=%s\n' % nodeMgrListenPort)
  if nodeMgrType == 'ssl':
  fileNew.write('LogFile=%s/%s\n' % (nodeMgrHome,'nodemanager.log'))

But this one just rewrites the file, and so I need to determine the values for properties like DomainsFile, JavaHome, etc., which are already set correctly in the original file. I only want to update the ListenAddress, and ListenPort, and possibly the SecureListener property based on the nodemanager type. Besides that, I want to backup the original file as well.

So, I adapted this  function to:
# Update the Nodemanager Properties
def updateNMProps(nmPropertyFile, nodeMgrListenAddress, nodeMgrListenPort, nodeMgrType):
  nmProps = ''
  print ('Read Nodemanager properties file%s: ' % nmPropertyFile)
  f = open(nmPropertyFile)
  for line in f.readlines():
    if line.strip().startswith('ListenPort'):
      line = 'ListenPort=%s\n' % nodeMgrListenPort
    elif line.strip().startswith('ListenAddress'):
      line = 'ListenAddress=%s\n' % nodeMgrListenAddress
    elif line.strip().startswith('SecureListener'):
       if nodeMgrType == 'ssl':
         line = 'SecureListener=true\n'
         line = 'SecureListener=false\n'
    # making sure these properties are set to true:
    elif line.strip().startswith('QuitEnabled'):
      line = 'QuitEnabled=%s\n' % 'true'
    elif line.strip().startswith('CrashRecoveryEnabled'):
      line = 'CrashRecoveryEnabled=%s\n' % 'true'
    elif line.strip().startswith('weblogic.StartScriptEnabled'):
      line = 'weblogic.StartScriptEnabled=%s\n' % 'true'
    elif line.strip().startswith('weblogic.StopScriptEnabled'):
      line = 'weblogic.StopScriptEnabled=%s\n' % 'true'         
    nmProps = nmProps + line
  # Backup file
  print nmProps
  print ('Rename File %s to %s ' % (nmPropertyFile, nmPropertyFileOrg))
  os.rename(nmPropertyFile, nmPropertyFileOrg)  
  # Save New File
  print ('\nNow save the changed property file to %s' % nmPropertyFile)
  fileNew=open(nmPropertyFile, 'w')
It first reads the property file, denoted with nmPropertyFile line by line.
If a line starts with a particular property that I want to set specifically, then the line is replaced. Each line is then added to the nmProps  variable. For completeness and validation I print the resulting variable.
Then I rename the original file to nmPropertyFile+'.org' using os.rename(). And lastly, I write the contents of the nmProps to the original file in one go.

This brings me again one step further to a completely scripted domain.