Samstag, 9. Mai 2020

Oracle EUS authentication with LSA activated on AD

Are there to many abbrevations in a posts title possible? If you think so, you might see this post a good example.

The setting I'm talking about is an Oracle Universal Directory (OUD) which works as a proxy between Oracle databases and Active Directory (AD) where the users are managed. Unfortunately it stopped working. Even when a user changed the password in AD, it could not log in with this password to the database, but always got
 ORA-01017: invalid username/password; logon denied  
This can have many reasons.
A check in Middleware/instances/euist_inst/OUD/logs/access(.log)  shows
[03/Mar/2020:13:09:00 +0100] MODIFY PROXY_REQ conn=1654 op=5 msgID=6 s_credmode=use-specific-identity dn="cn=Username,ou=...,dc=..." s_conn=1002 s_msgid=7983
[03/Mar/2020:13:09:00 +0100] MODIFY PROXY_RES conn=1654 op=5 msgID=6 result=53 Message="00000057: LdapErr: DSID-0C090F64, comment: Error in attribute conversion operation, data 0, v3839^@" etime=1 s_authdn=CN=Oracle 
[03/Mar/2020:13:09:02 +0100] DISCONNECT conn=1654 reason="Client Disconnect"
there is a MOs Note which describes the situation EUS Login Failure of AD Users Proxied by OUD: LdapErr: DSID-0C090CE0, comment: Error in attribute conversion operation (Doc ID 2612535.1) But neither the cause
They are not OUD-native. They have an AD format and indicate some problem with the passwords in AD.
nor the solution
Contact your AD administrator to determine the meaning of the AD portion of the error to fix this problem.
helps a lot.
Still asking the AD admin is a good idea. After some back & forth this line in eventlog on AD Server is an important step:
Code Integrity determined that a process (\...4\Windows\System32\lsass.exe) attempted to load \...\Windows\System32\oidpwdcn.dll that did not meet the Microsoft signing level requirements.

And it can be proved with signtool.exe - there is no certificate on the ddl:
"c:\Program Files (x86)\Windows Kits\10\bin\10.0.18362.0\x64\signtool.exe" verify oidpwdcn.dll 

File: oidpwdcn.dll 
Index  Algorithm  Timestamp


SignTool Error: No signature found.


Number of errors: 1

Of course Oracle Support has a solution for this situation in UD 11g - OIDPWDCN.DLL Plug-in Fails On AD 2012 R2 With Error "The password notification DLL oidpwdcn failed to load with error 577" (Doc ID 2616566.1) - but disabeling any security feature is not an acceptable solution in 2020. Sorry guys [NOT] !

The first attempt was to replace (quite old) oidpwdcn.dll with orapwdfltr.dll from modern opwdintg.exe
But a first check with signtool.exe didn't show any signature, and LSA also refused it. 

At that point, a SR at MOS was required. It went quite fast and Oracle confirmed, there is no signed version at that time. To get the latest orapwdfltr.dll, Bug 31134430 : NEED TO HAVE ORAPWDFLTR.DLL SIGNED BY MICROSOFT was opened and after reasonable time a signed ddl was provided. (I can not confirm nor decline, if additional contacts to Oracle were involved)

"C:\Program Files (x86)\Windows Kits\10\bin\10.0.18362.0\x64\signtool.exe" verify /v  orapwdfltr.dll
Verifying: orapwdfltr.dll
Signature Index: 0 (Primary Signature)
Hash of file (sha256): 2A14712107D424FF5577EF5C3D111CF66DB40F6226047ADC4F31389D69F437EB
Signing Certificate Chain:
    Issued to: VeriSign Class 3 Public Primary Certification Authority - G5
    Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
    Expires:   Thu Jul 17 01:59:59 2036
    SHA1 hash: 4EB6D578499B1CCF5F581EAD56BE3D9B6744A5E5
        Issued to: Symantec Class 3 Extended Validation Code Signing CA - G2
        Issued by: VeriSign Class 3 Public Primary Certification Authority - G5
        Expires:   Mon Mar 04 01:59:59 2024
        SHA1 hash: 5B8F88C80A73D35F76CD412A9E74E916594DFA67
            Issued to: Oracle America Inc.
            Issued by: Symantec Class 3 Extended Validation Code Signing CA - G2
            Expires:   Thu Jan 28 01:59:59 2021
            SHA1 hash: 1CB08E9B70B917E64407A4F2665799D58B171F89
