OraFAQ Forum: Security » how to make a user able to use tables in diferent table spaces

Home » RDBMS Server » Security » how to make a user able to use tables in diferent table spaces

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

how to make a user able to use tables in diferent table spaces [message #264589]

Mon, 03 September 2007 20:58

grupoapunte
Messages: 5
Registered: September 2007

Junior Member

Hi, i have a big issue, I have 2 users each user can access (make querys) to diferent tables of the same data base, the problem is that i dont want this to be this way, i need a user that can access all the tables in the data base so i can use it from my aplication, the tables are in diferent table spaces (i hope this is not the problem) to setup my DB i used this script (i didnt write this script):

CREATE TABLESPACE "SID_DATA" 
LOGGING 
DATAFILE 'C:\ORACLE\ORADATA\SID_DATA_02.ORA' SIZE 256M 
REUSE AUTOEXTEND 
ON NEXT  256M MAXSIZE  4096M, 
'C:\ORACLE\ORADATA\SID_DATA_03.ORA' SIZE 256M REUSE 
AUTOEXTEND 
ON NEXT  256M MAXSIZE  4096M, 
'C:\ORACLE\ORADATA\SID_DATA_04.ORA' SIZE 256M REUSE 
AUTOEXTEND 
ON NEXT  128M MAXSIZE  4096M, 
'C:\ORACLE\ORADATA\SID_DATA.ORA' SIZE 256M REUSE 
AUTOEXTEND 
ON NEXT  256M MAXSIZE  1024M EXTENT MANAGEMENT LOCAL SEGMENT 
SPACE MANAGEMENT  AUTO 


CREATE TABLESPACE "SID_IDX" 
LOGGING 
DATAFILE 'C:\ORACLE\ORADATA\SID_IDX.ORA' SIZE 128M REUSE 
AUTOEXTEND 
ON NEXT  128M MAXSIZE  4096M, 
'C:\ORACLE\ORADATA\SID_IDX_.02.ORA' SIZE 128M REUSE 
AUTOEXTEND 
ON NEXT  128M MAXSIZE  4096M, 
'C:\ORACLE\ORADATA\SID_IDX_.03.ORA' SIZE 128M REUSE 
AUTOEXTEND 
ON NEXT  128M MAXSIZE  4096M, 
'C:\ORACLE\ORADATA\SID_IDX_.04.ORA' SIZE 128M REUSE 
AUTOEXTEND 
ON NEXT  128M MAXSIZE  4096M EXTENT MANAGEMENT LOCAL SEGMENT 
SPACE MANAGEMENT  AUTO 

CREATE USER "SIPRE"  PROFILE "DEFAULT" 
IDENTIFIED BY "SIPRE_PASSWORD" 
DEFAULT TABLESPACE "SID_DATA"
TEMPORARY TABLESPACE "TEMP" 
QUOTA UNLIMITED 
ON "SID_DATA" 
QUOTA UNLIMITED 
ON "SID_IDX" 
QUOTA 524288 K 
ON "TEMP" 
ACCOUNT UNLOCK

CREATE USER "SIDEMELARI"  PROFILE "DEFAULT" 
IDENTIFIED BY "SIDEMELARI_PASSWORD" 
DEFAULT TABLESPACE "SID_DATA"
TEMPORARY TABLESPACE "TEMP" 
QUOTA UNLIMITED 
ON "SID_DATA" 
QUOTA UNLIMITED 
ON "SID_IDX" 
QUOTA 524288 K 
ON "TEMP" 
ACCOUNT UNLOCK

And after i used this to import the data base:

IMP system/dam369852@bdglobal file=C:\Oracle\oradata\BDGLOBAL\sipre.dmp fromuser=sipre touser=sipre
IMP system/dam369852@bdglobal file=C:\Oracle\oradata\BDGLOBAL\sidemelari.dmp fromuser=sidemelari touser=sidemelari

This is what happends, I make a query using one of the users:

select * from amareas
              *
ERROR at line 1:
ORA-00942: table or view does not exist

If i do the same query with the second user it works fine (for that table). It is supposed that i should be able to use only one user to access all the tables.

Thanks

[Updated on: Mon, 03 September 2007 21:03]

Report message to a moderator

Re: how to make a user able to use tables in diferent table spaces [message #264591 is a reply to message #264589]

Mon, 03 September 2007 21:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have 2 users each user can access (make querys) to diferent tables of the same data base, the problem is that i dont want this to be this way

I suspect that you have not ever read the Concept Manual found at http://tahiti.oracle.com

Oracle tablespaces have NOTHING to do with who can access which objects!

The ONLY way sipre can access objects owned by sidemelari is if GRANTs were issued to allow access & vice versa for objects owned by sidemelari being accessed by sipre.

So, your solution is to REVOKE the previously issued GRANTs as needed.

Report message to a moderator

Re: how to make a user able to use tables in diferent table spaces [message #264602 is a reply to message #264589]

Mon, 03 September 2007 22:35

grupoapunte
Messages: 5
Registered: September 2007

Junior Member

is not that i dont want to learn oracle! i just dont have time to do it, im making an aplication and my partner is making all the oracle stuff but hes gone for the week and i needed a bit of good help. I dont undestand how to solve the issue

Report message to a moderator

Re: how to make a user able to use tables in diferent table spaces [message #264604 is a reply to message #264589]

Mon, 03 September 2007 22:41

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Quote:

This is what happends, I make a query using one of the users:

select * from amareas
*
ERROR at line 1:
ORA-00942: table or view does not exist

I think you have either out of data in dump file .

As your partner take dump he know well what is inside it.

Or ,

use select * from [Schema_name]. [Table_name]

Must have grant select permission on the specified table as Ana mentioned.

Report message to a moderator

Re: how to make a user able to use tables in diferent table spaces [message #264605 is a reply to message #264589]

Mon, 03 September 2007 22:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> i just dont have time to do it,
I just don't have time for somebody who expects to be spoonfed answers.
If a timely solution is really, really, really IMPORTANT, then hire a consultant & pay for the fix for the mess that now exists.

Report message to a moderator

Re: how to make a user able to use tables in diferent table spaces [message #264768 is a reply to message #264589]

Tue, 04 September 2007 09:11

grupoapunte
Messages: 5
Registered: September 2007

Junior Member

omg! whats your problem dude! the reason why forums exists is to help eachother, im not telling anyone to answwer me, im asking, and by saying i dont have time i meant that i cant be stuying oracle to fix this problem because i have a lot of work to do, and usualy when that happends ppl uses a forum.

END OF THE CONVERSATION.

---------------------------------------------------

I found a way to solve this issue by using synonyms like this:

create synonym TABLA_NAME for SIPRE.TABLA_NAME;
grant select [, insert] [, update] [, delete] on TABLA_NAME to USER_NAME;

Report message to a moderator

Re: how to make a user able to use tables in diferent table spaces [message #264784 is a reply to message #264768]

Tue, 04 September 2007 10:26

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

First you have to read OraFAQ Forum Guide
It is said:
* Be polite
* First of all: did I try myself? Or am I just hoping that someone else is willing to do my work?
* Did I read the documentation? A lot of questions are answered there. Really.
* Have I checked the FAQ page?
* Did I search the board properly? Have I also tried the Site Search Engine?
* Did I use Google?
* ...

Obviously, you don't and this may be the

END OF THE HELP

----------------------------------------------------------------

Regards
Michel

Report message to a moderator

Previous Topic:	ORA-01950..........
Next Topic:	OLS query problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 07:35:31 CST 2024