Oracle Hide Column From User

Oracle Hide Column From User | In this post, we will learn how to hide the column from the user. Sometimes some data are sensitive and it should not be visible to every user but the table should be visible.

Oracle Hide Column From User Using View

For example, in a company, the name and ids of the employees can be visible to everyone but their salary details should not be visible to every employee/user. The simplest way to do this is to create a view of the table that does not contain all of the columns. Don’t grant select on the table, but only on the view.

Oracle Hide Column From User Using Invisible Column

Let’s first create a table “student”, to demonstrate how to hide columns from the user. Execute the below query to create the table:-

CREATE TABLE student
(
   id NUMBER,
   subject VARCHAR2(50) invisible
);

Let us insert values in the table student by executing the below query.

INSERT INTO student VALUES(1);

Let us commit it to saving permanently which cannot go back to the previous stage.

COMMIT; 

Now let us check the record in the student table by executing the following query.

SELECT * FROM student;

Output:-

   ID 
-----
    1     

Again now we will insert some more values to the table student by executing the following queries.

INSERT INTO student(id, subject) VALUES(2, 'Mathematics'); 
COMMIT;

Now let’s check the table student, the column made invisible will not show the records to the user. Execute the following query:-

SELECT * FROM student;

Output:-

   ID 
-----
    2   
    1

From the above output, we can say that the subject column is invisible to the user.

Note:- Invisible columns are still available for all actions, but need to be provided they are named explicitly.

Let’s execute a query where we provide the name of the invisible column explicitly in the select statement to display.

SELECT id, subject FROM student;

Output:-

     ID        SUBJECT
---------- --------------
      1          -
      2      MATHEMATICS

Facts about invisible columns include the following:

• A table can be partitioned by an invisible column, during or after table creation.
• Virtual columns can be invisible.
• External, cluster, and temporary tables cannot have invisible columns.
• User-defined types cannot have invisible attributes.
• System-generated hidden columns cannot be made visible.

Also see:- Truncate AUD$ table in Oracle 12c

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search