oracle connect as sys remote using ora2sql converter

hi.
I'm using http://www.convert-in.com/ora2sql.htm to connect to a oracle database.
It seems I have successfully configured the connection, however I can't logon.
If I try the user sys, it says that I must use sysdba... however in the helpfile it states
"Does not allow to connect to Oracle database as SYSDBA or SYSOPER "

Can someone give me a few pointers on how I should deal with this?
I have never created a new user in oracle before...
LVL 2
thor918Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Smart_ManCommented:
are you asking about how to create a user or how to connect.
every user have rights like connect , what tables to see. what he can do (granted) on tables .. etc

creating new users will be like
create user ....
and then you grant them rights like
grant ....


waiting for your reply
0
sonicefuCommented:
connect to oracle using any DBA account for example sys as sysdba or system

create user test identified by test;

grant resource to test;

----------------------------

and now use test user to create a connection
0
thor918Author Commented:
connection works, but the user I have password for is not allowed to connect in the ora2mysql program. so the solution seems to create a new user.
I do have the graphical interface "enterprise manager" on the server.

With ora2mysql program, I'm planning to dump to databases to file..
The dumping will have to be done from a workingstation (so no installation on the server.)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

thor918Author Commented:
thanks sonicefu.
what about password for that user?
and how much access is the user granted?
0
sonicefuCommented:
type
sqlplusw sys as sysdba

or

sqlplusw sys@DB_String as sysdba

SQL> create user test identified by test;
 
SQL> grant resource to test;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thor918Author Commented:
correction:
With ora2mysql program, I'm planning to dump 2 databases to file..
0
sonicefuCommented:
have you sys user's password ?

if yes,

type

sqlplusw sys as sysdba

or

sqlplusw sys@DB_String as sysdba

at command prompt

then SQL> prompt will appear then type the following at SQL> prompt one by one

create user test identified by test; --user name and password is test
 
---grant privileges to test user
 
 
grant resource to test;

Open in new window

0
sonicefuCommented:
Lets move step by step
----
Following are the requirements, are you fulfilling all of them?

1) You've installed Oracle client at system where you are working.
2) You've installed Oracle-to-MySQL software.

3) You've the password(s) of one of the following users.

sys
system
or
any other DBA
0
thor918Author Commented:
I do have sys user and the password for that.

I'm working on a workstation. I have installed oracle client. connection works.
I have installed oracle-to-mysql software.

I'm currently looking on the create user info you posted.
0
sonicefuCommented:
execute following command also at
SQL>
prompt

grant dba to test;

Open in new window

0
thor918Author Commented:
I also have an linux-workstation.
So I did install the sqlpus there instead.
I successfully made a connection using the codesnippet.

when you post suggestions, could you perhaps also write what it does?
:) it's several years since I touch oracle :p

./sqlplus username/passeword@server/dbname as sysdba

Open in new window

0
sonicefuCommented:
./sqlplus sys@ipAddress or ServerName:1521/DB_name as sysdba

0
thor918Author Commented:
sorry about the spelling errors ;)

 
0
sonicefuCommented:
replace
dbname with your database name (SID)
ipAddress or ServerName with ip or server Name

and save it into tnsnames.ora file, this file resides in /network/admin folder

orastr=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ipAddress or ServerName)(PORT=1521))
(CONNECT_DATA=
(SID=dbname)))

Open in new window

0
thor918Author Commented:
grant dba to test;

what access rights will this grant?
0
sonicefuCommented:
after pressing enter key, shell will ask you password for sys user
./sqlplus sys@orastr as sysdba

Open in new window

0
sonicefuCommented:
it will make test user a DBA

test can create any type of objects like table, view, procedure etc
test can grant privileges to other users as well

test can do all things relevant to administration
0
thor918Author Commented:
exellent. what if I want to remove this user after use?
0
Smart_ManCommented:
Deletes an IMAGE/SQL user.
Syntax

DEL[ETE] USER { User@Account | User.Account }
Parameters
User@Account             

is the name used to identify the IMAGE/SQL user to SQL. This name, referred to as the DBEUserID, is made up of an MPE/iX user and account name, connected with the period or @ symbol. This user and account must be a valid existing logon.
Prerequisites

    *

      SET SQLDBE issued.
    *

      SET TURBODB issued.
    *

      DBC status.
    *

      DBA authority.
    *

      Database attached.

Description

