Solved

Sharing a table in a database

Posted on 2011-09-27
34
300 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)
ID: 36709134
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
ID: 36709186
@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)
ID: 36709226
>>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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:gaugeta
ID: 36709289
@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)
ID: 36709342
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
ID: 36709357
@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)
ID: 36709393
'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
ID: 36709540

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)
ID: 36709852
>>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
ID: 36709972
@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)
ID: 36710184
>>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
ID: 36710276
@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)
ID: 36710396
>> 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
ID: 36710474
@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)
ID: 36711265
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
ID: 36711356
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36711935
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
ID: 36714395
@for_yan:Thanks for the reply.
I will try out these and  get back if i face any issues.
0
 

Author Comment

by:gaugeta
ID: 37127450
@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
ID: 37127593
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
ID: 37128843
@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
ID: 37128848
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
ID: 37129032
@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
ID: 37129055
Do you know the full name of the computer - like hostname.company.com - ?
0
 

Author Comment

by:gaugeta
ID: 37129890
@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
ID: 37129912
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
ID: 37129924

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
ID: 37129927
This was oracle exmple , but Sybase requires some analogous parameters
0
 

Author Comment

by:gaugeta
ID: 37129942
@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
ID: 37129958
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
ID: 37130229
@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
ID: 37130245
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
ID: 37132100
>>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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Deleting Rows from an Oracle Database - Performance 19 50
make null the repeated levels 2 27
mysql jsp example issue 32 38
Create file system directory from Oracle 10g 4 18
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

825 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