Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie security question
you cannot grant (or revoke) object privileges on a tablespace. you can specify "quota 0k on tablespace" for a user to prevent the user from creating any objects in the tablespace.
<terry_stjean_at_my-deja.com> wrote in message news:8plgks$k13$1_at_nnrp1.deja.com...
> In article <8plenq$hkj$1_at_nnrp1.deja.com>,
> Mark D Powell <markp7832_at_my-deja.com> wrote:
> > In article <8pl7lt$8rn$1_at_nnrp1.deja.com>,
> > terry_stjean_at_my-deja.com wrote:
> > > 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
> > >
> > 1) Give each userid only the privileges it needs including quota only
> > on its object target tablespaces
> >
> > 2) Do not give out access to the id's to anyone you are not forced to
> > give them to
> >
> > 3) Instead set up a prod user role and a test user role and perform
the
> > necessary grants to the role and give the roles respectively to a prod
> > application user and test application user. Give these out as
> > necessary.
> >
> > Item 3 will separate the object ownership/modification function from
> > the data manipulation function. The prod owner can issue a select
> > privilege to the test user role. Using roles allows the use of a
> > single grant statement to provide all necessary DML privileges to any
> > number of production or test id's. I come from the school of thought
> > where every user should have to logon with their own id and an
> > application should not run as a single database user. Hard coded
> > id/password combinations are security weak points cause they are not
> > that hard to get.
> >
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
> O.k., I found out how to create roles and assign a role to a user. But,
> how do I prevent a user from accessing another tablespace. Do I have to
> grant authority to each table for each user?
>
> >