The signature is timestamped: Thu Apr 23 03:33:05 2020
Timestamp Verified by:
    Issued to: DigiCert Assured ID Root CA
    Issued by: DigiCert Assured ID Root CA
    Expires:   Mon Nov 10 02:00:00 2031
    SHA1 hash: 0563B8630D62D75ABBC8AB1E4BDFB5A899B24D43
        Issued to: DigiCert SHA2 Assured ID Timestamping CA
        Issued by: DigiCert Assured ID Root CA
        Expires:   Tue Jan 07 14:00:00 2031
        SHA1 hash: 3BA63A6E4841355772DEBEF9CDCF4D5AF353A297
            Issued to: TIMESTAMP-SHA256-2019-10-15
            Issued by: DigiCert SHA2 Assured ID Timestamping CA
            Expires:   Thu Oct 17 02:00:00 2030
            SHA1 hash: 0325BD505EDA96302DC22F4FA01E4C28BE2834C5
SignTool Error: A certificate chain processed, but terminated in a root
        certificate which is not trusted by the trust provider.
Number of files successfully Verified: 0
Number of warnings: 0
Number of errors: 1
Also the AD accepted the dll.
Still a proper login was not possible as orclCommonAttribute was not populated after a password change. It's important to read the documentation to opwdintg.exe as this installation program not only applies the ddl (instpflt.bat), but also extends the schema (etadschm.bat). (beside other changes) 3 groups are added: ORA_VFR_11G, ORA_VFR_12C and ORA_VFR_MD5. Only if users belong to the proper group, it's matching password algorithm is used to populate orclCommonAttribute.
After the test user was added to the first group (and it's password was changed again) login on th etest-DB was possible again.   

If you want to use this new, signed ddl, at the time of this post, no regular source is available (afaik). I recommend to open a SR at MOS and ask for a signed version of orapwdfltr.dll. Maybe it helps to drop a comment about Bug 31134430 😉

A bit THANK YOU to Stefa Oehrli who answered uncountable number of questions.

Montag, 13. April 2020

Agent has been blocked manually. Unblock the Agent.

As it's easter weekend (at the time I write this blog) and it's a nice tradition here to hide some small items (often colored eggs or sweets) so other can find them, this story perfectly matches.
In Enterprise Manager 13c I had an agent with status

Agent has been blocked manually. Unblock the Agent.

Unfortunately In the Agents drop down menu, there is no (sub-) entry to unblock the agent. A well hidden entry!
After several attempts I was hinted where to look. It's in the "Setup" => "Manage Cloud Control" => "Agents" area:

There all agents are listed, and when selected the blocked one, it can be unblocked in the top action list.

I did not find a useful entry in the documentation (but that's probably my lack of search-foo). At least there is a Note EM 12c: How to Block or Unblock an Enterprise Manager 12c Cloud Control Agent if Agent Status is Shown as Blocked in EM Console or Emctl Status Agent Command Shows Heartbeat Status : Agent Is Blocked ? (Doc ID 1392601.1)

Mittwoch, 8. April 2020

Γνῶθι σεαυτόν

Sometimes it might be interesting to understand, what's the SQL statement which is currently executed.
In an Oracle instance, for any other session it's quite simple by accessing some v$ views. But from "within" the statement, it's not straight forward. Nevertheless it's possible, by a creative combination of some features.

Here is the example.

First let's create a proper user:


GRANT connect, resource    TO know;


GRANT    CREATE VIEW       TO know;

GRANT imp_full_database    TO know;

That's slightly more than really needed - so if you evern require this funcitonality, please be careful with required permissions!
In this example, we do not even need a table.
But a Package to keep some variables:

CREATE OR REPLACE PACKAGE other_color_injector AS
-- package OCI
    text_keeper   VARCHAR2(4000 CHAR) := '--';
    current_color VARCHAR2(100 CHAR)  := 'white';
    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2

END other_color_injector;


    FUNCTION rls_hook (
        p_schema  IN  VARCHAR2,
        p_object  IN  VARCHAR2
        PRAGMA autonomous_transaction;
        chk_color VARCHAR2(100 CHAR);
        text_keeper := sys_context('userenv', 'CURRENT_SQL');

        -- first word in /* comment 
        chk_color := regexp_substr(regexp_substr(text_keeper, '/\*.*\*'), '[[:alpha:]]+');
        dbms_output.put_line(' SQL Text:' || text_keeper || '---');
        IF chk_color IS NOT NULL THEN
            current_color := chk_color;
        END IF;
        RETURN NULL;

END other_color_injector;

And a function which gives us some feedback about the SQL in which it's called:

create or replace FUNCTION rain_bow 
-- RETURN t_tf_tab 
return rain_tab
    PIPE ROW ( other_color_injector.current_color );   


create or replace view cloud as select column_value as color from rain_bow();

The view is somehow important for the next step whihc puts all the pieces together:

    dbms_rls.add_policy(object_schema =>   'KNOW', 
                        object_name =>     'CLOUD', 
                        policy_name =>     'RAIN', 
                        function_schema => 'KNOW', 
                        policy_function => 'OTHER_COLOR_INJECTOR.RLS_HOOK',
                        statement_types => 'select', 
                        update_check => false);

With this objects in place, a beautiful, but maybe confusing result can be created:
Enter user-name: know/thyself@pdb1

Connected to:
Oracle Database 19c Enterprise Edition Release - Production

SQL> select * from cloud;


SQL> select /* red */  * from cloud;


SQL> select * from cloud;


SQL> select /* green */  * from cloud;


SQL> select * from cloud;



The important part is this line in OTHER_COLOR_INJECTOR.RLS_HOOK:
text_keeper := sys_context('userenv', 'CURRENT_SQL');

SYS_CONTEXT has access to CURRENT_SQL (and some other useful parameters) -  but only in fine grained audot / RLS events. So all the other objects are required to make this information available in my view rain_bow.

I also created a small twitter quiz where I asked for methods to identify the current SQL and create values based on the SQL.
Rene Jeruschkat suggested a solution based on latest v$sqlstats:

Mathias Rogel suggested to get the latest cursor from v$open_cursor which belongs to the current session:

Both are great solutions and show: there is quite often more than one way to solve a problem!

If you are curious about the greek letters in the title: Γνῶθι σεαυτόν they can be translated to know thyself - something which is often hard - not only for SQL statements.

Samstag, 7. März 2020

materialized WITH query

Sometimes I have to improve a SQL query where the same (or similar) subquery is used several times within the whole statement. This leads to many times the tables needs to be visited, even for the same rows.
It also makes the query hard to read.

A real life example I had to deal with this week is something like

FROM ( SELECT columns, aggregate functions
       FROM some tables
       ( SELECT IDa, 0 as IDb, col1
         FROM T1
         SELECT 0, IDb, col col1
         FROM T2 )
       ON some joins
       ( SELECT IDa, 0 as IDb, col2
         FROM T1
         SELECT 0, IDb, col col2
         FROM T2 )
       ON some joins
       ( SELECT IDa, 0 as IDb, col3
         FROM T1
         SELECT 0, IDb, col col3
         FROM T2 )
       ON some joins
       ( SELECT IDa, 0 as IDb, col4
         FROM T1
         SELECT 0, IDb, col col4
         FROM T2 )
       ON some joins
       WHERE some filters
       GROUP BY columns)
WHERE more filters

In this case it's quite visible there are 4 INNER JOINs to the same UNION of 2 tables, only the columns differ.

Fig 1 original PLAN - begin

The optimizer tried it's best and the beginning of the plan looked like Fig1. 3 more iterations with SORT - VIEW - SORT - UNION ALL follow. It's amazing the cost is so low, But even with higher cost there is not much the optimizer could do.

So my idea was to put it into a WITH clause and replace the INNER JOIN select with it.

The WITH clause is

WITH my_inner as (
SELECT IDa, 0 as IDb, col1, col2, col3, col4
SELECT   0, IDb,      col1, col2, col3, col4

And when replacing the first INNER JOIN
       ( SELECT * from my_inner )

Fig 2 changed PLAN - 1 replacement

It seems the optimizer did not like this as the cost increased.

But I continued with the next replacement:
Fig 3 changed PLAN - 2 replacements

The optimizer "understands" it can created a temporary object (the one which begins with SYS_TEMP_) and then re-uses it later.

With all 4 replacements, the Plan is different now:

Fig 4 changed PLAN - 4 replacements

The cost is still higher than in the original plan, but it's very likely the statement is faster than the original one.
In this case, there was no need to add the MATERIALIZED hint - it was done automatically, for very good reasons.

I like this optimization as it both, improves the readability of the query AND it's performance!

Montag, 3. Februar 2020

Gorillas, be aware!

 During the last 2 weeks I attended the Guerrilla Capacity and Performance online class.
It was somehow different from my normal technical activities as the content was totally agnostic of any specific technology and solely focussed on proper thinking and using of methodologies.
We learned sufficient about queueing theory, Littles Law, Amdahl and USL, PDQ and Statistical Forecasting like Multivariate Regression.
 Beside these technical skills the most important aspect was the tactical one: As modern timeframes and project plans does not allow a dedicated, full blown capacity planning step, the required approach is more guerilla like: Use oportunities, don't waste (your) resources, make achievements visible communicate properly.
 I have to thank Dr. Neil J. Gunther for giving this class. Beside the raw facts and figures he shared a great set of wisdom and anectotes.
 It's now on me to practice - the only way for becoming a practicioner. - Let's hope for many interesting projects ahead where I can apply my new skills!

Freitag, 17. Januar 2020

fixing a failed APEX installation

Today I tried to install APEX into a 19.5 database, but apexins.sql failed with

Errors found. Drop APEX_190200 before re-starting the installation.
# Actions in Phase 1:
    ok 1 - BEGIN                                                        |   0.00
    ok 2 - Creating FLOWS_FILES                                         |   0.00
    ok 3 - Creating APEX_PUBLIC_USER                                    |   0.02
    ok 4 - Creating APEX_GRANTS_FOR_NEW_USERS_ROLE                      |   0.00
    ok 5 - Creating SYS Objects                                         |   0.00
    ok 6 - Creating APEX Tables                                         |   0.13
    ok 7 - Installing Package Specs (Runtime)                           |   0.48
    not ok 8 - Installing Package Bodies (Runtime)                      |   0.20
    # Message: ORA-00933: SQL command not properly ended
    # Statement: comment on column apex_team_feedback.logged_as_issue_id      is
 'If feedback resulted in an issue, the unique identifier of the resulting issue
    # comment on column apex_team_feedback.LABEL_01 is 'Identifies the label for
 corresponding attribute.'
    ok 9 - Dev Grants                                                   |   0.00
    ok 10 - Creating Dev-Only Tables and Triggers                       |   0.00
    ok 11 - Installing Package Specs (Dev)                              |   0.20
    ok 12 - Installing Views (Dev)                                      |   0.02
    ok 13 - Installing Package Bodies (Dev)                             |   0.07
    ok 14 - Recompiling APEX_190200                                     |   0.33
    ok 15 - Creating APEX$ objects in APEX_190200                       |   0.02
    ok 16 - Creating Spatial Support                                    |   0.00
    ok 17 - Creating Instance Parameter Defaults                        |   0.00
    ok 18 - Installing Page Designer Metadata                           |   0.03
    ok 19 - Inherit Privileges                                          |   0.00
    ok 20 - Creating APEX_INSTANCE_ADMIN_USER                           |   0.00
not ok 1 - 19 actions passed, 1 actions failed                          |   1.50
ERROR at line 1:
ORA-20001: Install errors found in phase 1, see "not ok" messages above for
ORA-06512: at "APEX_190200.WWV_INSTALL_API", line 440
ORA-06512: at line 5

Some lines above in the logfile I found
comment on column apex_team_feedback.LABEL_01 is 'Identifies the label for corresponding attribute.'
ERROR at line 3:
ORA-00933: SQL command not properly ended

This lead me to the file core/apex_views.sql

There a simple ; was missing:
< comment on column apex_team_feedback.logged_as_issue_id      is 'If feedback resulted in an issue, the unique identifier of the resulting issue.';
> comment on column apex_team_feedback.logged_as_issue_id      is 'If feedback resulted in an issue, the unique identifier of the resulting issue.'

I have no idea why this file is missing that character.
The zip file seems not to be corrupted:

Maybe this helps anyone, Or I managed to find a problem in this env which causes unzip to drop a single ; - who knows?