Oracle: Password Policy

Well working with a new PeopleSoft Oracle Database, I went to create a new connect id user on the database with a relatively straight forward password and the database angry told me – NO. It said that the password policy required specific elements to be included with the password.  As most people know the system administrator password and connect id password associated with the Oracle Database need to be 8 characters long and should only contact alpha and numeric characters.  Seriously do not try to make it long or shorter and never put special characters in the password.  Over the years I have been mesmerized by the number of times this password has caused me grief.

So if there is a complex password policy set on the default profile you can turn it off by issuing the following command within sqlplus:

ALTER PROFILE "DEFAULT" LIMIT PASSWORD_VERIFY_FUNCTION NULL;

To determine what the profile is, you can issue the following statement:

select profile from DBA_USERS where username = '<username>';

Good To Know:

There are many elements in the profile that can cause issues one of them is the password expiry option.  Often not a good plan to have you main database account to stop functioning because of a password expiration, because Murphy’s Law says it will expire the day your DBA is on vacation and they will NOT pick the phone up that day.

Click here to go article:  Oracle Database User – Password Expiry

Windows Firewall

Over the years, protection has become increasingly more and more difficult to avoid.  It is everywhere, we have high end firewalls to let you, we have high end firewalls to let you out, we have firewalls within firewalls to protect other firewalls, and we also have firewalls on the servers themselves.  Sometimes you spend hours trying to figure out why something is not communicating to something else, and it turns out the good old Windows Firewall is causing your problem.  On the Unix Systems you may encounter firewall rules via the iptables security configuration, but that is another story.

A few weeks ago, while troubleshooting a connection issue, I found an article:  Top 10: Windows Firewall netsh commands and I was very pleased that I had. If you ever try to work through all the rules in Windows Firewall, you will be there awhile.

Tip #1: Turn off Windows Firewall – fastest way to determine if WFW is the problem

netsh advfirewall set allprofiles state on netsh advfirewall set allprofiles state off

Tip #2: Query Windows Firewall

netsh advfirewall firewall show rule name=all

Tip #3: Enable/Disable Ping

Disable: netsh advfirewall firewall add rule name=”All ICMP V4″ dir=in action=block protocol=icmpv4

Enable: netsh advfirewall firewall add rule name=”All ICMP V4″ dir=in action=allow protocol=icmpv4

Tip #4: Add/Remove a Specific Port Rule (i.e. For 1521 Oracle DB)

netsh advfirewall firewall add rule name=”Open Oracle DB Port 1521″ dir=in action=allow protocol=TCP localport=1521

netsh advfirewall firewall delete rule name=”Open Oracle DB Port 1521″ protocol=tcp localport=1521

Tip #5: Enable RDC Remote Desktop Connection

netsh advfirewall firewall set rule group=”remote desktop” new enable=Yes

Tip #6: Export/Import Firewall Settings (Same Rules – Multiple Machines)

netsh advfirewall export “C:\WFW-configuration.wfw”

netsh advfirewall import “C:\WFW-configuration.wfw”

PeopleSoft Object Owner: (OBJECTOWNERID)

Many years ago PeopleSoft introduced the object owner id which helps identify pretty much every PeopleSoft object (records, pages, content references, etc….) into where that object belongs.

This past week while working on a security matrix, the matrix needed to be broken down by module.  While with a little SQL magic and a little help from Excel and its filters the task was made significantly easier. In order to get a listing of the object owner ids within the system you can use look them up in the PSXLATITEM table.

SELECT * FROM PSXLATITEM WHERE FIELDNAME = ‘OBJECTOWNERID’ ORDER BY FIELDVALUE

If you want a specific application, Finance Objects have objectownerid starting with “F”, Human Resources “H”, Supply Chain “D”, Campus Solutions “S”, PeopleTools = “PPT”.  I also found some modules had multiple objectownerid values.

This handy bit of SQL courtesy of my friend Issam came in really handy, I honestly had never worked with the “WITH” function.  This code is specific to SQL Server 2014, but with a tweak or two it will work in Oracle 12 without an issue.  I am sure it will run in earlier versions, but as I have limited experience with the “WITH” function, it was only tested with MSSQL 2014 and Oracle 12c.

