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?

Freitag, 13. Dezember 2019

maximum size of ORALCE_SID

The maximum length of ORACLE_SID environment variable seems to be a mystery in many places.
Recently I reviews a document where the max length was defined as 8 characters. I was quite sure this was not right, but I didn't know the correct value. A short ressearch did not find anythign useful in the docs - the Best available there was RAC installation guide for Linux & UNIX:
The Oracle Service Identifier (SID) prefix is the first 8 characters of the database name. The SID prefix can contain only the characters a-z, A-Z, and 0-9. The SID prefix cannot contain operating system special characters, so if you use special characters in the first 8 characters of the database name, then these special characters are omitted in the SID prefix. There is a single SID prefix for every database. The SID prefix for a database must be unique within the cluster.
For an Oracle RAC database, each instance has a unique identifier, ORACLE_SID, which consists of the SID prefix and an instance number. The ORACLE_SID prefix can contain up to 12 characters. 
It always refers to a ORACLE_SID prefix - but never explains the total length. As it's the RAC documentation, I assume it's more about RAC capabilities (some internals in srvctl and supporting binaries / structures).

Some other trustworthy sources at Oracle are very vague in this area:

Tom Kyte (2002):
its really 4 characters as the convention (ORCL for example).
The reason is in support of 8.3 filenames. ALRTORCL.LOG, INITORCL.ORA, etc. We use 4, you use 4 and we can get 8 character filenames.

and later there (2007):
I would stick with 8 or less - better safe than sorry.

Connor McDonald (2017):
So even if you find a platform that does more than 8, I would never go more than that.

Another approach can be to identify Data Dictionary views which report the Oracle System ID.
One is v$instance.INSTANCE_NAME with Datatype VARCHAR2(16).
Another is v$thread.INSTANCE with Datatype VARCHAR2(80).
Oracle is really consequent in it's ambiguity!

So it's worth to do some tests!

My lab is 18c & 19c on Linux. Maybe other OS might show other limits!

The longest ORACLE_SID I could set and use to start an instance was
30 characters 
(In my case I used QWERTZUIOP1234567890ASDFGHJKLY)

With this ORACLE_SID set, a proper value is returned by v$thread

SQL> select instance from v$thread;


but v$instance does not show anything:

select  instance_name  from v$instance;



With an ORACLE_SID of 16 characters (QWERTZUIOP123456) v$instance is fine:
select  instance_name  from v$instance;



With any ORACLE_SID of 31 characters or more I got
ORA-00600: internal error code, arguments: [OSDEP_INTERNAL], [], [], [], [], [], [], [], [], [], [], []
ORA-27302: failure occurred at: slsid1
ORA-27303: additional information: Unable to get environment variable ORACLE_SID

Now I at least tested a possible max. length of ORACE_SID (18c & 19c on Linux) of 30 characters.
Whenever I use the System ID in scripts, I should query v$thread, NOT v$instance.
I recommend to use not more than 16 characters so it's still visible in v$instance also.