Account for all tables in SQL Plus?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Account for all tables in SQL Plus?

  1. #1
    Join Date
    Mar 2004
    Posts
    9

    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
    Location
    England
    Posts
    7,333
    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.

    HTH

    Dave

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    tahiti.oracle.com
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Mar 2004
    Posts
    9
    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