Post reply

db link access

  • January 18, 2012 at 12:10 pm

    #391345

    Hi friends,

    We have a linked server from sql server database to Oracle. I want to give read only access to specific tables in Oracle database. Is it possible to do this via Linked server?

    For example, the sql db user should be able to just read(select) the Oracle table via Linked server. Is there a way to do this?

    Thanks al lot

  • January 18, 2012 at 12:18 pm

    #1435442

    yes, but it takes a little bit of configuration on your part: you need at least TWO Oracle users in the Oracle database to accomplish this; one for when YOU connect as a admin, and another that will have read only access in oracle.

    once you know you have those two users, one that has rights appropriate for you, and another that is read only in Oracle, it's just a matter of setting up the linked server:

    the screenshot below is an example, anyone usaing "sa" or myself uses the "POWERUSER" credentials, and everyone else uses the credentials for "LIMITEDUSER"

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 18, 2012 at 2:46 pm

    #1435544

    Thanks a lot.

    Another question, when the users run a select on the Oracle table via linked server from SQL db, will there a lock created in the sql database? The reason I want to know is because the Oracle db users might be doing a DML on the same table simultaneously and I would like to know if there will be a lock issue on the databases that will cause problems in either of the database - when doing a select on the oracle table via linked server while the same table in Oracle is being updated...

    Thanks again

  • January 19, 2012 at 5:20 am

    #1435745

    newbieuser (1/18/2012)

    Thanks a lot.

    Another question, when the users run a select on the Oracle table via linked server from SQL db, will there a lock created in the sql database?

    No, no locks because there is no objects in the SQl database that are being access, SQL cannot create a lock on objects it doesn't own; the command on the Oracle side of things will do something similar to SQL, though...select statments would get the equivilent of a shared lock on the table.

    The reason I want to know is because the Oracle db users might be doing a DML on the same table simultaneously and I would like to know if there will be a lock issue on the databases that will cause problems in either of the database - when doing a select on the oracle table via linked server while the same table in Oracle is being updated...

    Thanks again

    there would be no difference between someone connecting directly to oracle and doing a SELECT, vs somoen coming in via a linked server and doing the same SELECT; the only difference is the underlying tool that is used for the conenction.

    As I understand it, Oracle is a little bit different when it comes to accessing data; a transaction doesn't block users access to the data; they get a copy of the data from before my personal transaciton started, so they don't get stuck waiting for my transaction to complete.

    I believe it goes something like this:

    if I've got a transaction open on the INVOICES table for example that has 5 rows. until I commit my changes, even though i'm adding,updating and deleting the data, and I go to lunch and come back an hour later before commiting that transaction, the other users can freely touch the original snapshot of the data from before i started my transaction; they are not blocked or anything.

    now if someone else deletes a row while i'm at lunch, and I try to update that row, i might get an error, but for updates of the same row, it's the typical last-to-update wins scenario.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 19, 2012 at 7:01 am

    #1435810

    That helps. Thanks a lot Lowell!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply