Help on user Privileges

[mesuj]

All
Have some basic clarification.

I have ORACLE installed and i Connect with one SID say test.I have 4 Table space and 4 users. I login as user1 and get the table space1 by default. But if i say Select from a table which is available in tablespace2 i'm able to get the results.I don't even specify the tablespace name. How is it possible?.

Thanks
mesuj

 

[Georgdba]

Hi -
shure you have granted dba to user1 or the user has privileg on that object
greetings Georg

 

[sem]

Quotas on tablespaces restrict the ability to create objects, not to use already created. You may even insert data into table having no rights on its tablespace. Default tablespace also provides only the DEFAULT value for newly created segments if you omit TABLESPACE clause.

 

[mesuj]

I was trying to port an existing ORACLE to another setup. In this I faced this problem. In the new setup I was not able to see the other tables where as in the old I could see.If I can see the objects in other tablespace then why am not able to see the same in the new environment.I checked the users setup. In the old environment they have the DBA privileges whereas in the new they don't. This is just my observation. Don't know if I'm right.

 

[karluk]

Yes you are absolutely right. The DBA role gives a user a lot of powerful system privileges, including "select any table". That's the reason that the id in the old environment can read the tables belonging to any user. If you want to preserve this capability in the new environment you will have to grant at least "select any table" to the id. Alternatively you can have the other users grant select authority on each individual table, but that's quite messy.