Table access issue

[suhasd75]

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

[vnktummala]

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

Thanks,

[suhasd75]

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

[davey23uk]

they aren't the actual commands

post the real output

[vnktummala]

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,

[suhasd75]

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

[vnktummala]

Suhas,

Try this ...

SQL> grant select on adtrack.candidates to adtrack_user;

Thanks,

[suhasd75]

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

[davey23uk]

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

providing only little snippets helps no-one - you mainly