?
Solved

oracle connect as sys remote using ora2sql converter

Posted on 2008-02-01
34
Medium Priority
?
638 Views
Last Modified: 2013-12-19
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...
0
Comment
Question by:thor918
  • 17
  • 10
  • 7
34 Comments
 
LVL 10

Expert Comment

by:Smart_Man
ID: 20795013
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795034
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
 
LVL 2

Author Comment

by:thor918
ID: 20795048
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 2

Author Comment

by:thor918
ID: 20795054
thanks sonicefu.
what about password for that user?
and how much access is the user granted?
0
 
LVL 13

Accepted Solution

by:
sonicefu earned 1000 total points
ID: 20795060
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
 
LVL 2

Author Comment

by:thor918
ID: 20795063
correction:
With ora2mysql program, I'm planning to dump 2 databases to file..
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795079
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795118
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
 
LVL 2

Author Comment

by:thor918
ID: 20795151
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795171
execute following command also at
SQL>
prompt

grant dba to test;

Open in new window

0
 
LVL 2

Author Comment

by:thor918
ID: 20795229
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
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795259
./sqlplus sys@ipAddress or ServerName:1521/DB_name as sysdba

0
 
LVL 2

Author Comment

by:thor918
ID: 20795270
sorry about the spelling errors ;)

 
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795276
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
 
LVL 2

Author Comment

by:thor918
ID: 20795283
grant dba to test;

what access rights will this grant?
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795291
after pressing enter key, shell will ask you password for sys user
./sqlplus sys@orastr as sysdba

Open in new window

0
 
LVL 13

Expert Comment

by:sonicefu
ID: 20795305
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
 
LVL 2

Author Comment

by:thor918
ID: 20795666
exellent. what if I want to remove this user after use?
0
 
LVL 10

Assisted Solution

by:Smart_Man
Smart_Man earned 1000 total points
ID: 20795710
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
 
LVL 2

Author Comment

by:thor918
ID: 20795756
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
 
LVL 10

Expert Comment

by:Smart_Man
ID: 20795798
then when you finish

delete user mydatabase.bckuser

hope it is what you are lookign for.

waiting for your reply
0
 
LVL 2

Author Comment

by:thor918
ID: 20795913
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
 
LVL 10

Expert Comment

by:Smart_Man
ID: 20795976
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
 
LVL 2

Author Comment

by:thor918
ID: 20796035
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
 
LVL 2

Author Comment

by:thor918
ID: 20796107
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
 
LVL 2

Author Comment

by:thor918
ID: 20796149
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
 
LVL 2

Author Comment

by:thor918
ID: 20796178
the user only needs select premissions
0
 
LVL 10

Expert Comment

by:Smart_Man
ID: 20796217
grant select to bckuser;
0
 
LVL 2

Author Comment

by:thor918
ID: 20796365
and this will grant select premissions to what databases?
0
 
LVL 10

Expert Comment

by:Smart_Man
ID: 20796552
as a dba , you have permissions over the db server , including the dbs inside that server.

waiting for your reply
0
 
LVL 13

Expert Comment

by:sonicefu
ID: 20798255
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
 
LVL 10

Expert Comment

by:Smart_Man
ID: 20800330
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
 
LVL 2

Author Closing Comment

by:thor918
ID: 31427041
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
 
LVL 2

Author Comment

by:thor918
ID: 20812607
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question