This discussion is archived
2 Replies Latest reply: Mar 5, 2013 7:03 AM by Paul Horth RSS

Table Access Issue

885137 Newbie
Currently Being Moderated
Hello ,

I have an issue. I am able to access one table (in different schema) from view but cann't access table directly. view script is like this

select
column1,column2, column3 as (select c1,c2,c3 from table1)

when i tried to access table1 directly getting error "Object table1 not exists" but I am accessing this table from view.
even I tried to findout the table/view name from all_tables / all_views system view.
But 0there is no such table/view esists. Also verified synonyms. Pleae help me how to find
the object, where (schema) it is exists.

Thanks
deb
  • 1. Re: Table Access Issue
    ajallen Pro
    Currently Being Moderated
    You have privileges to select from the view but not the underlying table. This is a fairly common security scheme. You will have to get the table owner ('s dba) to give you privileges directly on the underlying table if you have a business need to directly access it.
  • 2. Re: Table Access Issue
    Paul Horth Expert
    Currently Being Moderated
    882134 wrote:
    Hello ,

    I have an issue. I am able to access one table (in different schema) from view but cann't access table directly. view script is like this

    select
    column1,column2, column3 as (select c1,c2,c3 from table1)

    when i tried to access table1 directly getting error "Object table1 not exists" but I am accessing this table from view.
    even I tried to findout the table/view name from all_tables / all_views system view.
    But 0there is no such table/view esists. Also verified synonyms. Pleae help me how to find
    the object, where (schema) it is exists.

    Thanks
    deb
    That's normal Oracle behaviour. The owner of a table can create a view and grant select access on the view to you which
    allows you to select from the view but doesn't allow you to access (or even see) the underlying table(s) of the view.

    This is a security mechanism - for example there might be columns (like salary) that the owner doesn't want you to see,
    so they exclude them from the view.

    If you want to access the table you must get the owner to grant you access to it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points