Okay, I have two users, both using the same tablespace. One user has some tables and another user has their own tables.
In Oracle 9i I am getting the error Table or View does not exist, using PL/SQL Developer. All of the tables from userA work just fine, no errors at all, so it is only the ones from UserB. However when I type "userB.tablename" as soon as I hit the '.' the menu showing all tables under that user pops up so it sees the table.
Furthermore, when creating the columns for the table under userB, I made a typo before my first compilation of my procedures. When compiling it noticed the discrepancy between what my procedure was calling and what the table contained, so it could actually see the table to check compilation. But when I fixed the typo it just said Table or View does not exist.
I'm relatively new to SQL, and am actually an intern at this office I am working on, but this schema is coming from higher ups since we are expanding a customer's existing database, not creating a fresh one. So my question is what does this problem sound like and what should I check, because I am lost a bit right now.
I fixed the problem by granting the system privilege "SELECT ANY TABLE" and it works fine now, however now I have a second question, does doing that have any significant security implications. Furthermore, does "CREATE ANY TABLE" have any security implications that I should be aware of?
I'd hate to leave myself open to an injection attack or something, and I'm not experienced enough to know if this is a problem or not.
It seems that the problem was in granting the privilege, but I'd say that you chose wrong one.
What you really needed to do was granting SELECT privilege from userB to userA, not SELECT ANY TABLE.
This is an example (not a copy-paste from real situation - I'm just typing it as if it was an editor):
SQL> CONNECT userB/its_password;
SQL> GRANT SELECT ON table_1 TO userA;
SQL> GRANT SELECT ON table_2 TO userA:
SQL> GRANT EXECUTE ON procedure_1 TO userA;
SQL> CONNECT userA/its_password;
SQL> SELECT * FROM userB.table_1;
-- this will work. In order NOT to use 'userB' notation, create a synonym:
SQL> CREATE SYNONYM table_1 FOR userB.table_1;
SQL> CREATE SYNONYM procedure_1 FOR userB.procedure_1;
Is CREATE ANY TABLE potentially dangerous? Well, it depends ... I guess you wouldn't like anyone to create thousands of tables in your schema, would you?
Basically, don't grant unnecessary privileges. When you grant something, find out what it implies. There's online Oracle documentation at Tahiti.