0

Is there a privilege in Oracle that lets a user view or not view the list of objects in another schema?

I've got a user where I've given them select rights to a view in another user's schema. However, they don't seem to be able to see that view in their client (think they are using Oracle's SQL Developer).

3 Answers 3

1

It is not a privilege, but a view - all_tables might help.

Connected as Scott, I'll grant select privilege to Mike:

SQL> show user
USER is "SCOTT"
SQL> grant select on dept to mike;

Grant succeeded.

Connect as Mike:

SQL> connect mike/xyz@orcl
Connected.

SQL> select table_name
  2  from all_tables
  3  where owner = 'SCOTT';      --> note OWNER

TABLE_NAME
------------------------------
DEPT

SQL>

If you omit where clause, you'll see all tables you have access to, but - it'll also show a lot of SYS, SYSTEM and similar users' owned tables, which is something you probably aren't interested in so - skip them, if you want.

0

Long and the short of it, there isn't a permission per se. ALL_TABLE and ALL_VIEWS will show you only the tables/views you have access to read from (as described by @Littlefoot).

I was presuming that Oracle might have a permission scheme like the Unix chmod 711 (rwx--x--x) - you can execute and work with files in the directory, but you can't list the contents of the directory.

In this particular case, the issue seems to have been Oracle SQL Developer and some form of caching.

Closing and reconnecting the Oracle connection in question did not refresh the changes in the access rights. However, shutting down the application completely and reopening did allow them to come through.

Evidently, the user in question hadn't restarted their application (or for that matter workstation) in sometime either.

A.K.A. Have you tried turning it off and on again?

-1

Is there a privilege in Oracle that lets a user view or not view the list of objects in another schema?

Oh, yes. Lots of them. :-)

I've got a user where I've given them select rights to a view in another user's schema.

I'm guessing you're still at the "Users are important to Security" stage (Trust me; they're not).
I'd strongly suggest using Roles to confer privileges. Then, when a given User moves on and someone comes in to replace them, you just grant them the Role and everybody's happy. Without this, you'll probably have to try and rebuild all this from scratch (because you'll have deleted the first Users account, and all their privileges, when they left!)
Anyway ...

However, they don't seem to be able to see that view in their client (think they are using Oracle's SQL Developer).

This might be as simple as they don't know where to look in that client!
Multiple schemas throws a lot of people when they start with Oracle.

When they fire up SQL Developer, they can see all the objects that their account owns.
To find objects owned by any other account, they have to rummage through the "tree" to find the "Other Users" entry, expand that, find the user in question and all the objects owned by that account (that they have privileges to see) will appear there.

+ Connection1
  + Tables
  + Views
    . . . 
  + Other Users   <-- This is the Trouble-maker  
    + User_2 
      + Tables
      + Views 
        . . . 
    + User_3 
      + Tables
      + Views 
        . . . 
2
  • I'm guessing you are still at the "I have to be arrogant to show off how smart I am" stage of your career, and your manager has locked you in a room to keep the business safe from you and your attitude. I know I would with any of my team acting like you. However, you keep doing you.
    – BIBD
    Feb 17, 2023 at 15:09
  • Thanks! I will. But I strongly suspect I'll continue to get queries, just like this, from so-called "professional" Oracle Developers at least once a month, just as I do now. The bit about Roles stands despite all that, though.
    – Phill W.
    Feb 20, 2023 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.