4 Replies Latest reply on Aug 7, 2017 9:47 PM by John McGinnis-Oracle

    SYS objects (directories) showing under a normal user's tree

    EdStevens

      SQL Dev 4.0.2.15 build 15.21

       

      While making sure it was safe to drop an account for a departing employee, I was doing a sanity check by walking down the navigation tree for the user account.  I was surprised to see several database links listed, and on closer inspection, they are all owned by SYS.  So why are db links owned by SYS showing under a normal user?

       

        • 2. Re: SYS objects (directories) showing under a normal user's tree
          EdStevens

          Vadim Tropashko-Oracle wrote:

           

          Reproduced, bugged, thank you.

          Ok, good to know.

          While at it, you might also want to check "Editions" and "XML DB Repository", tough I've never worked with either and so don't uderstand the implications ... this may be normal ??

           

          From the same user:

           

           

          • 3. Re: SYS objects (directories) showing under a normal user's tree
            JuanM

            EdStevens wrote:

             

            SQL Dev 4.0.2.15 build 15.21

             

            While making sure it was safe to drop an account for a departing employee, I was doing a sanity check by walking down the navigation tree for the user account. I was surprised to see several database links listed, and on closer inspection, they are all owned by SYS. So why are db links owned by SYS showing under a normal user?

             

             

            I tested what you saying and it seems you can see those directories because "the query to retrieve this information" is done using the connected privilleged user (DBA or similar) privileges. So, I agree, this would not happen, in this navigation between the users, you should be able to see the objects that "displayed user" is allowed to see. So, if you can connect with user you want to drop, different result you will have.

            • 4. Re: SYS objects (directories) showing under a normal user's tree
              John McGinnis-Oracle

              Directories are a special case of object in the database. All directories are owned by SYS, even directories created by other privileged users. To be truly consistent, the Directories folder thus should only show for the SYS user (either for connections to SYS or when looking at the SYS schema under Other Users). However, that’s not necessarily convenient.

               

              We’ve opted to display the Directories folder for all users. The folder displays all directories the user has access to. Any directories that a user creates will be listed, as the creator of a directory is granted access by default. In the current release, the same list of Directories will display for any user under Other Users. However, this behavior is somewhat confusing.

               

              In a future release, we are going to remove the Directories folder from users under the Other Users folder. In this, it will be similar to how Public Synonyms and Public Database Links work, in that these objects all exist within a single global namespace. Unlike Public Synonyms and Public Database Links, the list of Directories displayed will be limited to ones the user can access.

               

              As an aside, we have a report that will list all the objects owned by a user. Running it as that user or as a DBA provides a quick way to sanity check what will be dropped when dropping the user.

               

              - John

              SQL Developer Team