GAUTAM
asked on
Sharing a table in a database
Hi Experts...
My friend has a database with many tables.My point of intrest is only two tables in his entire database.
How do i share only these two tables from his machine to my machine.
I need those two tables for my web-application.
And i need to convince him that this connection is secure and i have no access to only these two tables.
And what all do i need to do inorder to do this process.
My friend has a sybase database on a server and i dont have any database installed on my machine.
Please help...
My friend has a database with many tables.My point of intrest is only two tables in his entire database.
How do i share only these two tables from his machine to my machine.
I need those two tables for my web-application.
And i need to convince him that this connection is secure and i have no access to only these two tables.
And what all do i need to do inorder to do this process.
My friend has a sybase database on a server and i dont have any database installed on my machine.
Please help...
ASKER
@slightwv:Thanks for the reply.
Which is the best option to have the database and table on my machine or just connecting to his database for a web application.
The reason i added oracle database is because i have oracle databse installed on my machine.
Sorry forgot to add that info.
Please help...
Which is the best option to have the database and table on my machine or just connecting to his database for a web application.
The reason i added oracle database is because i have oracle databse installed on my machine.
Sorry forgot to add that info.
Please help...
>>because i have oracle databse installed on my machine
I was confused by "i dont have any database installed on my machine"
>>Which is the best option
The 'best' option depends on your requirements and how the remote database is used versus how your application is used. For example, do you have different up-time requirements?
If you link to his database and it goes down, your application is down.
If you create a 'copy' of the data locally, you need some mechanism to keep the tables updated. What is your requirement for how up to date the data needs to be?
As you can see, there really isn't a way for us to answer 'best' because we do not know your requirements.
This is further complicated if you create two databases and one is Sybase and the other a different product. You need to make sure whatever does the data migration is compatible with both.
I was confused by "i dont have any database installed on my machine"
>>Which is the best option
The 'best' option depends on your requirements and how the remote database is used versus how your application is used. For example, do you have different up-time requirements?
If you link to his database and it goes down, your application is down.
If you create a 'copy' of the data locally, you need some mechanism to keep the tables updated. What is your requirement for how up to date the data needs to be?
As you can see, there really isn't a way for us to answer 'best' because we do not know your requirements.
This is further complicated if you create two databases and one is Sybase and the other a different product. You need to make sure whatever does the data migration is compatible with both.
ASKER
@slightwv:Thanks for the reply.
Sorry for the confusion caused.
I have a oracle installed on my machine.While i was asking the question wasnt thinking about other machine i had.
We do not have different up-times my web-application works when my friends server is up.
The information in his batabase gets updated on a second to second basis and i need the recently updated version of his table to be accessible.i.e i am supposed to work with the most recently updated table contents.
Please help...
Sorry for the confusion caused.
I have a oracle installed on my machine.While i was asking the question wasnt thinking about other machine i had.
We do not have different up-times my web-application works when my friends server is up.
The information in his batabase gets updated on a second to second basis and i need the recently updated version of his table to be accessible.i.e i am supposed to work with the most recently updated table contents.
Please help...
If you need real-time data then you should connect directly to his database.
I would only look at a local option if absolutely necessary.
I'm not a Sybase person so I cannot comment on the security of the database connection. You need to ask about the level of security needed. For example, do you need the data encrypted over the wire?
I would only look at a local option if absolutely necessary.
I'm not a Sybase person so I cannot comment on the security of the database connection. You need to ask about the level of security needed. For example, do you need the data encrypted over the wire?
ASKER
@slightwv:Thanks for the reply.
I am a newbie to this.
Can you explain in what all ways can i achieve this.
Please help...
I am a newbie to this.
Can you explain in what all ways can i achieve this.
Please help...
'all ways'? There are probably more than I can thing of.
Many depends on the amount of data involved. For example, if there aren't many rows you might be able to get away with disconnected datasets in your web app.
The straight-forward approach is what I posted initially:
I'm an Oracle person but I think Sybase will allow you to create another database user specific for your application. Then just grant the necessary permissions to that user that will only allow them to do what they need.
Your web app will connect to the database using that user and can only perform the actions it is allowed.
Again, speaking only from an Oracle perspective you can take this a step further and mask the tables by creating stored procedures that take specific actions on behalf of the user. This adds even more security since the web app cannot see the base tables. It can only access the procedures.
Many depends on the amount of data involved. For example, if there aren't many rows you might be able to get away with disconnected datasets in your web app.
The straight-forward approach is what I posted initially:
I'm an Oracle person but I think Sybase will allow you to create another database user specific for your application. Then just grant the necessary permissions to that user that will only allow them to do what they need.
Your web app will connect to the database using that user and can only perform the actions it is allowed.
Again, speaking only from an Oracle perspective you can take this a step further and mask the tables by creating stored procedures that take specific actions on behalf of the user. This adds even more security since the web app cannot see the base tables. It can only access the procedures.
ASKER
For example, if there aren't many rows you might be able to get away with disconnected datasets in your web app.-I did not get this point you made.
So from the above can i infer that i can directly connect to ny friends machine from my machine and access his database as one of my own.If so then how do i establish the connection from my machine to my friends machine.
Again, speaking only from an Oracle perspective you can take this a step further and mask the tables by creating stored procedures that take specific actions on behalf of the user-I did not get this point either.
Please help...
>>can i infer that i can directly connect to ny friends machine from my machine and access his database
It is possible if everything is set up and configured properly.
>>If so then how do i establish the connection from my machine to my friends machine
I'm not following. Are you asking how a web application makes a database connection?
>>I did not get this point either.
Create a stored procedure that does what you need. Only grant execute on the procedure to the user your application uses. Then if the application is compromised the hacker will not be able to just select all the data from the database.
For example: You want to display the data in your web app and can bind XML to the display.
An Oracle procedure might look like:
create or replace procedure myProc(outResults out clob)
is
begin
select xmlelement("a",sysdate) into outResults from dual;
end;
/
Then the app user would execute the procedure. The results will be returned and the user never knows what the vase tables are.
It is possible if everything is set up and configured properly.
>>If so then how do i establish the connection from my machine to my friends machine
I'm not following. Are you asking how a web application makes a database connection?
>>I did not get this point either.
Create a stored procedure that does what you need. Only grant execute on the procedure to the user your application uses. Then if the application is compromised the hacker will not be able to just select all the data from the database.
For example: You want to display the data in your web app and can bind XML to the display.
An Oracle procedure might look like:
create or replace procedure myProc(outResults out clob)
is
begin
select xmlelement("a",sysdate) into outResults from dual;
end;
/
Then the app user would execute the procedure. The results will be returned and the user never knows what the vase tables are.
ASKER
@slightwv:Thanks for the reply.
>>If so then how do i establish the connection from my machine to my friends machine
I'm not following. Are you asking how a web application makes a database connection?
I wanted to ask that when we have a database on the same machine i just set the dsn and connect to the database from my application through a thin driver in case of oracle.
Should'nt i do something extra when the database is installed on another machine like linking both machines i a secure path or something.
Secondly i'm not familiar with binding XML to the display.
Sorry but i still did not get how the stored procedure is enforcing confidentiality.
Please help...
>>If so then how do i establish the connection from my machine to my friends machine
I'm not following. Are you asking how a web application makes a database connection?
I wanted to ask that when we have a database on the same machine i just set the dsn and connect to the database from my application through a thin driver in case of oracle.
Should'nt i do something extra when the database is installed on another machine like linking both machines i a secure path or something.
Secondly i'm not familiar with binding XML to the display.
Sorry but i still did not get how the stored procedure is enforcing confidentiality.
Please help...
>>Should'nt i do something extra when the database is installed on another machine
Connecting to a local database versus a remote database really shouldn't matter. A database connection is a database connection.
This is Oracle specific and I would not use the SYS user but check out the docs at:
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/urls.htm#CHDBJIIF
Example 8-1 Using SYS Login To Make a Remote Connection
>>Secondly i'm not familiar with binding XML to the display.
What does your web app need to do?
>>Sorry but i still did not get how the stored procedure is enforcing confidentiality.
Assuming your friend creates a user say, 'bob' and only grants execute on the sample procedure given above.
If your app gets hacked and someone connects to the database as 'bob', what can they see/access?
Connecting to a local database versus a remote database really shouldn't matter. A database connection is a database connection.
This is Oracle specific and I would not use the SYS user but check out the docs at:
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/urls.htm#CHDBJIIF
Example 8-1 Using SYS Login To Make a Remote Connection
>>Secondly i'm not familiar with binding XML to the display.
What does your web app need to do?
>>Sorry but i still did not get how the stored procedure is enforcing confidentiality.
Assuming your friend creates a user say, 'bob' and only grants execute on the sample procedure given above.
If your app gets hacked and someone connects to the database as 'bob', what can they see/access?
ASKER
@slightwv:Thanks for your reply.
>>Secondly i'm not familiar with binding XML to the display.
What does your web app need to do?
Ans:My webapp need to take in say the name of the user and give back their address.Just an example.
If your app gets hacked and someone connects to the database as 'bob', what can they see/access?
They can see a list say user and their address related info.
What is the above procedure doing?
Please help...
>>Secondly i'm not familiar with binding XML to the display.
What does your web app need to do?
Ans:My webapp need to take in say the name of the user and give back their address.Just an example.
If your app gets hacked and someone connects to the database as 'bob', what can they see/access?
They can see a list say user and their address related info.
What is the above procedure doing?
Please help...
>> give back their address.Just an example
There are a lot of examples out there on data binding. Just search around for the ones that fit your needs.
>>They can see a list say user and their address related info
If your procedure accepts an id as input and returns the address then the hacker can only see address information if they know the id's to pass in.
If you just granted 'bob' select on the address table, the hacker could see ALL addresses.
>>What is the above procedure doing?
It is just an example of selecting information from a table and returning it.
>>Please help...
I'm afraid this question is quickly getting out of scope.
My recommendation for the initial question is to create a application user in the remote Sybase database and access the information directly. If there are strict security restrictions, I would really research the stored procedure approach.
There are a lot of examples out there on data binding. Just search around for the ones that fit your needs.
>>They can see a list say user and their address related info
If your procedure accepts an id as input and returns the address then the hacker can only see address information if they know the id's to pass in.
If you just granted 'bob' select on the address table, the hacker could see ALL addresses.
>>What is the above procedure doing?
It is just an example of selecting information from a table and returning it.
>>Please help...
I'm afraid this question is quickly getting out of scope.
My recommendation for the initial question is to create a application user in the remote Sybase database and access the information directly. If there are strict security restrictions, I would really research the stored procedure approach.
ASKER
@slightwv:Could you give a sample java application connecting to a remote oracle or sybase database.
I just want to figure out the differences in syntax for changing my logic to access remote instead of local database.
Please help...
I just want to figure out the differences in syntax for changing my logic to access remote instead of local database.
Please help...
Sorry. I'm not a Java or Sybase person.
Google returned the following but I didn't see a Java Thin example:
http://www.connectionstrings.com/sybase-adaptive
http://www.connectionstrings.com/sybase-advantage
Hopefully a Java/Sybase Expert will be along soon.
Google returned the following but I didn't see a Java Thin example:
http://www.connectionstrings.com/sybase-adaptive
http://www.connectionstrings.com/sybase-advantage
Hopefully a Java/Sybase Expert will be along soon.
This is how you connect to remote Oracle database:
the syntax of this line will be somewaht ddifferent for Sybase:
"jdbc:oracle:thin:@host_na me:1521:SI D
1521 is port - may be different number
the syntax of this line will be somewaht ddifferent for Sybase:
"jdbc:oracle:thin:@host_na
1521 is port - may be different number
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
conn =
DriverManager.getConnection ("jdbc:oracle:thin:@host_name:1521:SID",
user, password);
stmt = conn.createStatement();
addressVector = new Vector();
ResultSet rs0 = stmt.executeQuery("select email from table_name");
while (rs0.next()) {
addressVector.addElement(new InternetAddress(rs0.getString("email")));
}
conn.close();
} catch(Exception ex) {....
}
This is how they give JDBC connection string fro Sybase
in
http://www.java2s.com/Tutorial/Java/0340__Database/AListofJDBCDriversconnectionstringdrivername.htm
in
http://www.java2s.com/Tutorial/Java/0340__Database/AListofJDBCDriversconnectionstringdrivername.htm
Sybase (jConnect 4.2 and earlier)
jdbc:sybase:Tds:<HOST>:<PORT>
com.sybase.jdbc.SybDriver
Sybase (jConnect 5.2)
jdbc:sybase:Tds:<HOST>:<PORT>
com.sybase.jdbc2.jdbc.SybDriver
ASKER
@for_yan:Thanks for the reply.
I will try out these and get back if i face any issues.
I will try out these and get back if i face any issues.
ASKER
@for_yan:Here i tried the same for oracle database and it does not work.
I have no direct connectivity for the database i want to connect as in if i ping the ip address from my machine i get no reply from the cmd prompt.
How do i become a member of the network in which the database i want to acees is residing.
Please help...
I have no direct connectivity for the database i want to connect as in if i ping the ip address from my machine i get no reply from the cmd prompt.
How do i become a member of the network in which the database i want to acees is residing.
Please help...
It is hard to get your point.
Are you saying that you cannot ping the machine on which the database resides?
Yes, you eed network connectivity to that machine before you'll be thinking about connnection to the databses there.
If it is somewhere bind the firewall you need some privilileges to get there.
The only way to do it in this case isa to communicate with your friend
who shoul grant you this abiolity if it is possible.
If they do not allow you to access to the host - there is no way you can access it let alone
connect to their databse.
If I'm misaunderstanding your last statement please, explain in more details.
Are you saying that you cannot ping the machine on which the database resides?
Yes, you eed network connectivity to that machine before you'll be thinking about connnection to the databses there.
If it is somewhere bind the firewall you need some privilileges to get there.
The only way to do it in this case isa to communicate with your friend
who shoul grant you this abiolity if it is possible.
If they do not allow you to access to the host - there is no way you can access it let alone
connect to their databse.
If I'm misaunderstanding your last statement please, explain in more details.
ASKER
@for_yan:Thanks for the reply.
Yes how do i establish network connectivity between my machine and my friend's machine.
He has a normal dynamic ip internet connection.
What are the steps for connecting to that system so that i can interact with the database.
Please help...
Yes how do i establish network connectivity between my machine and my friend's machine.
He has a normal dynamic ip internet connection.
What are the steps for connecting to that system so that i can interact with the database.
Please help...
does not mater dynamic or not dynamic ip address - if you cannot ping that machine by name - then ask your friend
ASKER
@for_yan:Thanks for the reply.
How does one establish connection with another computer remotely.
Can you suggest steps for the same.
Please help...
How does one establish connection with another computer remotely.
Can you suggest steps for the same.
Please help...
Do you know the full name of the computer - like hostname.company.com - ?
ASKER
@for_yan:Thanks for the reply.
I do not have those details as i wanted to approach him after i had all details regarding establishment of the connection.
Usually in genral scenario how will the computer be identified if one wants the connection to be established over internet.
Genrally how the connection take place beween two computers where the host's computer replies when pinged and database too can be accessed.
Please help...
I do not have those details as i wanted to approach him after i had all details regarding establishment of the connection.
Usually in genral scenario how will the computer be identified if one wants the connection to be established over internet.
Genrally how the connection take place beween two computers where the host's computer replies when pinged and database too can be accessed.
Please help...
First you need to be able to acces the host - if it is behind the firewall you'll not be able to do it.
If you can access the host, just pinging by its fully qualified internet name
then you'll need the parameters of database - again hostname, instance, port, user. password - this applies in this way to Oracle, Sybase has soomething similar
But if this host is in corporate environemnt and you are not in the same company it will most probably be behind the firewall
and it is a speacial thing to get access to it, it would be not that straightforward
I think you need first to contact those people who manage it - if you don't have their agreement it makes no sense thinking about it
If you can access the host, just pinging by its fully qualified internet name
then you'll need the parameters of database - again hostname, instance, port, user. password - this applies in this way to Oracle, Sybase has soomething similar
But if this host is in corporate environemnt and you are not in the same company it will most probably be behind the firewall
and it is a speacial thing to get access to it, it would be not that straightforward
I think you need first to contact those people who manage it - if you don't have their agreement it makes no sense thinking about it
I showed you in posting
36711356 above what you need to connect
DriverManager.getConnectio
user, password);
If you have internet access to host_name and you know thosw aparameters of database whioch
are mentioned here - you should be able to access it, but this all of cousrse happens
on top of internet connection.
If there is no such connection, databses parameterts would hardly help you
This was oracle exmple , but Sybase requires some analogous parameters
ASKER
@for_yan:Yes i want to access a database behind a corporate fire wall.
And am trying to convince them so that they can create a new user for the database and allow me to access only a few tables and views.
How practical does this sound.
And by the way wont the host ip address change atleast once a day how do i get the refreshed ip every time i know that within lan this is not a problem.
Can you suggest any other ways in which this connection can be made secure so that its easy for me to convince them.
Please help...
And am trying to convince them so that they can create a new user for the database and allow me to access only a few tables and views.
How practical does this sound.
And by the way wont the host ip address change atleast once a day how do i get the refreshed ip every time i know that within lan this is not a problem.
Can you suggest any other ways in which this connection can be made secure so that its easy for me to convince them.
Please help...
I don't know about ip address change - you don't need ip address to access the host - you can access it by the host name.
It all depends on their wish to give you access.
It is all possible but it requires that they want to do it - it is not a technical but it is organizational question.
If they want to do it - they will give you all the details.
It does not make sense to suggest any ways now without their particiaption - I'm sure there are more than one ways to do it.
What is required is their commitment to do it.
It all depends on their wish to give you access.
It is all possible but it requires that they want to do it - it is not a technical but it is organizational question.
If they want to do it - they will give you all the details.
It does not make sense to suggest any ways now without their particiaption - I'm sure there are more than one ways to do it.
What is required is their commitment to do it.
ASKER
@or_yan:Thanks a lot for the replies.
While trying to establish the same is it possible to only gain access to the two tables of intrest and even though i am inside the firewall now i should not be able to access any other resource.
Or genrally in my requirement on what lines can i try to convince them to share the data and keeping their other resourecs from being accessed.
Or what is the safest or the best approach to convince the company personnel.
Please help...
While trying to establish the same is it possible to only gain access to the two tables of intrest and even though i am inside the firewall now i should not be able to access any other resource.
Or genrally in my requirement on what lines can i try to convince them to share the data and keeping their other resourecs from being accessed.
Or what is the safest or the best approach to convince the company personnel.
Please help...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you need a database on your machine or just a connection to the other database?
You mention Sybase. This was posted in an Oracle zone. Is it Oracle related?