Forum Stats

  • 3,875,939 Users
  • 2,266,998 Discussions
  • 7,912,379 Comments

Discussions

ALTER TABLE MOVE encrypted column + CLOB

Version: 19.13.0.0.0

Issue:

DROP TABLE TEST_TBL_MOVE PURGE
/
CREATE TABLE TEST_TBL_MOVE(
                           ENCRYPTED_COLUMN VARCHAR2(9 CHAR) ENCRYPT USING 'AES192' 'SHA-1' NO SALT  NOT NULL ENABLE, 
                           CLOB_COLUMN      CLOB
                          )
  TABLESPACE USERS
  LOB(CLOB_COLUMN) STORE AS SECUREFILE
/
INSERT
  INTO TEST_TBL_MOVE
  SELECT  '123456789',
          'X'
    FROM  DUAL
/
COMMIT
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
*
ERROR at line 1:
ORA-12899: value too large for column ??? (actual: 25, maximum: 9)

SQL>

If we remove CLOB column table move works fine:

DROP TABLE TEST_TBL_MOVE PURGE
/
CREATE TABLE TEST_TBL_MOVE(
                           ENCRYPTED_COLUMN VARCHAR2(9 CHAR) ENCRYPT USING 'AES192' 'SHA-1' NO SALT  NOT NULL ENABLE
                          )
  TABLESPACE USERS
/
INSERT
  INTO TEST_TBL_MOVE
  SELECT  '123456789'
    FROM  DUAL
/
COMMIT
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS
/

Table altered.

SQL>

If we add CLOB back and don't encrypt other column:

DROP TABLE TEST_TBL_MOVE PURGE
/
CREATE TABLE TEST_TBL_MOVE(
                           UNENCRYPTED_COLUMN VARCHAR2(9 CHAR) NOT NULL ENABLE, 
                           CLOB_COLUMN      CLOB
                          )
  TABLESPACE USERS
  LOB(CLOB_COLUMN) STORE AS SECUREFILE
/
INSERT
  INTO TEST_TBL_MOVE
  SELECT  '123456789',
          'X'
    FROM  DUAL
/
COMMIT
/
ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
/

Table altered.

SQL>

Did anyone ran into this isuue and found solution? I found nothing on Oracle support site.

SY.

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond

    I ran your test on 19.11.0.0 several times and got a similar result, except the "actual" kept changing (varying between 21 and 31). I think the problem is triggered by the CHAR declaration - nothing goes wrong if the declaration is varchar2(9), it only fails on varchar2(9 char).

    If it's really important a possible workaround is to declare the column using byte semantics, allowing for the worst-case byte count for a single character and then add a column constraint that limits it to the 9 characters you want; e.g for a 2-byte character set:

            ENCRYPTED_COLUMN VARCHAR2(18)
                    ENCRYPT USING 'AES192' 'SHA-1' NO SALT
                    NOT NULL
                    check (length(encrypted_column) <= 9),
    

    Regards

    Jonathan Lewis



    P.S. A little warning: the 19c manual says that SHA-1 will be deprecated in 21c.

Answers

  • JohnWatson2
    JohnWatson2 Member Posts: 4,516 Silver Crown

    Column encryption does have a fair bit of overhead for narrow columns. I haven't checked since 11.2 and it may have changed since, but back then columns were always padded out to multiples of 16 bytes. Then salt adds 16 bytes, and MAC adds 20. So a varchar2(1) actually used 52 bytes.

    I know you specified NO SALT, but 9 + 16 = 25 which is a bit of coincidence. Do you get the same effects with and without NO SALT and NOMAC ? Does disabling inline storage for the LOB change things?

    The above are not answers, just possible lines for research.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond

    I ran your test on 19.11.0.0 several times and got a similar result, except the "actual" kept changing (varying between 21 and 31). I think the problem is triggered by the CHAR declaration - nothing goes wrong if the declaration is varchar2(9), it only fails on varchar2(9 char).

    If it's really important a possible workaround is to declare the column using byte semantics, allowing for the worst-case byte count for a single character and then add a column constraint that limits it to the 9 characters you want; e.g for a 2-byte character set:

            ENCRYPTED_COLUMN VARCHAR2(18)
                    ENCRYPT USING 'AES192' 'SHA-1' NO SALT
                    NOT NULL
                    check (length(encrypted_column) <= 9),
    

    Regards

    Jonathan Lewis



    P.S. A little warning: the 19c manual says that SHA-1 will be deprecated in 21c.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,953 Red Diamond
    edited Aug 20, 2022 12:18PM

    Ah, thanks Jonathan, you're right - move fails if encrypted column length semantics is CHAR. So workaround is to change column length semantics to BYTE, move table and change it back to CHAR. Still, I wonder how encrypted column in conjunction to CLOB column makes move to fail. Will see what Oracle says...

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,953 Red Diamond

    And the test:

    SQL> DROP TABLE TEST_TBL_MOVE PURGE
      2  /
    
    
    Table dropped.
    
    
    SQL> CREATE TABLE TEST_TBL_MOVE(
      2                             ENCRYPTED_COLUMN VARCHAR2(9 CHAR) ENCRYPT USING 'AES192' 'SHA-1' NO SALT  NOT NULL ENABLE,
      3                             CLOB_COLUMN      CLOB
      4                            )
      5    TABLESPACE USERS
      6    LOB(CLOB_COLUMN) STORE AS SECUREFILE
      7  /
    
    
    Table created.
    
    
    SQL> INSERT
      2    INTO TEST_TBL_MOVE
      3    SELECT  '123456789',
      4            'X'
      5      FROM  DUAL
      6  /
    
    
    1 row created.
    
    
    SQL> COMMIT
      2  /
    
    
    Commit complete.
    
    
    SQL> ALTER TABLE TEST_TBL_MOVE MODIFY ENCRYPTED_COLUMN VARCHAR2(36 BYTE) -- 9 times 4 (4 is max # of bytes per character in my charset)
      2  /
    
    
    Table altered.
    
    
    SQL> ALTER TABLE TEST_TBL_MOVE MOVE TABLESPACE USERS LOB(CLOB_COLUMN) STORE AS SECUREFILE(TABLESPACE USERS)
      2  /
    
    
    Table altered.
    
    
    SQL> ALTER TABLE TEST_TBL_MOVE MODIFY ENCRYPTED_COLUMN VARCHAR2(9 CHAR) -- back to char length semantics.
      2  /
    
    
    Table altered.
    
    
    SQL>
    

    SY.