PL/SQL No Rows Selected on Views by Specific User

Question

Hi I would like to ask for help on Oracle SQL.

I have a View that is accessible to specific users but suddenly today 1 of those users output is always "No Rows Selected". Object grants are present to select on that view but still no rows are being generated.

For a brief example of my observation.

Main Table is Personal_File

View is Personal_File_v which is only select column1, column2, column3,... from Personal_File

User1 is hr_inquiry

User2 is hr_users

User3 is hr_inquiry1 (Created from the Script of hr_inquiry)

running Select * from Personal_file_v

hr_inquiry has no output / no row selected

hr_users generated all data

hr_inquiry1 generated all data

Also tried recreating the view but still has the same problem

Can anyone help me? or where to look on how to fix this?

Thanks!

• Ditto: longshot possibility: Fine Grained access control on the underlying table .. restricting data for "unregistered" users .. ie the new user you created hasn't been "granted" FGAC on that table, and hence can't see anything. (it's a longshot, but it is possible). In truth, we're all guessing until you post an actual, reproducible script.
• Wernfried Domscheit: @Ditto yes it would be possible. However, I don't think you can deploy Fine-Grained Access Control by accident without knowing exactly what you are doing.
• Ditto: @WernfriedDomscheit agreed, but I never said the OP deployed it ... more likely their DBA or other team deployed it, and OP wasn't aware of it ;) That's the possibility I see here ... not that OP deployed it and totally forgot about it .. LOL

Answer 1

I somehow doubt that things are that simple.

A view is just a stored query (a SELECT statement). You said that

• personal_file table's owner created a view by selecting only a few columns from that table and granted appropriate privileges to 3 other users

  • also, there's no WHERE clause which would filter certain rows out

• then, these 3 users run select * from personal_file_v. That won't work because - in order for it to work:

  • personal_file_v must exist in their schema (either as their own object, or a synonym which points to the original owner's view), or
  • there's a public synonym with that name

  You never said which option you use, so - check what's really going on in HR_INQUIRY's schema by checking ALL_OBJECTS. Apparently, object with that name is accessible to it (otherwise you'd get ORA-00942 (table or view doesn't exist), not no rows selected.

If there are no rows being returned,

• personal_file table is empty or insert hasn't been committed
  • that's most probably not the case because 2 other users see data
• you think you're selecting from the "original" personal_file_v, but you aren't - there's another (empty) table or view which takes precedence (most probably created in HR_INQUIRY's schema)
• there actually is a WHERE clause in the view definition which filters all rows out when query is ran by HR_INQUIRY

Well, now you have certain things to check; see if anything of that helps.

• Ian: Yes There is no "where" on that view since there are certain columns that are not need to be shown to those 3 users I listed. personal_file_v has a public synonym and its only unique to that view no other same name. hr_users is made from hr_inquiry script it just had other objects that are not for hr_inquiry users eyes., yes hr_inquiry is already an old working user. also hr_inquiry1 is a duplicate of hr_inquiry I just added 1 on the end of the username yet its working and not like the original. also personal_file cannot be empty since its a employee table of a prod database.
• Ian: the weirdest thing I got from this problem is I made hr_inquiry1 from hr_inquiry script. these 2 users are a mirror of each other, the only thing that is different is the "1" in the username and yet 1 has an output and 1 has none in the same View.