Patrick's Oracle DBA Blog

Post navigation

In a previous post I showed that by default when authentication_ldap_simple communicates with a Windows Domain Controller (or any other LDAP service), then the password is transmitted unencrypted during authentication.

This time I’ll demonstrate how to close this loophole. A pre-requisite is that the Domain Controller needs to be configured to accept secure connections. This is done by installing a certificate, the process is well documented elsewhere so I won’t repeat it here.

For simple LDAP authentication, whether connections by the plugin to the LDAP server are secure. If this variable is enabled, the plugin uses TLS to connect securely to the LDAP server.

In both cases we have to set authentication_ldap_simple_ca_path to point to the certificate authority file used when securing the domain controller. (Pro-tip ensure the both the file attributes of this certificate and of the directory it sits in are such that the mysql process is able to access it, you won’t believe how long I wasted due to this).

Of the two methods, I have been informed that the TLS method is optimal so that is what I will demonstrate. Note I have found that it’s better to load the plugin and set the variables in the mysql configuration file (my.cnf) and restart the service rather than setting them dynamically (it seems the otherwise the values do not correctly propagate to the appropriate processes due to LDAP connection pooling) so that’s what I’ll show you.

I mentioned in my previous blog entry that had I encountered an issue with this plugin, and I think now that it has been fixed in the latest MySQL versions (released on 2019-04-25) it’s reasonable to share my findings.

The following tests are with MySQL Version 8.0.13. I start by installing the plugin, pointing it at my Windows Domain Controller and creating a user associated with the DN of my Windows account.

Let’s test in 8.0.16. Note one difference here is that I have to set variable authentication_ldap_simple_group_search_attr to ” to disable AD group checking. This behaviour, related to proxy users, seems to have been introduced in 8.0.14, but on my lab setup this step breaks authentication completely so I disable it.

I’m not sure if there is anyone using authentication_ldap_simple due to it sending passwords unencrypted between database and domain controller, but if there is, I’d suggest checking whether you are susceptible to this issue and if so applying latest patchset ASAP.

I’m also not sure if this is specific to LDAP authentication with Active Directory or other Directory Services are affected. I also wonder whether authentication_ldap_sasl is affected, but I don’t have configuration to check that out.

However if we re-run the original connection attempt (with encrypted traffic between database client and server) capturing the network traffic between database and domain controller, we can see password is transferred unencyrpted as this point

The documentation alludes to some of the restrictions, although to my mind it seems to focus on the encryption between database client and server, but not between database server and Domain Controller (unless I’ve missed something).

The server-side authentication_ldap_simple plugin performs simple LDAP authentication. For connections by accounts that use this plugin, client programs use the client-side mysql_clear_password plugin, which sends the password to the server in clear text. No password hashing or encryption is used, so a secure connection between the MySQL client and server is recommended to prevent password exposure.

Based on the above observations, as I was expecting, this plugin combination is not really suitable for implementation for any environment which takes security seriously.

If that wasn’t enough reason to avoid the plugin, during the course of my investigation I discovered some other surprising behavior. Based on discussion with Oracle support this should be resolved in the next release of SQL Server, so I’ll wait till that is released before sharing.

Recently some of our developers have moved forwards with fixing some code that has been using literals, instead of bind variables. A complication is that they are using lists of values, so the SQL they were generating was of format

WHERE COL1 IN (1,2,3,4)

They resolved this by using an array in Java and construct similar to the following:

WHERE COL1 IN (SELECT * FROM TABLE(?))

However when moving to the new method performance degraded, they were getting full table scans where previously they were getting index access.
Initially I thought we could resolve this by using a cardinality hint on the TABLE select ie:

WHERE COL1 IN (SELECT /*+CARDINALITY(t 1) */ * FROM TABLE(?) t)

However this didn’t help much. I’ve managed to reproduce the problem to the testcase below (running on 18c):

Fundmentally I think the problem is the optimzer is unable to push the TABLE function into the inner select.
For the moment we’re having to revert to a hybrid solution where they generate SQL such as the following:

WHERE COL1 IN (?,?,?,?)

Each value has to be bound seperately. It’s not ideal as shared pool is somewhat full of each variation depending on how many values there are. Also they are having to do a soft parse of the cursor each time, rather than parsing once and re-using.

However other than getting the developers to rewrite all their queries I don’t see any better solution at the moment.

Recently we hit an issue with a complex SQL statement (formatted was 44,000 lines, maybe subject of a separate blog post), causing the CBO to struggle consuming large amounts of PGA memory, and the host to start swapping memory and impacting other database users.

The pga_aggregate_limit parameter did not appear to be kicking in (maybe because this was happening during parse phase), so while looking for a proper solution we considered other ways to limit the effect of this problem SQL.

As we are on release 12.2 one thing we tried was a (relatively new) feature of resource manager, session_pga_limit. This should limit the PGA any one session can consume (as opposed to pga_aggregate_limit which is instance wide), however new features can be a little temperamental, especially in the first few versions after they have been introduced.

After a bit of trial and error we determined that setting it to any value greater than 4G (4096 MB) causes the feature not to kick in.

The following is my testcase on a 12.2.0.1.180717 PDB. I could not reproduce this behavior on 18c (non-multitenant) implying this limitation (bug?) has likely been fixed.

First we create a resource manager plan, consumer groups and directives, and configure the instance to use this plan.

Invalid SQL being sent to the database is something to watch out for,
it’s a real performance killer because once SQL is rejected by parser,
it is not cached in shared pool, resulting in hard parse every time the SQL is encountered.

One cool new feature of Oracle 12.2 is the fact that such SQL parse errors are automatically logged to the alert log (by default every 100 occurrences of a particular SQL)

(Random thoughts, the database must be storing these invalid SQL somewhere to keep track of the parse error count, I wonder where that is? I guess though that even though it has stored cached the statement in it’s “Invalid SQL” list it will still have to re-parse every time it’s encountered as the statement may become valid if say a table it relied on got created). (Edit: 2018-10-10 As is often the case, Jonathan Lewis seems to have the answer to this)

A similar effect can be achieved on previous versions by setting event 10035.

One instance that I’ve been monitoring has regular occurrences of the following SQL:

A quick google returns lots of hits, but no real solution. Oracle Support bug 8469553 also has some clues. Basically it seems to be a problem with older versions of ODBC, and likely solution is to upgrade ODBC version.

However I was thinking about a short-term fix, and recalled a presentation from Kerry Osborne regarding SQL Translation Framework to transform one SQL statement to another, and wondered whether I could transform this invalid statement to a valid one?

Observe from the alert log that because the entries in the mview log have been deleted as part of the fast refresh, Oracle determines that it is appropriate to enable row movement on the mview log table and perform a “shrink space” operation.

The behavior of the LAST_LOGIN field on DBA_USERS has changed with respect to proxy authentication (for the better I think).

Proxy authentication is an feature of the Oracle Database that effective allows you to be connected as one user (the client user to use Oracle’s terminology), but using the credentials of another user (the proxy user). This is useful in combination with using personal accounts (one for every user) as the proxy users, using application accounts as the client users, avoiding the need for users to share application account passwords.

The test-case below demonstrates that when using proxy authentication in 12.1, the last login for the client user (only) is updated.

Previously if an account was only being used as a proxy user, there was no way of knowing it was actually being used (without implementing a login trigger and storing the login time in a separate table). With this change we can know for such a user account if and when it is being used.

After implementing a recent change, and I pulled of a quick AWR report to compare performance. At first glance I was impressed by significant improvement in “log file parallel write” event, which had halved from 2 millisecond to 1 millisecond, but something about the numbers didn’t quite look right.