CODEWITHSUNDEEP
sqloracle-databasevieworacle-sqldeveloper
PassionateDeveloper
PassionateDeveloper

Reputation: 15138

How to set the right to see a view in oracle?

I am logged in as user "a" in my oracle database. I can write a query like "Select * from MyView" and I get all results - thats fine.
BUT I can't see the view itself in SqlDeveloper in my View-tree (list), additional to that I am not the owner (which I can see with "Select * from all_views").

How can I change the rights of this view to see "MyVIew" in the tree?

Upvotes: 2

Views: 271

Answers (3)

thatjeffsmith
thatjeffsmith

Reputation: 22427

@Littlefoot is correct in his answer. You could of course also browse to the view directly via the 'Other Users' portion of the tree, then drill down into the Views.

BUT, you could also just set your Views filter to 'Include Synonyms'

I wrote a story on this topic for TABLES, but it also applies to VIEWS here.

enter image description here

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

I presume you should query all_objects to see what you're actually dealing with. Why? Because of the following example:

Connected as user mike, I'll create a table, grant select on it to public and create a public synonym:

SQL> connect mike/lion
Connected.
SQL> create table test as select 'Littlefoot' name from dual;

Table created.

SQL> create public synonym myview for test;

Synonym created.

SQL> grant select on test to public;

Grant succeeded.

Connect as another user, scott and select from myview:

SQL> connect scott/tiger
Connected.
SQL> select * from myview;

NAME
----------
Littlefoot

Right; it works. Where is it in SQL Developer?

enter image description here

Aha, it is in public synonyms (I applied filter on name, looking for MYVIEW to skip zillion other public synonyms).

Or, in SQL*Plus:

SQL> select object_name, object_type, owner from all_objects where object_name = 'MYVIEW';

OBJECT_NAME                    OBJECT_TYPE         OWNER
------------------------------ ------------------- ------------------------------
MYVIEW                         SYNONYM             PUBLIC

SQL>

Therefore, I suggest you do the same - query all_objects and you'll know something more about the issue.

Upvotes: 1

Elmo N'diaye
Elmo N'diaye

Reputation: 49

you can not change the right of the view to see it your View-tree (list), because it is not your own object. To have the view in your View-tree (list) you have to create it in your own shema.

Upvotes: 0

Related Questions