Skip to content

How to Resolve ORA-28348: index defined on the specified column cannot be encrypted

  • by

ORA-28348

Tried to encrypt a column, but it failed with ORA-28348:

SQL> alter table oe.customers modify (cust_first_name encrypt no salt);
alter table oe.customers modify (cust_first_name encrypt no salt)
                                 *
ERROR at line 1:
ORA-28348: index defined on the specified column cannot be encrypted

Although we use NO SALT to encrypt an indexed column, we still got an error.

ORA-28348 means that the column you attempt to encrypt has a special index which type is either functional-based index, domain index or join index, which is not allowable to encrypt.

Solution

Now, you have 2 options, giving up the idea to encrypt the column or dropping the index.

To solve ORA-28348, we drop the index then encrypt the column without salt.

1. Drop Index

SQL> drop index oe.cust_upper_name_ix;

Index dropped.

2. Encrypt Column

SQL> alter table oe.customers modify (cust_first_name encrypt no salt);

Table altered.

We use NO SALT for later indexing.

To make sure the column has been encrypted, we should know how to check encrypted data.

3. Create a Normal Index

This is an optional step. To balance restrictions of data encryption and data retrieval performance, we create a normal index for the original columns.

SQL> create index oe.cust_name_idx on oe.customers (cust_last_name, cust_first_name);

Index created.

Performance might be somewhat compromised, but it works.

Leave a Reply

Your email address will not be published.