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:

<items>
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK References]]></title>
    <query>
        <sql>
            <![CDATA[select fk.owner,
fk.table_name,
fk.constraint_name,
fk.status
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;]]>
        </sql>
    </query>
    </item>
</items>

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!

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';

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):
  cd("/")
  if name == None:
    realm = cmo.getSecurityConfiguration().getDefaultRealm()
  else:
    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")
  else:
    authenticator = realm.lookupAuthenticationProvider(name)
  return authenticator

With a realm an an authenticator, we can change the password length:

#
#
def setMinPasswordLengthOnDftAuth(minPasswordLength):
  try:
    edit()
    startEdit()
    # Get Realm and Authenticator
    realm = getRealm()
    authenticator = getAuthenticator(realm)
    authenticator.setMinimumPasswordLength(int(minPasswordLength))
    passwordValidator=realm.lookupPasswordValidator('SystemPasswordValidator')
    passwordValidator.setMinPasswordLength(int(minPasswordLength))    
    save()
    activate(block='true')
    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:
    stopEdit('y')
    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.