ORA-00942: table or view does not exist error is g

2020-08-01 05:16发布

For my Oracle database I created a user. I want that user to have access to only 3 tables. So I wrote those queries:

grant select on table1 to newuser;
grant select on table2 to newuser;
grant select on table3 to newuser;

And I got this from the console, which ensures that I gave the grant.

GRANT succeeded

However when I connect to database with this user and write the following query, I get ORA-00942 error.

Select * from table1;

I think I need to write additional queries for privileges and roles(I already added CONNECT role). What might it be?

标签: oracle
2条回答
Rolldiameter
2楼-- · 2020-08-01 06:05

Assume that,

  • NEWUSER --> THE user to which a grant has been provided.

  • EXISTINGUSER --> The owner of the table for which a grant is provided.

Login as EXISTINGUSER, and enter following query:

GRANT SELECT ON TABLE1 TO NEWUSER ;

Login as NEWUSER, and select using:

SELECT * FROM EXISTINGUSER.TABLE1;

In case you want to avoid using "EXISTINGUSER"."TABLE1", then you can create a synonym, which is equivalent to a ALIAS NAME:

Login as NEWUSER, enter following query:

CREATE SYNONYM SYN_TABLE1 FOR EXISTINGUSER.TABLE1;

For selecting data from synonym, login as NEWUSER and select using:

SELECT * FROM SYN_TABLE1;

Synonym Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

查看更多
太酷不给撩
3楼-- · 2020-08-01 06:15

Run the query by specifying table owner.

Select * from tableowner.table1;

If this doesn't work then either you have not granted access on correct table or you are logged in with wrong user.

remember same table name can exist in multiple schemas.

查看更多
登录 后发表回答