WITH PR (PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH) AS (
SELECT P.PORTAL_NAME, P.PORTAL_PRODUCT, P.PORTAL_SEQ_NUM, P.OBJECTOWNERID, P.PORTAL_OBJNAME, P.PORTAL_LABEL, P.PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, cast(P.PORTAL_LABEL as varchar(4000)) AS MYPATH FROM PSPRSMDEFN P
WHERE P.PORTAL_LABEL = ‘Root’
AND P.PORTAL_NAME = ‘EMPLOYEE’
UNION ALL
SELECT P_ONE.PORTAL_NAME, P_ONE.PORTAL_PRODUCT, P_ONE.PORTAL_SEQ_NUM, P_ONE.OBJECTOWNERID, P_ONE.PORTAL_OBJNAME, P_ONE.PORTAL_LABEL, P_ONE.PORTAL_REFTYPE, P_ONE.PORTAL_URI_SEG1, P_ONE.PORTAL_URI_SEG2, cast( (MYPATH + ‘ –> ‘ + P_ONE.PORTAL_LABEL) as varchar(4000)) AS MYPATH FROM PR P INNER JOIN PSPRSMDEFN P_ONE ON P.PORTAL_NAME = P_ONE.PORTAL_NAME
AND P.PORTAL_REFTYPE = ‘F’
AND P.PORTAL_OBJNAME = P_ONE.PORTAL_PRNTOBJNAME WHERE P_ONE.PORTAL_LABEL <> ‘Root’ AND P_ONE.PORTAL_NAME = ‘EMPLOYEE’ )

SELECT PORTAL_NAME, PORTAL_PRODUCT, PORTAL_SEQ_NUM, OBJECTOWNERID, PORTAL_OBJNAME, PORTAL_LABEL, PORTAL_REFTYPE, PORTAL_URI_SEG1, PORTAL_URI_SEG2, MYPATH FROM PR;

GO

Wildcard SSL + PeopleSoft

Out of the box you can do just about anything with WebLogic, however, when you get into some of the specifics and try to tie it into an application like PeopleSoft you can run into some interesting quirks.  I personally really like wildcard SSL certificates because it cuts down on a huge administrative headache that can come from trying to manage certificates for every “name”  that needs to be managed.  In PeopleSoft you can have dozens of servers and it can get to be an administrative nightmare.

WebLogic allows you to store and reference the wildcard certificate in you keystore, however, Wildcard Host Name Verification will fail with default settings.  If you do a quick google search you will turn up several posts that explain to simply “ignore” wildcard host name verification.  I am not a fan of just “ignoring” things.  So you could simply add the following to the start command of Weblogic:

-Dweblogic.security.SSL.ignoreHostnameVerification=true

However, I found what I consider a better option which is to set the validation to a custom wildcard host name verification with a delivered public class for WebLogic Security: SSLWLSHostnameVerifier, which can be called for hostname verification and will apply wildcard host name verification checks. This wildcard host name verification is instantiated by the WebLogic hostname verifier wrapper that checks for proxies, expectedName, etc. It obtains this hostname verifier class name from the SSLMBean or the hostname verifier property.

In the WebLogic Console:

you can change the advanced SSL properties:

Hostname Verification:

Custom HostName Verification

Custom Hostname Verifier:

weblogic.security.utils.SSLWLSWildcardHostnameVerifier

This will do a check of the peer certificate of the SSL session’s peer certificate SubjectDN CommonName attribute supports wildcarding, the CommonName attribute must meet the following:

• the CN must have at least two dot (‘.’) characters
• the CN must start with “*.”
• the CN can have only one “*” character

As of PeopleTools 8.50 PeopleSoft no longer requires the NSS cert7.db or Oracle Wallet Manager and all the crazy setup in order to get the LDAPS configuration to work. As of 8.50 PeopleSoft uses the Java Naming and Directory Interface (JNDI) API which allows Java software clients to discover and look up data and objects. JNDI being a JAVA API means that it is independent of the underlying implementation.

There is no need for additional installations and the certificates no longer need to be stored on the application server domain folders, all the certificates are stored in the database.

In order to setup LDAPS, you will need the Root CA certificate for the LDAPS Server, in my case I am using Active Directory so I went on my domain controller and went into the certificate store and exported the CA certificate for the server. Next you need to import that certificate into the digital certificates within PeopleSoft.

Home > PeopleTools > Security > Security Objects > Digital Certificates

Next, you need to add a Root CA, and give it an Alias Description – click refresh – you you will see hyperlink to import the certificate. Next you will want to copy the “pem” format of the CA certificate into the window. The easiest way to get this format is to export the file in Base x.509 format while viewing the certificate.

Bounce the application server and you should be able to Test Connections to the LDAPS server: