CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
mzurita
mzurita

Reputation: 690

ORA-00942: Table or View not exist connecting with another user

in Oracle SQL developer I got error ORA-00942: Table or View not exist connecting with another user when I do the following:

CREATE USER marta IDENTIFIED BY 'marta';
GRANT SELECT, INSERT ON myTable TO marta;

so then, executing:

CONNECT marta/marta;
INSERT INTO myTable VALUES ('1', 'foo', bar');

got the ORA-00942...

Obviusly, If I use system user I can insert row with no issues.

I searched other answers but I couldnt solve this... what is wrong

Upvotes: 1

Views: 1826

Answers (1)

APC
APC

Reputation: 146249

Obviusly, If I use system user I can insert row with no issues.

Uh-oh. There's nothing obvious about that. The SYSTEM user should not own a table called MY_TABLE (or whatever application table that is actually named). The SYSTEM user is part of the Oracle database, its schema is governed by Oracle and using it for our own application objects is really bad practice.

But it seems you have created a table in that schema and user MARTA can't see it. That's standard. By default users can only see their own objects. They can only see objects in other schemas if the object's owner (or a power user) grants privileges on that object to the other user.

So, as SYSTEM

grant select on my_table to marta;

Then, as MARTA

select * from system.my_table;

To avoid prefixing the owning schema MARTA can create a synonym:

create or replace synonym my_table for system.my_table;

select * from my_table;

But really, you need to stop using SYSTEM for your own tables.

Upvotes: 6

Related Questions