Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sysdba role in script

Re: sysdba role in script

From: <bofh1234_at_hotmail.com>
Date: 7 Feb 2007 11:33:55 -0800
Message-ID: <1170876835.636941.249480@s48g2000cws.googlegroups.com>


On Feb 7, 9:11 am, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> On Feb 6, 10:35 pm, bofh1..._at_hotmail.com wrote:
> > I am writing a script. This script will create a tablespace, a user,
> > switch that user, create more tablespaces, etc. Thanks to some
> > friendly people I have a mostly working script. Mostly working
> > because when I switch to the user in the script the sysdba privledges
> > are not dropped.
> > This does not work
> > connect sys as sysdba
> > create tablespace test datafile '/u03/oradata/prod/test01.dbf size
> > 200M;
> > create user harry identified by password default tablespace test
> > temporary tablespace temp quota 24M on test;
> > grant connect, resource, sysdba to harry;
> > conn harry/potter
> > create tablespace data2 datafile '/u03/oradata/prod/data201.dbf' SIZE
> > 300M;
>
> > The data2 tablespace isn't created in the right area.
>
> > This works:
> > connect sys as sysdba
> > create tablespace test datafile '/u03/oradata/prod/test01.dbf size
> > 200M;
> > create user harry identified by password default tablespace test
> > temporary tablespace temp quota 24M on test;
> > grant connect, resource, sysdba to harry;
> > exit sqlplus and launch sqlplus again
> > connect harry/potter
> > create tablespace data2 datafile '/u03/oradata/prod/data201.dbf' SIZE
> > 300M;
>
> > The data2 tablespace is created correctly.
>
> > What am I doing wrong? I am using Oracle 10.2.0.1
>
> > Thanks,
>
> I am confused by you statement that the tablespace is not created in
> the right location but you do not display any error or explain where
> it was created. Why do you need to switch users when any DBA can
> create or alter a tablespace. What is the point of switching?
>
> Why do you grant sysdba then not connect as sysdba though any user
> connected as sysdba is user SYS rather than themselves?
>
> > connect / as sysdba
> Connected.
> > show user
> USER is "SYS"
> > connect mpowel01 as sysdba
> Enter password:
> Connected.
> > show user
> USER is "SYS"
>
> Oracle has stated that Connect and Resource are obsolete roles and are
> provided for backward compatiability only. You should not be using
> them.
>
> Maybe someone else will understand your problem but I think you need
> to explain it more clearly and include query results on
> dba_tablespaces and/or dba_data_files to show what is happening. That
> or list the error messages.
>
> HTH -- Mark D Powell --

This is what happens when I run the script: login in sys as sysdba
Tablespace created.
User created.
Grant succeeded.
Connected.
DROP TABLESPACE STORE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-01031: insufficient privileges

CREATE TABLESPACE STORE DATAFILE '/u03/oradata/prod/store01.dbf' SIZE 3M
*
ERROR at line 1:
ORA-01031: insufficient privileges

Table created.

If I create the user, exit sqlplus, login as harry the script works without error.

Thanks, Received on Wed Feb 07 2007 - 13:33:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US