New cool feature in Oracle 12. Let's do some test and see what's the behavior looks like.
Oracle Reference page: http://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN13866
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
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)
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
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
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
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.
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
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.
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