Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: User default directories using imp
Frank wrote:
> Daniel Morgan wrote:
> > Randy Harris wrote:
> >
> >
> >>"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> >>news:3EAAE701.888DA97A_at_exxesolutions.com...
> >>
> >>>Randy Harris wrote:
> >>>
> >>>
> >>>>"Frank" <fvanbortel_at_netscape.net> wrote in message
> >>>>news:3EAA9DB6.2090402_at_netscape.net...
> >>>>
> >>>>>Randy Harris wrote:
> >>>>>
> >>>>>>Is there any way to
> >>>>>>
> >>>>>>
> >>>>>>>get imp to restore tables to the user's default rather than the
> >>>>>>
> >>source
> >>
> >>>>>>>tablespace?
> >>>>>>>
> >>>>>>
> >>>>>If you knew that beforehand, you could have created
> >>>>>the user with the correct default tablespace, revoke unlimted
> >>>>>tablespace (user gets it with the resource role), and grant
> >>>>>quota on default (and other) tablespaces. Explicitely do
> >>>>>a grant 0 on system.
> >>>>>The import (ignore=y), and the import will use the user's default
> >>>>>tablespace.
> >>>>>
> >>>>>
> >>>>>--
> >>>>>Regards, Frank van Bortel
> >>>>>
> >>>>
> >>>>Can I grant unlimited quota on his default tablespace and 0 on system?
> >>>
> >>And
> >>
> >>>>if so, that would then force his objects to get created in his default?
> >>>
> >>(I
> >>
> >>>>have to do the import again for the production roll out)
> >>>>
> >>>>Thanks for your help
> >>>>--
> >>>>Randy Harris
> >>>
> >>>Yes. And it is a good idea to always grant 0 on the system tablespace to
> >>>everyone except SYS and SYSTEM.
> >>>
> >>>BTW: Does anyone know why it is that when Oracle installs it thinks SYSTEM
> >>
> >>is a
> >>
> >>>temporary tablespace for the SYSTEM schema? This has always struck me as
> >>
> >>quite
> >>
> >>>strange. Oracle builds a rollback segment in the SYSTEM tablespace and
> >>
> >>this I
> >>
> >>>understand. But why not a small temp tablespace to serve the same purpose
> >>
> >>rather
> >>
> >>>than configuring it as it does?
> >>>--
> >>>Daniel Morgan
> >>>http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> >>>(remove one 'x' from my email address to reply)
> >>>
> >>
> >>Please forgive my slowness. How do I "grant 0 on system"? I tried ALTER
> >>USER CM QUOTA 0 ON SYSTEM, but unless I revoke RESOURCE his objects keep
> >>ending up in SYSTEM.
> >
> >
> > Good lord man ... revoke RESOURCE. Oracle advises that the default roles be
> > dropped just as they advise that SYS and SYSTEM have their passwords changed.
> > Only grant that which is required. Nothing more. Create your own roles that have
> > appropriate privileges.
>
>
>
>
>
The roles to remove are CONNECT, RESOURCE, and DBA.
The CONNECT role contains lots of privileges that have nothing to do with connecting to the database. The only privilege required to connect is CREATE SESSION.
My suggestion is to create the following roles and others modeled upon them.
READONLY --- with only create session
DATA_ENTRY -- READONLY plus nothing but INSERT and maybe SELECT and UPDATE into
specific tables
SUPERVISOR
MANAGER
EXECUTIVE
DEVELOPER
SR_DEVELOPER
DBA -- and with only those privileges required for the job. DBA does not need to be a
clone of SYS
-- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp (remove one 'x' from my email address to reply)Received on Mon Apr 28 2003 - 18:45:51 CDT