Navigating views in oracle database

When it comes to databases, views are some of the things that come in handy especially in production environments. One known major advantage of using views is the security layer it adds to databases or data. In a scenario where I want to hide sensitive data from user A, I can create a view that excludes columns that have sensitive data and grant this view the user A. Whenever user A queries this view, they will see limited number of columns as compared to the original table.

Views in Oracle databases
While most concepts around database views are similar across most database management systems, there are a few differences when it comes to oracle database.

1. Role based privileges The view owner in oracle database should have direct privileges on base tables and not role-based privileges. In all view scenarios, for a user to create a view, they need SELECT privileges on the base tables ie the tables the view is querying. In other relational database management systems such as PostgreSQL, if the user creating the view has these privileges via a role, the view creation will be successful. However, when it comes to oracle, the Create view command will fail if the privilege on base tables is via a role. Therefore, the view creator should be granted SELECT on base tables directly. Example: We have a table called books with the columns bookName, bookID, CreateDate and Author. User A wants to create a view that will display bookName and Author. This means User A will need SELECT privilege on these two columns in the books table.

SELECT privilege via a role:
CREATE ROLE dataSele;

GRANT SELECT ON bookName, Author TO dataSele;
GRANT dataSele TO A;

Below query will fail in oracle when User A runs it:

CREATE VIEW authData AS 
SELECT bookName, Author FROM books;

Fix:
GRANT SELECT ON bookName, Author TO A;

After granting direct select privileges to User A, below query will run successfully:

CREATE VIEW authData AS 
SELECT bookName, Author FROM books;

1. Granting other Users privileges on the view owned by User A PostgreSQL

GRANT SELECT ON authData TO B;

User B runs query successfully

SELECT * FROM authData;

Oracle
User B gets an error after running below query:

SELECT * FROM authData;

Why is user B query unsuccessful in oracle?
For a user to successfully query a view owned by another user in oracle, the view owner should have the grant option privilege on the base tables ie User A should have the privilege of granting privileges they have on base tables to other users.

Solution:

GRANT SELECT ON books TO A WITH GRANT OPTION;
GRANT SELECT ON authData TO B;

User B will run below query successfully:
SELECT * FROM authData;

Conclusion
Migrating from other database management systems to oracle can sometimes seem to be a challenge but don't worry, once you have the SQL basics everything else will eventually fall in to place.