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

Suhas - can you post the actual commands you used for step 4 & 7?

Thanks,

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

they aren't the actual commands

post the real output

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.

Quote:

---

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,

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

Suhas,

Try this ...

SQL> grant select on adtrack.candidates to adtrack_user;

Thanks,

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

well next time provide all the information and you might get better help!

providing only little snippets helps no-one - you mainly