Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    london
    Posts
    10

    Unanswered: granting table access problem

    I create a user from sys as follows:

    SQL> create user test identified by test;
    User created.

    SQL> grant create session to test;
    Grant succeeded.

    -- then granted permission for test to do all on one of sys's tables people

    SQL> select * from people;

    NAME USERNAM
    --------------- -------
    Robby Williams robby

    SQL> grant all on people to test;
    Grant succeeded.

    SQL> commit;
    Commit complete.

    --then logged in as user test to see the people table

    SQL> connect test/test;
    Connected.

    SQL> select * from people;
    select * from people
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    --Why do i get this error message? Am i doing something wrong? Any help would be greatly appreciated. Cheers!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: granting table access problem

    When user TEST says "select * from people", Oracle looks for:
    1) a table of that name owned by test (test.people)
    2) a private synonym called PEOPLE owned by test.
    3) a public synonym called PEOPLE.

    In this case, none of the above were found. There are various ways to deal with this:

    1) Specify the table owner:

    test> select * from sys.people;

    2) Alter the current schema first:

    test> alter session set current_schema=sys;
    test> select * from people;

    3) Create a private synonym:

    test> create synonym people for sys.people;
    test> select * from people;

    4) Create a public synonym

    sys> create public synonym people for sys.people;
    test> select * from people;

    By the way, you should NOT be creating objects in the SYS schema. SYS is Oracle's own schema!

  3. #3
    Join Date
    Mar 2004
    Location
    london
    Posts
    10

    Re: granting table access problem

    Thanks alot for that


    Originally posted by faatimah
    I create a user from sys as follows:

    SQL> create user test identified by test;
    User created.

    SQL> grant create session to test;
    Grant succeeded.

    -- then granted permission for test to do all on one of sys's tables people

    SQL> select * from people;

    NAME USERNAM
    --------------- -------
    Robby Williams robby

    SQL> grant all on people to test;
    Grant succeeded.

    SQL> commit;
    Commit complete.

    --then logged in as user test to see the people table

    SQL> connect test/test;
    Connected.

    SQL> select * from people;
    select * from people
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    --Why do i get this error message? Am i doing something wrong? Any help would be greatly appreciated. Cheers!

Posting Permissions

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