ORA-00904 Invalid column when column exists

[jovery]

Hi I might be missing something obvious here but I'm bashing my head against a wall on this problem.

I have two users A and B, user B has two tables PUB and DETAIL which have SELECT granted to user A and have the following query:

SELECT 
  DISTINCT d.adno,d.vno,d.pubno,p.bwnet
FROM B.DETAIL d, B.PUB p
WHERE TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
AND TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
AND d.billflag = 'Y'
AND d.adno NOT IN
  (SELECT DISTINCT adno
   FROM B.DETAIL
   WHERE TO_DATE(pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
   AND TO_DATE(pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')
   AND (lastbillflag = '1' OR lastbillflag = '999'))
AND d.adno = p.adno
AND d.vno = p.vno
AND d.pubno = p.pubno;

When I run this code as user B (the object owner) it works fine, but when I run it as user A I get

SELECT DISTINCT d.adno,d.vno,d.pubno,p.bwnet
      *
ERROR at line 1:
ORA-00904: invalid column name

Any help would be appreciated

[jovery]

Forgot to mention this is 8.1.7.4 on Solaris

[davey23uk]

I know it sounds stupid, but you can describe those tables from user A cant you?

also

WHERE TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('01-NOV-2004','dd/mm/yy')
AND TO_DATE(d.pubdate,'dd/mm/yy') <= TO_DATE('12-NOV-2004','dd/mm/yy')

Whats the point of the second and?

it it passes the first one, no need to test for the second one and it

[jovery]

Hi,

yes I can describe the tables.

Appreciate your comment about the crap code (it's from one of our developers) but at present I'm more concerned about why the statement fails when the columns are availiable.

Regards

[DaPi]

Hi Jim, since the geatest minds on the internet haven't come up with a solution in two days, I'll have go:

- we are talking about a straight query NOT a proc (grants via roles and all that)?

- where does it start to go wrong if you take it apart?
1)
SELECT * FROM B.DETAIL
SELECT * FROM B.PUB
2)
SELECT * FROM B.DETAIL d, B.PUB p
WHERE d.adno = p.adno
AND d.vno = p.vno
AND d.pubno = p.pubno;
3)
orginal query without the NOT IN (SELECT . . . ) clause

[jovery]

Hi Dapi,

Sorry about the delay in responding....

The problem seems to lie with the B.PUB table, if I attempt any sort of select including a count(*) I get the same error. but only if I run the queries as user A

This is a straight forward SQL*Plus query and all the grants are direct.

Regards

[DaPi]

Hmmmmmmm...... baffling.

I'd look next at the data dict for oddities: are the grants really there? does user A have an object called PUB? what does B.PUB look like?

Raise a TAR?

[jovery]

Thanks Dapi but I've finally found the problem.

On closer inspection of that table I discovered it has a function based index based on a function in schema A. Directly granting execute to that function to user B now allows user A to run the query.

Can't believe I didn't spot it sooner, D'oh!

Regards