Oracle column in table mysteriously changing value

Question

I have a server_password column in my table server_services.

I'm using a package to encrypt/decrypt the password.

This sometimes fails with an internal Oracle error message and is what I am investigating. I believe the error message is oracle's way of telling me the password or encrypted value is wrong.

The password is stored in a type VARCHAR2(100 BYTE) column; the encode/decode functions work with utl_raw.cast_to_raw and utl_raw.cast_to_varchar2 and take and give a varchar2. When looking at the password, it is looking strange, with many boxes indicating non-printable characters.

Now my problem is that the decryption works, but then starts failing. To investigate this, I calculated a hash of the encrypted password:

select rawtohex(
DBMS_CRYPTO.Hash (
    UTL_I18N.STRING_TO_RAW (SERVER_PASSWORD, 'AL32UTF8'),
    2)
)
FROM SERVER_SERVICES;

What is strange is that suddenly the hash changes, and there is no code that is supposed to do that. And I'm also not touching the table data manually.

-- doesn't work:
-- D40D0635FAC75F47DE5164F0EF50A2DB
-- regenerated password
-- works
-- 41136DFB85EFEDAC08150A8959923422
-- waited some time
-- doesn't work again
-- D40D0635FAC75F47DE5164F0EF50A2DB

Do you have any idea what is causing this and how to prevent it?

The oracle error message and code:

ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 44
ORA-06512: at "XXX_CONFIGURATION.ENCRYPT_DECRYPT_PASSWORD", line 32
ORA-06512: at "XXX_CONFIGURATION.ENCRYPT_DECRYPT_PASSWORD", line 65
ORA-06512: at line 1

I tried to look at the package, but it is "wrapped":

"PACKAGE BODY dbms_crypto_ffi wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
228a 312
2L+V7+Eg9BLQ+XmmDXawGjt9NCkwg811r9yGfC9ArU6O79zKUmj9ScZSugr0ScKkyrBaaS8K
RM2eU4oiAJ+r4d4lezkNE6wQ5Yqan2461E6wiGvHZQEvOHHdBvzuJjQ/5PjBOr9CEvi243t6
Nusbvg7sjKYbTVZlssZ/s90CpB2h0dzVXMQpZhJcAfTFvlB+l117gW6UHTxdwaXcMgkKFOu+
jmGw/I+etGPJCcg2NqGIdnOS/tLPTjDD8Mmi7CKC2I91UUJNDFVV10G4Y6hD7Sg8Wr7jrWyr
bE19xjlMFdvDcopkNK4XDwXyXv0yShDNbaQwiWB79aHGzH6gOUhVWqS6eHvGPCrT/4GrTPbt
bFS90BT3RMN3Cybzt62xAVVModFTWF5e9WFZnFlmAVLRMZjPzFy8cOI1ypXYqRavEaSGDhQQ
As+M/5avOclUIBg7+k0ST18JEIgBSpxwlEG5dPir5MlGdwiukLMO3PDLfUdLorLyDnU5Zhjx
5d2qj6rP5MQ7zEeDPIV5bUC6ZsSkmw3mbXACrJSDJpQj2dBk3gJva4421V2iVZoGNvkdtRLJ
AsmiXJk/hZ5CMXWYICcL+Q81D/1dLpzrF4zPtSkWu5tRBDi4NNnc47Qz7zzLk/KnREgMmtCH
7hLnDweY0QjvUyF750ixx+IvKebAnUiJZwkHGqS1mP2lkvIwDTrxott1qzSiQTMvBlwez/KQ
VUhaBP1VHM7LGFfBfna8hnu0SiD311nbL4AmxR4+t70k2jD7+XS+Vg==
"

Have you `COMMIT` the changes to the password? If you have not then the updated value will be visible from the session which made the change (but not in other sessions) and if the session ends then the changes will be rolled back. If it is this, then there does not need to be any code that would change the value back - just terminating a session with uncommitted data would be sufficient. — MT0, Jul 24 '19 at 13:42
@MT0 That is an idea, but I am testing from a Oracle SQL Developer session and I believe the session didn't change. I also did a commit in between when it was working. — Adder, Jul 24 '19 at 13:48
How can I look at `SYS.DBMS_CRYPTO_FFI`, it is not listed under my packages? — Adder, Jul 24 '19 at 13:48
Oracle is not likely to be changing a table value silently. Something must be doing that, and committing, while you are sitting idle in your session. You said you didn't commit but for that to happen whatever mechanism you are using to regenerate the password must be commiting - otherwise that mystery session would block. Assuming there is no auditing on the table, I'd start by looking for looping processes, cron/scheduler jobs, or just what other sessions are connected at the same time, etc. (Curious why you store as VARCHAR2 not RAW, but not really relevant; and odd characters are expected.) — Alex Poole, Jul 24 '19 at 14:19
I added the `SERVER_SERVICES` table just lately, there are no jobs or processes changing it. It might be possible that this is caused by `entitymanager.flush` in the java code, but the jobs which might be doing that are disabled on the test system. — Adder, Jul 24 '19 at 14:26
There seem to be two issues here; the stored value in the table apparently changing, and the exception thrown by decryption - which may be the same, but what exception is actually being thrown by DBM_CRYPTO_FFI line 67? You've omitted that; passing in a null key will throw ORA-28239 at that line for instance. Which looking at your previous question would imply something is changing your package's `l_key` value. Finding if/where that might be happening could solve both problems, perhaps. Declare it as constant and see if anything else then fails? Other a [mre] probably would help. — Alex Poole, Jul 24 '19 at 14:37
The hash changing indicates that the server_password value changed. So it is probably not the `l_key` that changes. — Adder, Jul 24 '19 at 15:08
Trying to look at that SYS package is a bit of a red herring. The ORA- error it throws at line 67 is important, not what it's doing internally. (FWIW it's calling a C library anyway, so there is no insight to what it really does.) That error will indicate what it doesn't like about what you are passing in. Seeing your package code might reveal a problem, but your description suggests something outside that and in another session is changing the table value; unless you're calling something in the package you haven't mentioned. — Alex Poole, Jul 24 '19 at 15:30
The key change was a guess but might align with it working once then failing. Are you sure you aren't swapping the key and encrypted value in your encryption/store function, as another guess? I'm not sure what else we can do with the information provided though, sorry. Good luck tracking it down. — Alex Poole, Jul 24 '19 at 15:32
I'll try and see whether it is an `entitymanager.flush` problem in the Java code. Maybe I can change that column to readonly somehow. — Adder, Jul 24 '19 at 15:35

Oracle column in table mysteriously changing value

0 Answers0