oracle connect as sys remote using ora2sql converter

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

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

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.)

thanks sonicefu.
what about password for that user?
and how much access is the user granted?

correction:
With ora2mysql program, I'm planning to dump 2 databases to file..

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;

Select allOpen in new window

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

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.

execute following command also at
SQL>
prompt

grant dba to test;

Select allOpen in new window

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

Select allOpen in new window

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

sorry about the spelling errors ;)

 

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)))

Select allOpen in new window

grant dba to test;

what access rights will this grant?

after pressing enter key, shell will ask you password for sys user

./sqlplus sys@orastr as sysdba

Select allOpen in new window

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

exellent. what if I want to remove this user after use?

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;

then when you finish

delete user mydatabase.bckuser

hope it is what you are lookign for.

waiting for your reply

okey. shouldn't I spesify witch database the user have access to.
and are there any way to list the oracle users in sqlpluss?

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

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

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

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

the user only needs select premissions

grant select to bckuser;

and this will grant select premissions to what databases?

as a dba , you have permissions over the db server , including the dbs inside that server.

waiting for your reply

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

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

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 ;)

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 ;)