ORA-28009

#1

Hi,
I am running a shell script which call a perl script which uses DBI to
connect to a remote (ie another server) which creates schemas ie table
spaces, users, tables in the waiting DB.

Or rather it does not:
Database Connection Error: ORA-28009: connection as SYS should be as
SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin)
The problem is as follows:
../create_stuff.sh <database type> <database name> <host> <username>
<password> <port>

I ran one of the many scripts locally on the unix command line with
the hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK.

The problem is that the shell script, calls more shell scripts, calls
perl scripts and passing in "sys" as a parameter fails at the Oracle
10G level cos we need the AS sysdba.
And if I call the script with "sys as sysdba" "bloggs" (or similar
tried a few things) it goes wrong big time.

Possibly the answer is to create a user that has the powers of "sys as
sysdba" or something similar. But I am stuck really...

can anyone help plz?

Syd

#2

Create another user and create tables so they are owned by this user.

create user ABC identified by ABC
default tablespace USERS
temporary tablespace TEMP;

grant CONNECT,RESOURCE to ABC;

After that connect as ABC/ABC and create tables, indexes, etc.

#3

On 14 Feb, 23:30, vsevolod afanassiev <vsevolod.afanass... (AT) gmail (DOT) com>
wrote:

Quote:

Create another user and create tables so they are owned by this user.

create user ABC identified by ABC
default tablespace USERS
temporary tablespace TEMP;

grant CONNECT,RESOURCE to ABC;

After that connect as ABC/ABC and create tables, indexes, etc.

Thanks - but I need to run the scripts as they are - and the scripts
create tablespaces and users.

#4

Op 14-2-2010 23:45, syd_p schreef:

Quote:

Hi,
I am running a shell script which call a perl script which uses DBI to
connect to a remote (ie another server) which creates schemas ie table
spaces, users, tables in the waiting DB.

Or rather it does not:
Database Connection Error: ORA-28009: connection as SYS should be as
SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin)
The problem is as follows:
./create_stuff.sh<database type> <database name> <host> <username
password> <port

I ran one of the many scripts locally on the unix command line with
the hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK.

The problem is that the shell script, calls more shell scripts, calls
perl scripts and passing in "sys" as a parameter fails at the Oracle
10G level cos we need the AS sysdba.
And if I call the script with "sys as sysdba" "bloggs" (or similar
tried a few things) it goes wrong big time.

Possibly the answer is to create a user that has the powers of "sys as
sysdba" or something similar. But I am stuck really...

can anyone help plz?

Syd

Try this: username: "sys", database name "DBNAME as sysdba"

Shakespeare

#5

On Sun, 14 Feb 2010 14:45:45 -0800, syd_p wrote:

Quote:

Hi,
I am running a shell script which call a perl script which uses DBI to
connect to a remote (ie another server) which creates schemas ie table
spaces, users, tables in the waiting DB.

Or rather it does not:
Database Connection Error: ORA-28009: connection as SYS should be as
SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin) The problem is as
follows:
./create_stuff.sh <database type> <database name> <host> <username
password> <port

I ran one of the many scripts locally on the unix command line with the
hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK.

The problem is that the shell script, calls more shell scripts, calls
perl scripts and passing in "sys" as a parameter fails at the Oracle 10G
level cos we need the AS sysdba.
And if I call the script with "sys as sysdba" "bloggs" (or similar
tried a few things) it goes wrong big time.

Possibly the answer is to create a user that has the powers of "sys as
sysdba" or something similar. But I am stuck really...

can anyone help plz?

Syd

This is actually a wrong group for DBI questions but nevertheless, the
answer is using ora_session mode. Here is an example:

$dsn = "dbi:Oracle:"; # no dbname here
$ENV{ORACLE_SID} = "orcl"; # set ORACLE_SID as needed
delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set

$dbh = DBI->connect($dsn, "", "", { ora_session_mode =>
ORA_SYSDBA});

It has been reported that this only works if $dsn does not
contain a SID so that Oracle then uses the value of the ORACLE_SID (not
TWO_TASK) environment variable to connect to a local instance.
Also the username and password should be empty, and the user
executing the script needs to be part of the dba group or osdba group.

Having said all that, it is a bad idea to write Perl scripts running as
SYSDBA.

--
http://mgogala.freehostia.com

#6

On Mon, 15 Feb 2010 13:45:27 +0100, Shakespeare wrote:

Quote:

Try this: username: "sys", database name "DBNAME as sysdba"

Shakespeare

Will not work.

--
http://mgogala.freehostia.com

#7

On Feb 14, 2:45*pm, syd_p <sydneypue... (AT) yahoo (DOT) com> wrote:

