privileges

[Mentallo9]

Security question which is probably asinine, but I am stumped:
I created a Oracle 8.1 DB on Solaris 7. I created two users lets say A and B. User A created a bunch of objects. User A then granted select, insert, update and delete on these objects to user B. Oracle tells me the grants were successful, but when I log in as user B and try to query the database, I get the "Table or view does not exist" error. I have tried specifying the proper schema and all different combinations of caps -- is there some tool I need to turn on or some other grant that user B needs?

Thanks in advance,
Neophyte in trouble

[marist89]

As userB, run this query:
select owner, table_name
from all_tables
where owner = 'A'

If you get rows back, then b can see a's tables. If you don't get any rows back, something is wrong with the grants.

If you get rows back, then you can access a's tables by (case does not usually matter):
select * from a.mytable

If you get rows back and can not select data from them, post the results of the first query...

[Mentallo9]

Thanks for the quick response -- here's the output that is driving me nuts. I changed thge users/passwords but other than that this is right from my telnet. A does have roughly 25 tables full of data.
Thanks
Brian

bash-2.03$ sqlplus A/password

SQL*Plus: Release 8.1.5.0.0 - Production on Tue Dec 19 08:42:59 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> grant select on all_tables to B;

Grant succeeded.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
bash-2.03$ sqlplus B/password

SQL*Plus: Release 8.1.5.0.0 - Production on Tue Dec 19 08:45:03 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> select owner, table_name from all_tables where owner='A';

no rows selected

SQL>

[marist89]

When issuing a GRANT, you grant permissions to a particular table. For example (as a):
grant select on mytable to b;

To find out all of the tables that a owns, logon as A and run this query:
select table_name from user_tables;

Then, when you get the result of the above query, issue a grant statement for each table. For example, if one of the tables returned was mytable, then your grant wuld be:
GRANT SELECT,INSERT, UPDATE, DELETE ON mytable TO B;

[Mentallo9]

Hi Jeff - I get the same output as before using the specific table grant. There is only one database and one schema so I am very confused as to why B cannot see any of A's objects.
Brian

SQL> grant SELECT, UPDATE, DELETE, INSERT ON B_MANUFACTURER TO B;

Grant succeeded.

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release

bash-2.03$ sqlplus B/password


SQL> select * from B_MANUFACTURER;
select * from B_MANUFACTURER
*
ERROR at line 1:
ORA-00942: table or view does not exist

[marist89]

You must reference A as the owner...

select * from A.B_MANUFACTURER

[Mentallo9]

Oh geez...I was afraid it was something stupid like this. Thanks for your help and have a good week!
Brian

[marist89]

Sometimes all you need is a second set of eyes to look at it...