Table access issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Table access issue

  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66

    Table access issue

    Hello,

    This sounds really weird but happened in our database yesterday after we migrated to a 11g and RAC from single-instance 10g.

    Only one user has trouble accessing tables of another schema after the migration.

    Example:

    1. Schema = user1
    2. table name = tab1
    3. role name = role1
    4. user1 has given select privilege on tab1 to role1.

    5. app user = user2
    6. Contains no objects of its own
    7. user2 is granted connect and role1 (both default)
    8. select * from user1.tab1;
    9. ERROR at line 1:
    ORA-00942: table or view does not exist

    Can anyone guess what the issue might be?
    A reply at the earliest would be appreciated.

    Thanks!
    Suhas

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Suhas - can you post the actual commands you used for step 4 & 7?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Sure Vijay,

    4. grant select on tab1 to role1; -- logged in as user1

    -- tab1 is a normal table

    7. -- logged in as sys
    sql> grant connect,role1 to user2;
    sql> alter user user2 default role connect,role1;

    8. select * from user1.tab1; -- as user2

    #8 results in ORA-00942

    Any guesses?

    Thank you,
    Suhas

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    they aren't the actual commands

    post the real output

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    you must be typing wrong table name while granting privilege to role. check the table name carefully.

    I have simulated your case in my test box and no issues for me.

    SQL> create user user1 identified by user1;

    User created.

    SQL> grant connect,resource to user1;

    Grant succeeded.

    SQL> conn user1/user1
    Connected.
    SQL> create table tab1 (name varchar2(30));

    Table created.

    SQL> conn /as sysdba
    Connected.
    SQL> create role role1;

    Role created.

    SQL> grant select on user1.tab1 to role1;

    Grant succeeded.

    SQL> create user user2 identified by user2;

    User created.

    SQL> grant connect,role1 to user2;

    Grant succeeded.

    SQL> conn user2/user2
    Connected.

    SQL> select count(*) from user1.tab1;

    COUNT(*)
    ----------
    0

    SQL> conn /as sysdba
    Connected.
    SQL> alter user user2 default role connect,role1;

    User altered.

    SQL> conn user2/user2
    Connected.
    SQL> select count(*) from user1.tab1;

    COUNT(*)
    ----------
    0

    SQL>
    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  6. #6
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Ok, here's the actual SQL session output:

    SQL> conn adtrack
    Enter password:
    Connected.
    SQL> grant select on candidates to adtrack_user;

    Grant succeeded.

    SQL> conn /as sysdba
    Connected.
    SQL> select * from dba_user_roles
    2 where grantee = 'APP_OPS_REPORTS';
    select * from dba_user_roles
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    SQL> select * from dba_role_privs
    2 where grantee = 'APP_OPS_REPORTS';

    GRANTEE GRANTED_ROLE ADM DEF
    ------------------------------ ------------------------------ --- ---
    APP_OPS_REPORTS CONNECT NO YES
    APP_OPS_REPORTS DEVELOP_MANAGER NO NO
    APP_OPS_REPORTS ADTRACK_READ_ONLY NO YES
    APP_OPS_REPORTS AQ_USER_ROLE NO YES
    APP_OPS_REPORTS ADTRACK_USER NO YES
    APP_OPS_REPORTS ADTRACK_MANAGER NO NO
    APP_OPS_REPORTS DEVELOP_USER NO NO
    APP_OPS_REPORTS ADTRACK_DBA NO NO
    APP_OPS_REPORTS RESOURCE NO NO
    APP_OPS_REPORTS AQ_ADMINISTRATOR_ROLE NO NO
    APP_OPS_REPORTS ADTRACK_BILLING NO NO

    11 rows selected.

    SQL> conn app_ops_reports
    Enter password:
    Connected.
    SQL> select * from adtrack.candidates where rownum=1;
    select * from adtrack.candidates where rownum=1
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    SQL>

    Now, what say?

    Suhas

  7. #7
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Suhas,

    Try this ...

    SQL> grant select on adtrack.candidates to adtrack_user;

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  8. #8
    Join Date
    Jan 2004
    Location
    Bangalore, India
    Posts
    66
    Hi Vijay,
    Thanks for all the replies! and yes, your suggestion worked!!
    However, I had opened an SR with Oracle and the following is part of it...

    "
    Starting with 10.2.0.4, password protected roles are not enabled when granted, as this was considered a security hole. t. You'll need to do an ALTER SESSION
    SET ROLE to enable it.
    "
    and I said: "I dont want to do "alter session" everytime. What is the "global" solution?


    29-SEP-09 18:03:45 GMT

    Perhaps a logom trigger to do the alter? "

    They seem to be side-lined with too many complexities over at Oracle

    Again, many thanks for your excellent inputs: I learnt something today...

    Regards,
    Suhas

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    well next time provide all the information and you might get better help!

    providing only little snippets helps no-one - you mainly

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