OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » table management

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table management [message #183507]

Fri, 21 July 2006 03:35

limbe
Messages: 20
Registered: July 2006
Location: Limbe, Cameroon

Junior Member

Hello!

I have a problem using tale on my oracle 10g database,
I created table using user USER1
when I connect to databse with USER1 I can access the tables, but if I connect with USER2 i can not access the table. Error message is
ORA-00942 Table or view does not exist

Hugues

Report message to a moderator

Re: table management [message #183519 is a reply to message #183507]

Fri, 21 July 2006 03:51

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

By default, USER2 has no access to tables in any other schema than its own.

Connect as USER1 in SQL*Plus

GRANT SELECT ON <table> TO USER2;

USER2 can now see the table as USER1.<table>

Report message to a moderator

Re: table management [message #183545 is a reply to message #183519]

Fri, 21 July 2006 05:04

limbe
Messages: 20
Registered: July 2006
Location: Limbe, Cameroon

Junior Member

Hello!

Thanks Very Much, this has solved my problem.

But i am building an application with WINDEV as client, I created all the table on shema USERDEV,
How can I grant priveleges to all users whoe will be using the application to the objects in this schema.

Thanks

Hugues

Report message to a moderator

Re: table management [message #183549 is a reply to message #183507]

Fri, 21 July 2006 05:11

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

Use a system grant.
As sysdba:
grant connect to <your user>;
grant select any table to <your user>;

Or if you need object privs then as the table owner:
create public synonym <table_name> for <your user>.<table_name>;
grant select, update, insert, delete on <synonym> to <your user>;

Report message to a moderator

Re: table management [message #183556 is a reply to message #183549]

Fri, 21 July 2006 05:48

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Well, you could grant SELECT ANY TABLE, as long as there was no data in the system that you wanted to remain confidential.
It does leave you fairly wide open from a ecurity point of view - if anyone can see DBA_USERS, then they can get the encrypted passwords and perform a brute force decryption attack.

I reckon you'd be better off creating a ROLE, granting this role to all your users, and then granting SELECT on these tables to that role.

Report message to a moderator

Re: table management [message #183644 is a reply to message #183556]

Fri, 21 July 2006 12:58

limbe
Messages: 20
Registered: July 2006
Location: Limbe, Cameroon

Junior Member

hello,

Thanks for your replies,

I have understood that i should refer to other users table by OTHERUSER.TABLENAME, this works perfectly.
But now is there a way to access other user without the otheruser reference say by typing straigh TABLENAME.

Thanks

Hugues

Report message to a moderator