Quote:

Hi,
I am running a shell script which call a perl script which uses DBI to
connect to a remote (ie another server) which creates schemas ie table
spaces, users, tables in the waiting DB.

Or rather it does not:
Database Connection Error: ORA-28009: connection as SYS should be as
SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin)
The problem is as follows:
./create_stuff.sh <database type> <database name> <host> <username
password> <port

I *ran one of the many scripts locally on the unix command line with
the hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK.

The problem is that the shell script, calls more shell scripts, calls
perl scripts and passing in "sys" as a parameter fails at the Oracle
10G level cos we need the AS sysdba.
And if I call the script with "sys as sysdba" *"bloggs" (or similar
tried a few things) it goes wrong big time.

Possibly the answer is to create a user that has the powers of "sys as
sysdba" or something similar. But I am stuck really...

can anyone help plz?

Syd

Have you set things up so someone can connect remotely as sys there?
Connecting locally merely means the local user has group rights.
http://www.orafaq.com/wiki/Oracle_da...password_files

jg
--
@home.com is bogus.
http://news.techworld.com/security/3...ntcmp=nws-hm-l

#8

On Mon, 15 Feb 2010 11:33:40 -0800, joel garry wrote:

Quote:

Have you set things up so someone can connect remotely as sys there?
Connecting locally merely means the local user has group rights.

The OP has asked the same question on the DBI group and Jared Still, of
the "Perl for an Oracle DBA" fame, has given him the same answer I did..

--
http://mgogala.byethost5.com

#9

On 15 Feb, 23:07, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:

Quote:

On Mon, 15 Feb 2010 11:33:40 -0800, joel garry wrote:
Have you set things up so someone can connect remotely as sys there?
Connecting locally merely means the local user has group rights.

The OP has asked the same question on the DBI group and Jared Still, of
the "Perl for an Oracle DBA" fame, has given him the same answer I did..

--http://mgogala.byethost5.com

Hi Guys,
I am the OP and actually I have only asked the question on this group.
I kind of thought the answer lies in the Oracle world, hence my
posting in this group.
I guess I did not phrase the question very well.
The problem is that I have a selection of scripts that dont like "sys
as sysdba" as a username.
Dont know why and I dont want to mess with the scripts.
What I need is a username of one word (not three that makes the
scripts barf)
that has the powers to create tablespaces and users in a database that
I created with dbca.

Maybe that is possible? If so please help me.
Thanks for your input so far!

Syd

#10

Shakespeare <whatsin (AT) xs4all (DOT) nl> writes:

Quote:

Op 14-2-2010 23:45, syd_p schreef:
Hi,
I am running a shell script which call a perl script which uses DBI to
connect to a remote (ie another server) which creates schemas ie table
spaces, users, tables in the waiting DB.

Or rather it does not:
Database Connection Error: ORA-28009: connection as SYS should be as
SYSDBA or SYSOPER (DBD ERROR: OCISessionBegin)
The problem is as follows:
./create_stuff.sh<database type> <database name> <host> <username
password> <port

I ran one of the many scripts locally on the unix command line with
the hardcoded string "sqlplus sys as sysdba/bloggs" and that works OK.

The problem is that the shell script, calls more shell scripts, calls
perl scripts and passing in "sys" as a parameter fails at the Oracle
10G level cos we need the AS sysdba.
And if I call the script with "sys as sysdba" "bloggs" (or similar
tried a few things) it goes wrong big time.

Possibly the answer is to create a user that has the powers of "sys as
sysdba" or something similar. But I am stuck really...

can anyone help plz?

Syd

Try this: username: "sys", database name "DBNAME as sysdba"

If that doesn't work, my suggestion would be to look at the perl
documentations for the DBD::Oracle driver. This has a lot of useful
information and examples and will likely give better results than random
shots in the dark. As the driver is regularly being updated and as its
not uncommon to find multiple versions of perl on one system, its also
important to check driver versions and ensure the docs you are looking
at match the driver being used.

Doing a grep for sysdba for the DBD::Oracle driver on my system gives
the following (as well as other matches - this is just an example).

Connect Attributes
ora_session_mode
The ora_session_mode attribute can be used to connect with SYSDBA
authorization and SYSOPER authorization. The ORA_SYSDBA and
ORA_SYSOPER constants can be imported using

use DBD::Oracle qw(

ra_session_modes);

This is one case where setting ORACLE_SID may be useful since
connecting as SYSDBA or SYSOPER via SQL*Net is frequently disabled
for security reasons.

HTH

Tim

--
tcross (at) rapttech dot com dot au