-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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.
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.
-
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|