31. Juli 2013

Oracle12c: Unsichtbare Spalten: Wirklich unsichtbar?

Oracle12c Invisible Columns: Really invisible?
Heute geht es um die in Oracle12c neuen Invisible Columns. Das ist zwar ein ganz einfaches Feature, aber man kann auch ein wenig was dazu erzählen ...
create table tab_invcolumns (
  id          number(10),
  name        varchar2(200),
  kennzeichen varchar2(20) invisible 
)
/

alter table tab_invcolumns modify (kennzeichen visible)
/

alter table tab_invcolumns modify (kennzeichen invisible)
/
Bei einem CREATE TABLE kann nun also zu einer Spalte das Attribut INVISIBLE vergeben werden; mit einem ALTER TABLE ... MODIFY kann es nachträglich geändert werden. Der Effekt ist, dass die Spalte bei einem describe Kommando nicht mehr angezeigt werden.
SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
In einem SQL*Plus der Version 12c kann man jedoch mit set colinvisible ON|OFF die Anzeige der unsichtbaren Spalten in einem describe ein- oder ausschalten ...
SQL> set colinvisible on

SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
 KENNZEICHEN (INVISIBLE)                            VARCHAR2(20)
Wenn man in die Data Dictionary View USER_TAB_COLS hineinguckt, findet sich dort die Spalte HIDDEN_COLUMN, welche Auskunft über die Sichtbarkeit einer Spalte gibt.
SQL> select column_name, data_type, hidden_column 
  2  from user_tab_cols where table_name='TAB_INVCOLUMNS' ;

COLUMN_NAME                    DATA_TYPE                      HID
------------------------------ ------------------------------ ---
ID                             NUMBER                         NO
NAME                           VARCHAR2                       NO
KENNZEICHEN                    VARCHAR2                       YES
Für Anwendungen und Entwickler werden unsichtbare Spalten dann hochinteressant, wenn man das Datenmodell erweitern möchte und ...
  • ... in der Anwendung SQL-Abfragen mit SELECT * FROM ... verwendet wurden, oder ...
  • ... SQL INSERT-Anweisungen ohne Spaltenliste (INSERT INTO {TABLE} VALUES (...)) programmiert wurden.
Wenn die Spaltenliste in einer SQL INSERT Anweisung fehlt (was eigentlich kein schöner Stil ist, aber dennoch immer wieder vorkommt), müssen normalerweise alle Tabellenspalten "versorgt" werden. Ab Oracle12c kann man sagen: müssen alle sichtbaren Spalten versorgt werden. Das folgende SQL INSERT funktioniert also, obwohl für die Spalte KENNZEICHEN kein Default festgelegt wurde.
SQL> insert into tab_invcolumns values (1, 'CZARSKI');

1 Zeile wurde erstellt.
Das geht sogar noch weiter. Wird keine Spaltenliste, jedoch ein Wert für die unsichtbare Spalte vergeben, so gibt es eine Fehlermeldung. Das folgende INSERT schlägt fehl, obwohl die Tabelle drei Spalten hat - und drei Werte angegeben wurden. Die Tabelle hat eben nur zwei sichtbare Spalten.
SQL> insert into tab_invcolumns values (1, 'CZARSKI', null);
             *
FEHLER in Zeile 1:
ORA-00913: Zu viele Werte
Analog dazu zeigt ein SELECT * nur die sichtbaren Spalten an - unsichtbare Spalten werden unterdrückt. Und das ist besonders interessant, wenn eine Anwendung ein SELECT * macht, eine bestimmte Spaltenliste erwartet und die Tabelle nun erweitert werden soll.
SQL> select * from tab_invcolumns

        ID NAME
---------- ------------------------------
         1 CZARSKI
Eines sei nochmals gesagt: Unsichtbare Spalten dienen keinesfalls dazu, Inhalte zu schützen - denn sie lassen sich normal nutzen, wenn man sie explizit anspricht. Auf keinen Fall ist eine unsichtbare Spalte als Zugriffsschutz zu verstehen: Dazu gibt es andere Möglichkeiten.
SQL> insert into tab_invcolumns (id, name, kennzeichen) values (2, 'MUELLER', 'NORMAL');

1 Zeile wurde erstellt.

SQL> select id, name, kennzeichen from tab_invcolumns;

        ID NAME                           KENNZEICHEN
---------- ------------------------------ --------------------
         1 CZARSKI                        ?
         2 MUELLER                        NORMAL

2 Zeilen ausgewählt.
Für die Experten sei erwähnt, dass die unsichtbaren Spalten eigentlich gar kein neues Feature sind - strenggenommen kennt die Oracle-Datenbank sie bereits mindestens seit der Version 8. Das kann man einfach selbst nachvollziehen, indem man auf einer älteren Datenbank eine Tabelle mit einer XMLTYPE-Spalte oder einer Objekttyp-Spalte anlegt. Neu in Oracle12c ist, dass man das Spaltenattribut INVISIBLE nun selbst verwenden kann - und zwar für beliebige Tabellenspalten. Das folgende Skript zeigt eine "Invisible Columns Experience" in Oracle10g auf Basis eines Objekttypen - in Oracle9 und Oracle8 sollte es analog funktionieren ...
Verbunden mit:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> create type person_t as object(
  2    name varchar2(200),
  3    vorname varchar2(200),
  4    gebdatum date
  5  )
  6  /

Typ wurde erstellt.

SQL> create table tab_personen(
  2    id number(10),
  3    person person_t
  4  )
  5  /

Tabelle wurde erstellt.