Use the DELETE USER command to delete an IMAGE/SQL user from a DBEnvironment. When a user is deleted, the DBEUserID (User@Account or User.Account) is removed from the SQL group associated with the TurboIMAGE/XL user class. Note that the group itself and the corresponding view(s) remain in the DBEnvironment because other user-created views may be based on views created by IMAGE/SQL.
Example

In the example below, once the DELETE USER command is issued, user RYAN.ATC can no longer access the SALES database with IMAGE/SQL.

      >>DELETE USER  RYAN.ATC


waiting for your reply
0
thor918Author Commented:
okey.
dabasename = "mydatabase"
backupuser   = "bckuser"

what should I write?:

create user bckuser identified by bckuser; --user bckuser and password is test
---grant privileges to bckuser user
 
grant resource to bckuser;
grant dba to bckuser;
0
Smart_ManCommented:
then when you finish

delete user mydatabase.bckuser

hope it is what you are lookign for.

waiting for your reply
0
thor918Author Commented:
okey. shouldn't I spesify witch database the user have access to.
and are there any way to list the oracle users in sqlpluss?
0
Smart_ManCommented:
yes specify the db name befor ethe username.

yes there is a way to list the users using the sql and not the graphical interface. i really do not recall which table they are stored in.

select * from table; would show all tables. it should be obivoius to notice the system table where the users are stored. just make sure you are loged as sysdba.

i will look for the user info table later but for now try this solution

create user database name.bckuser identified by bckuser; --user bckuser and password is test
---grant privileges to bckuser user
 
grant resource to bckuser;
grant dba to bckuser;

waiting for your reply
0
thor918Author Commented:
okey. tried the first, and the suggestion above.

first I executed (the first command reports that the user is created):
create user bckuser identified by bckuser;
--user bckuser and password is test
--grant privileges to bckuser user
grant resource to bckuser;
grant dba to bckuser;

root@data-tsit-lx:/opt/oracle/instantclient_10_2# ./sqlplus bckuser/test@oradbserver/dbname
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 1 13:30:39 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

here is the other test:
SQL> create user dbname.bckuser identified by bckuser;
create user dbname.bckuser identified by bckuser
                   *
ERROR at line 1:
ORA-01936: cannot specify owner when creating users or roles

0
thor918Author Commented:
okey. I gained access when I set the password for the user in a different way.
are there a typo above? because the set password line does not seem to work.

this one do work:
passw bckuser

0
thor918Author Commented:
okey. the bckuser connectes fine in the "oracle to MYSQL"-tool, however when I come to the select oracle tables, there is non availeble.
I guess there are something missing in the premissions of the user
0
thor918Author Commented:
the user only needs select premissions
0
Smart_ManCommented:
grant select to bckuser;
0
thor918Author Commented:
and this will grant select premissions to what databases?
0
Smart_ManCommented:
as a dba , you have permissions over the db server , including the dbs inside that server.

waiting for your reply
0
sonicefuCommented:
delete user mydatabase.bckuser
Invalid statement

To drop (delete) a user, following is the statement

drop user bckuser;
---------------------------
<<and this will grant select premissions to what databases?>>

grant select to bckuser;   ,----this is not complete statement

actual grant statement is
grant select on tableName to bckuser;
-----------------------------
use the statements to grant privileges to bckuser, as i suggested you in my comments
0
Smart_ManCommented:
as you are loged in as a dba you will be able to see all teh tables so you might grant teh select on the tables you want.
or for all tables like

grant select on (select tablename from tab) to user;

waiting for yoru reply to make sure of the drop/delete
0
thor918Author Commented:
it seems oracle works in a different way than other databasesystems.
(I have probably a little competence on the subject)
http://engineeringnews.tamu.edu/help/wvtwschm.htm
Everything is stored under one user. I didn't get so far to try to get another user to see what's inside another users schema. however I just realised the username/password of that my programs uses to connect to the database. When I did that, the tables shows fine in oracle2mysql-program!

Thanks for all the help.
I have never seen such quick reply's before ;)
0
thor918Author Commented:
it seems oracle works in a different way than other databasesystems.
(I have probably a little competence on the subject)
http://engineeringnews.tamu.edu/help/wvtwschm.htm
Everything is stored under one user. I didn't get so far to try to get another user to see what's inside another users schema. however I just realised the username/password of that my programs uses to connect to the database. When I did that, the tables shows fine in oracle2mysql-program!

Thanks for all the help.
I have never seen such quick reply's before ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.