DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004

    Account for all tables in SQL Plus?

    I am just learning Oracle 9i and SQL Plus. After going through
    setting up everything and examining the starter database I have found
    that SQL Plus will not allow me to everything in the database.

    Example: If I log in using Sys/password I can see system related
    tables but I cant query on the example schemas that were installed.

    If I log in as Scott/tiger I can see that schema and tables.

    Is there an account that will allow me to see everyone's schema setup
    and also query on it?

    I would like to log on once in a non administrator account and be
    able to query on any table in any schema including some of the
    system views.

    I am working on my Applications Developer cert and really need to know
    the workings of tables and schema for development purposes.

    Any help would be appreciated. Thanks JW

  2. #2
    Join Date
    Sep 2002
    you need to grant yourself access to those objects.

    if you log on as sys you can do select * from scott.emp for example.

    If you have an account without dba priveleges you need to grant access to them, so e.g. if you have account called john you would do grant select on scott.emp to john as either the sys user or scott user.

    Then as john you would need to do select * from scott.emp;

    By default in Oracle you have no access to anything apart from your own objects. If you want full access I suggest you create a role and grant select any table to that role (along with whatever other privs you need, e.g update and delete)

    I also wouldnt recommend this in a production system - asking for trouble, however if its only for tesitng go for it

    There are accounts called sys and system which will allow access to anything, but they are special accounts and they are not to be used for general day to day use. So my advice is to create you own user with the right privs you need - a good habit to get into.



  3. #3
    Join Date
    Nov 2000
    Jeff Hunter
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Mar 2004
    That works...will do. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.