Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Question Unanswered: Grant access to table

    Hi experts
    I'm using Oracle 817. I have 2 users (sc & eisview) in an instance. When I use sqlplus sc/password, it able to view cif table.

    SQL> select count(*) from cif;

    COUNT(*)
    ----------
    176

    But when i use eisview/password to select the same table, error occured

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

    i already grant select on cif table to eisview:
    SQL> grant select on cif to eisview;

    Grant succeeded.

    but user eisview still cannot see the table.
    Please help.

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    You need to qualify the table with the schema owner

    select count(*) from sc.cif;

    Create a synonym on cif to avoid qualifying the table with the schema name.

    http://download-west.oracle.com/docs...ements_72a.htm

    .

  3. #3
    Join Date
    Dec 2005
    Posts
    3

    Smile

    I create synonym but the problem is i'm actually needs the table for the schema, not synonyms. At last i create cif table for eisview schema:

    SQL> create table cif as select * from sc.cif;

    Table created.

    I think the problem is solved. Is it true?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps it is. But, more likely, in a month or two you'll encounter unexplainable problems as the same query will give you different results when run on those schemas as you'll forget that this is a TABLE here instead of a SYNONYM.

    When dealing with synonyms, you use the same set of data in all schemas whose synonyms refer to the same original table.
    Your "solution" will be OK as long as noone changes any of those tables. You could manage this on two schemas, but what will happen when another schema appears and needs to use data in the 'cif' table? Will you create another one? Who will be responsible for the maintenance?

    Finally, what is wrong with synonyms? Why would you prefer tables instead of synonyms? In such a case, it doesn't seem to be a clever choice.

  5. #5
    Join Date
    Dec 2005
    Posts
    3

    Cool

    Why do I use table, not synonyms?
    I need to download the data from Oracle/Solaris into SQL Server/Windows. That's in development. when time comes to do the migration to Production, all settings will be mapped to production. That is if I use synonym in development, problem will occured during migration to production because there's actually table in production, not synonym.
    I hope that will answer.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you wrote those few sentences in the first post, everything would be much more clear and people here wouldn't have to guess what your are trying to do.

    Reading your last post, however, it seems that the development environment should be exact copy of the production database. Moreover, are you saying that - when the development is finished - you'll just move everything (tables, database triggers, packages, ...) to the production? Because, if it is only the CODE you're moving, it really doesn't matter whether it is a table or a synonym. For example, in a statement like

    SELECT empno, ename, loc FROM employees;

    you can't tell is the 'employees' table or a synonym.

    If you are satisfied with your own solution, that's OK ... I hope you don't mind me discussing about the issue.

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I'm still having trouble understanding the original question. In an Oracle database you could have two tables named "CIF", or ten, or a hundred. SC could have one, EISVIEW could have another, SCOTT, WILLIAM and KERBEROSS could each have their own as well. Now you log in as JOESCHMOE and enter "SELECT * FROM cif;" and you wonder why the database doesn't just give you the one you were thinking of when you ran it?

Posting Permissions

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