-
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
-
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...
Jeff Hunter
-
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>
-
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;
Jeff Hunter
-
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
-
You must reference A as the owner...
select * from A.B_MANUFACTURER
Jeff Hunter
-
Oh geez...I was afraid it was something stupid like this. Thanks for your help and have a good week!
Brian
-
Sometimes all you need is a second set of eyes to look at it...
Jeff Hunter
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
|