➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
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.
Now let us check the record in the student table by executing the following query.
SELECT * FROM student;
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;
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;
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!