Solved

Sharing a table in a database

Posted on 2011-09-27
34
298 Views
Last Modified: 2012-05-12
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...
0
Comment
Question by:gaugeta
  • 14
  • 10
  • 9
34 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Your friend needs to create a user in his database and only grant you the necessary privileges to those tables.

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?
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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?
0
 

Author Comment

by:gaugeta
Comment Utility
@slightwv:Thanks for the reply.
I am a newbie to this.
Can you explain in what all ways can i achieve this.
Please help...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
'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.
0
 

Author Comment

by:gaugeta
Comment Utility

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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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?
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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.
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.


0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
This is how you connect to remote Oracle database:
the syntax of this line will be somewaht ddifferent for Sybase:
"jdbc:oracle:thin:@host_name:1521:SID

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

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 47

Expert Comment

by:for_yan
Comment Utility
This is how they give JDBC connection string fro Sybase
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

Open in new window

0
 

Author Comment

by:gaugeta
Comment Utility
@for_yan:Thanks for the reply.
I will try out these and  get back if i face any issues.
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
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.
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
does not mater dynamic or not dynamic ip address - if you cannot ping that machine by name - then ask your friend
0
 

Author Comment

by:gaugeta
Comment Utility
@for_yan:Thanks for the reply.
How does one establish connection with another computer remotely.
Can you suggest steps for the same.
Please help...
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
Do you know the full name of the computer - like hostname.company.com - ?
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
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
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility

I showed you in posting
36711356 above what you need to connect

  DriverManager.getConnection ("jdbc:oracle:thin:@host_name:1521:SID",
             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
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
This was oracle exmple , but Sybase requires some analogous parameters
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 47

Expert Comment

by:for_yan
Comment Utility
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.
0
 

Author Comment

by:gaugeta
Comment Utility
@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...
0
 
LVL 47

Accepted Solution

by:
for_yan earned 250 total points
Comment Utility
No doubt technically it is possible to give you access access to only two tables
They can also limit your access to any other resources.
I cannot give you advice though how to convince themto give you access.
They certainly should be somehow interested to do it.
What interest they may have - only they and you can decide.
How can I help you with that?

0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
>>Yes i want to access a database behind a corporate fire wall.

I think you are fighting a losing battle.  I do not know anyone that will expose their database to the 'Internet'.

You will need to VPN in or likely access the database through a web service or other middleware application.

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now