SQL> select column_name, data_type, hidden_column from user_tab_cols 
  2  where table_name='TAB_PERSONEN'

COLUMN_NAME                    DATA_TYPE       HID
------------------------------ --------------- ---
ID                             NUMBER          NO
PERSON                         PERSON_T        NO
SYS_NC00003$                   VARCHAR2        YES
SYS_NC00004$                   VARCHAR2        YES
SYS_NC00005$                   DATE            YES

SQL> select id, SYS_NC00003$, SYS_NC00004$, SYS_NC00005$ from tab_personen

        ID SYS_NC00003$    SYS_NC00004$    SYS_NC00005$
---------- --------------- --------------- -------------------------
         1 Mustermann      Max             31.07.1984 00:00:00
This blog posting is about one of the new features in Oracle12c: Invisible Columns. This is a very simple one (but definitively worth writing a blog posting about) ...
create table tab_invcolumns (
  id          number(10),
  name        varchar2(200),
  kennzeichen varchar2(20) invisible 
)
/

alter table tab_invcolumns modify (kennzeichen visible)
/

alter table tab_invcolumns modify (kennzeichen invisible)
/
So - in a CREATE TABLE statement we can use the INVISIBLE attribute for a column. We can furthermore switch a columns' visibility with ALTER TABLE ... MODIFY. An invisible column will not be contained in a describe result.
SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
In SQL*Plus Version 12c you can use the set colinvisible switch. Setting COLINVISIBLE to ON lets SQL*Plus include the invisible columns into the result of describe.
SQL> set colinvisible on

SQL> desc tab_invcolumns
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(200)
 KENNZEICHEN (INVISIBLE)                            VARCHAR2(20)
The data dictionary view USER_TAB_COLS contains the HIDDEN_COLUMN attribute. This column tells us, whether a table column is visible (NO) or invisible (YES).
SQL> select column_name, data_type, hidden_column 
  2  from user_tab_cols where table_name='TAB_INVCOLUMNS' ;

COLUMN_NAME                    DATA_TYPE                      HID
------------------------------ ------------------------------ ---
ID                             NUMBER                         NO
NAME                           VARCHAR2                       NO
KENNZEICHEN                    VARCHAR2                       YES
Invisible columns are very interestin for application developers when it's about extending the applications data model (ALTER TABLE ADD COLUMN) and the application contains ...
  • ... SELECT * FROM ... queries or ...
  • ... SQL INSERT statement without a column list (INSERT INTO {TABLE} VALUES (...)).
In a SQL INSERT statement without a column list, the VALUES clause must contain a value for each table colunn. Beginning with Oracle12c we must say: "for each visible table column". So the following SQL statement executes successfully, although we did not provide a column list and the VALUES clause contains only two values.
SQL> insert into tab_invcolumns values (1, 'CZARSKI');

1 row created.
If we now add a value for the invisible column, but still don't provide a column list, the statement will fail. The table has three columns - and we provided three values. But the table only has two visible columns: So the INSERT statement indeed contains "too many values".
SQL> insert into tab_invcolumns values (1, 'CZARSKI', null);
             *
ERROR in line 1:
ORA-00913: Too many values
According to this, a SELECT * query will return only visible columns - invisible ones will be suppressed. That is very important for applications which (a) execute these queries agains the database, (b) expecting a particular set of columns and (c) we are now about the extend the table ...
SQL> select * from tab_invcolumns

        ID NAME
---------- ------------------------------
         1 CZARSKI
I'd like to emphasize: Invisible columns are no security feature. The INVISIBLE attribute is absolutely inappropriate to implement access control for a table column, since we can use invisible columns like the visible ones - the only difference is that invisible columns must be used explicitly. For access control, we have other functions and features.
SQL> insert into tab_invcolumns (id, name, kennzeichen) values (2, 'MUELLER', 'NORMAL');

1 row created.

SQL> select id, name, kennzeichen from tab_invcolumns;

        ID NAME                           KENNZEICHEN
---------- ------------------------------ --------------------
         1 CZARSKI                        ?
         2 MUELLER                        NORMAL

2 rows selected.
Honestly, invisible columns are not really a "new" feature - the Oracle database uses invisible columns at least since Oracle8 (don't know about Oracle7). The following script (runs in Oracle8 and higher) creates a table with an object type. For the attibutes of this object type, Oracle creates invisible table columns - since Oracle8. The real "new" thing in Oracle12c is that we now have the "official" attribute INVISIBLE, which we can use for any table column.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> create type person_t as object(
  2    name      varchar2(200),
  3    firstname varchar2(200),
  4    birthdate date
  5  )
  6  /

Type created.

SQL> create table tab_persons(
  2    id number(10),
  3    person person_t
  4  )
  5  /

Table created.

SQL> select column_name, data_type, hidden_column from user_tab_cols 
  2  where table_name='TAB_PERSONS'

COLUMN_NAME                    DATA_TYPE       HID
------------------------------ --------------- ---
ID                             NUMBER          NO
PERSON                         PERSON_T        NO
SYS_NC00003$                   VARCHAR2        YES
SYS_NC00004$                   VARCHAR2        YES
SYS_NC00005$                   DATE            YES

SQL> select id, SYS_NC00003$, SYS_NC00004$, SYS_NC00005$ from tab_personen

        ID SYS_NC00003$    SYS_NC00004$    SYS_NC00005$
---------- --------------- --------------- -------------------------
         1 Doe             John            31.07.1984 00:00:00

Keine Kommentare:

Beliebte Postings