Re: Newbie security question

Not possible as requested.

You can prevent anyone from *creating* objects in a tablespace by making sure they have 'quota 0 on xxxx' (assuming they haven't been inadvertently been granted 'unlimited tablespace' rights by virtue of granting 'resource' to public or directly to themselves -an old Oracle 7 habit).

But you can't prevent them selecting from or performing DML to objects created within a tablespace unless you carefully refrain from granting them such privileges on the specific *objects* created in the other tablespace -and them's object-level privileges, not tablespace level ones.

In other words, if EMP is in tablespace DATA1, and you grant me 'insert on EMP' rights, the fact that you earlier specified I should have 'quota 0 on DATA1' won't stop me inserting into the EMP table, because the EMP table is *your* object, and its size comes, therefore, out of YOUR quota on that tablespace. If my insert makes EMP bigger, that's a deduction from your quota, not mine.

There are NO privileges that prevent you doing things at tablespace level (except for the quota rights, which only affect object creation and extension).

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



<terry_stjean_at_my-deja.com> wrote in message
news:8pl7lt$8rn$1_at_nnrp1.deja.com...


> We have 2 tablespaces, dev and prod (development and production). We

> have 2 users set up, devuser and produser. Devuser should have read

> access to tablespace prod as well as all data and object access to

> tablespace dev. Produser should have no access to tablespace dev and

> only data access to tablespace prod.

> What do I need to do to set this up this way.

>

> Terry

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.