grant select column privileges to user on condition that users can only access column related to their id column - Oracle pl/sql

Question

I got some tables:

Book(bookId, libraryId, bookName, bookType);

BookType(bookType, typeName);

Library(LibrayId);

User(UserId);

BookBorrowed(BorrowId, LibraryId, UserId);

BorrowDetail(BorrowId, bookId)

and a user created in sqlDeveloper, C##DG.

How to GRANT privileges(select) ON BookBorrowed and User with UserId = 1(C##DG can only see and use SELECT the data on the 2 table whose UserId column is 1)?.

Can you guys show me some examples?

Thanks you very much.

Answer 1

The proper Oracle solution to this is Virtual Private Database.

if that isn't an option, another way is to define views such as:

create view userBookBorrowed
   as select * from bookBorrowed bb
      join users u on (u.userid = bb.userid)
      where u.username = USER;

Then only grant the users access to the view, not the table.