Oracle 12: Invisible Column

New cool feature in Oracle 12. Let's do some test and see what's the behavior looks like.




Setup: create a table with invisible column


Create table inv_tble (
name varchar2(50),
name_inv varchar2(50)
);


Test # 1: Insert 5 records to the table.


insert into inv_tble (name, name_inv) values ('John', 'Inv_John');
insert into inv_tble (name, name_inv) values ('Moe', 'Inv_Moe');
insert into inv_tble (name, name_inv) values ('Doe', 'Inv_Doe');
insert into inv_tble (name, name_inv) values ('Coe', 'Inv_Coe');
insert into inv_tble (name, name_inv) values ('Sandy', 'Inv_Sandy');
commit;

Test# 2: Select * from inv_tble


select * from inv_tble;

NAME  NAME_INV
----- ----------
John  Inv_John
Moe   Inv_Moe
Doe   Inv_Doe
Coe   Inv_Coe
Sandy Inv_Sandy


Describe the table.

desc inv_tble;
Name              Null?    Type
----------------- -------- ------------
NAME                       VARCHAR2(50)
NAME_INV                   VARCHAR2(50)


Test #3: Changing NAME_INV to invisible


SQL> alter table inv_tble modify NAME_INV invisible;

Table altered.



Test # 4: Describe Invisible Column

desc inv_tble;
Name              Null?    Type
----------------- -------- ------------
NAME                       VARCHAR2(50)


Note: the NAME_INV no longer showing up

Couple ways to find out what columns I have in INV_TBLE;

1 - if column_id is NULL in user_tab_columns then that's an invisible column. It has no ID and nothing to trace which make sense.

col table_name format a20
col column_name format a20
select table_name, column_name, column_id from user_tab_columns where table_name='INV_TBLE';

TABLE_NAME           COLUMN_NAME           COLUMN_ID
-------------------- -------------------- ----------
INV_TBLE             NAME                          1
INV_TBLE             NAME_INV


2 - set colinvisible on


SQL> set colinvisible on;
SQL> desc inv_tble;
Name                                                  Null?    Type
----------------------------------------------------- -------- ----------------------------------
NAME                                                           VARCHAR2(50)
NAME_INV (INVISIBLE)                                           VARCHAR2(50)



Note: If user only need to find out if a single table has invisible column, clearly, SET COLINVISIBLE ON is a better option. If it's for multiple tables, user might need to query the USER_TAB_COLUMNS view.



Test # 4: Select * from the table. The invisible column will not showed up anymore

select * from inv_tble;

NAME
-----
John
Moe
Doe
Coe
Sandy



So, are they gone ? This is going to be one big different than Oracle Redaction and Column Encryption. User can still look at the data if they know the column name.

Not nearly, if user knows the name of the invisible column

SQL> select name, name_inv from inv_tble;

NAME  NAME_INV
----- ----------
John  Inv_John
Moe   Inv_Moe
Doe   Inv_Doe
Coe   Inv_Coe
Sandy Inv_Sandy

SQL> select name, name_inv from inv_tble where name_inv='Inv_Sandy';

NAME  NAME_INV
----- ----------
Sandy Inv_Sandy

Test # 5 : Insert along the invisible column. As long as you can specify the column name you can insert it otherwise, user will get ORA-00913

insert into inv_tble (name, name_inv) values ('Spiderman', 'Inv_Spiderman');
1 row created.

select name, name_inv from inv_tble;

NAME            NAME_INV
--------------- ---------------
John            Inv_John
Moe             Inv_Moe
Doe             Inv_Doe
Coe             Inv_Coe
Sandy           Inv_Sandy
Spiderman       Inv_Spiderman

6 rows selected.

select * from inv_tble;

NAME
---------------
John
Moe
Doe
Coe
Sandy
Spiderman

6 rows selected.

SQL>  insert into inv_tble values ('Batman','Inv_Batman');
insert into inv_tble values ('Batman','Inv_Batman')
             *
ERROR at line 1:
ORA-00913: too many values




Test # 6: Update 2 records

update inv_tble set name='Superman' where Name_inv='Inv_Spiderman';

1 row updated.

select name, name_inv from inv_tble;

NAME            NAME_INV
--------------- ---------------
John            Inv_John
Moe             Inv_Moe
Doe             Inv_Doe
Coe             Inv_Coe
Sandy           Inv_Sandy
Superman        Inv_Spiderman

6 rows selected.

update inv_tble set name_inv='Inv_Superman' where name_inv='Inv_Spiderman';

1 row updated.

select name, name_inv from inv_tble;

NAME            NAME_INV
--------------- ---------------
John            Inv_John
Moe             Inv_Moe
Doe             Inv_Doe
Coe             Inv_Coe
Sandy           Inv_Sandy
Superman        Inv_Superman

6 rows selected.

SQL> commit;

Commit complete.


Test # 7: Delete the invisible column as where clause. Nothing fancy. It works just like update. If you know the invisible column, you can do anything.

delete from inv_tble where Name_inv='Inv_John';

1 row deleted.

select name, name_inv from inv_tble;

NAME            NAME_INV
--------------- ---------------
Moe             Inv_Moe
Doe             Inv_Doe
Coe             Inv_Coe
Sandy           Inv_Sandy
Superman        Inv_Superman



Test # 8: Delete without where clause. Again, nothing special, delete WITHOUT where clause will wipe out all the records.

SQL> select name, name_inv from inv_tble;

NAME            NAME_INV
--------------- ---------------
Moe             Inv_Moe
Doe             Inv_Doe
Coe             Inv_Coe
Sandy           Inv_Sandy
Superman        Inv_Superman

SQL> delete from inv_tble;

5 rows deleted.

SQL> select name, name_inv from inv_tble;

no rows selected

SQL> rollback;

Rollback complete.

SQL> select name, name_inv from inv_tble;

NAME            NAME_INV
--------------- ---------------
John            Inv_John
Moe             Inv_Moe
Doe             Inv_Doe
Coe             Inv_Coe
Sandy           Inv_Sandy
Superman        Inv_Superman

6 rows selected.



The end of the testing.

Test # 9: Truncate the table and verify the invisible column

SQL> truncate table inv_tble;

Table truncated.

SQL> select * from inv_tble;

no rows selected

SQL>  select name, name_inv from inv_tble;

no rows selected


Test # 10: Alter the table. Getting back the Column ID. It was NULL when the column was invisible.


SQL> alter table inv_tble modify name_inv visible;

Table altered.

SQL> desc inv_tble;
Name                                                  Null?    Type
----------------------------------------------------- -------- -------------------------------
NAME                                                           VARCHAR2(50)
NAME_INV                                                       VARCHAR2(50)


SQL> select table_name, column_name, column_id from user_tab_columns where table_name='INV_TBLE';

TABLE_NAME           COLUMN_NAME           COLUMN_ID
-------------------- -------------------- ----------
INV_TBLE             NAME                          1
INV_TBLE             NAME_INV                      2



I think I have done enough DML testing on Invisible column and concluding that, Invisible Column feature is offering a data masking agent but not nearly a security feature. One can look at the DBA_TAB_COLUMNS and identify the column names in the table and query the data with fully qualified column name and obtain the data. The invisible column does not offer much security from the application side either since application (forms) can selective picking what to be presented to the interface. This is a fun feature but I am not seeing much values in database design.

No comments:

Post a Comment

Subscribe to: Posts (Atom)