Search code examples
javasqloracle-databasejdbc

Unique Identifier for Oracle DB to identifying each record uniquely

I have a Oracle db where there are no keys defined (primary, unique) and I can't use ROWID, and I can't modify any tables in the db. I have only read access.

Is there a way create unique identifier by our own (using Java)?

I have oracle db and my job is to build the java connector code for oracle db then fetch all the records and publish in Kafka pipeline, then some other team will consume the records and scan it for sensitive info then they will send those records to production team for masking the data in those records. The scanning team found suppose in 5th records sensitive info how they will convey to prod team that mask the 5th records, because there are no key's defined.

So, while publishing the records in Kafka pipeline I need to publish it with some reference if table doesn't have any keys defined, so that the scanning team will pass those records with reference to prod team for masking. I can only read records from db, and not have access to modify the records/tables.

Solution

  • Assuming the source database is entirely static i.e. read-only you can use a hashing algorithm to generate a fingerprint for each row. The stronger the hash the less likely you are to get a hash collision (but also the longer the extract will take to run, so it's a trade off).

    You don't mention the database version. For later versions using STANDARD_HASH sql function. In earlier versions use DBMS_CRYPTO package. In either case you will need to concatenate all the columns of the source table and apply the hash function to the resultant string. You can write dynamic SQL from USER_TAB_COLUMNS to generate the statements for the required tables.

    It is important to provide default values for nullable columns, otherwise 'Y'|null and null|'Y' will hash the same, and you don't want that.

    select  deptno
       ,dname
       ,location
       ,standard_hash(deptno||dname||nvl(location,'@@@'), 'SHA256') as hashkey
from dept;

    Note: if your source database is in use and rows can be added or changed then you are all out of luck. But then there is no excuse for an Oracle database with no keys. That is just plain poor practice.