Read-only DBLink !

[vikas000000a]

Hi all,
I have got a problem regarding Oracle DB Links:

I have two physically seperate database servers with Oracle 10g installed on both of them. Both the servers are by used by us here within city. However, one of them is being used by other users also of our company in the span of entire country (India) with all the read/write permissions. And the other server is used exclusively by my office.

Now, due to a certain requirement, we need to give read-only access to our database (till now, being used exclusively by us) to the users of my company in entire country. I have researched over the problem and found that DB Link is generally the answer to such a problem. But my problem is that DB Link automatically gives the write access also to the users thus defeating our purpose.. I dont seem to find any other way around.

Anticipating a quick response....

[amitpatel66]

Hi all,
I have got a problem regarding Oracle DB Links:

I have two physically seperate database servers with Oracle 10g installed on both of them. Both the servers are by used by us here within city. However, one of them is being used by other users also of our company in the span of entire country (India) with all the read/write permissions. And the other server is used exclusively by my office.

Now, due to a certain requirement, we need to give read-only access to our database (till now, being used exclusively by us) to the users of my company in entire country. I have researched over the problem and found that DB Link is generally the answer to such a problem. But my problem is that DB Link automatically gives the write access also to the users thus defeating our purpose.. I dont seem to find any other way around.

Anticipating a quick response....

Try using ROLES for the purpose

CREATE ROLE readonly;
GRANT SELECT ON myschema.table1 TO readonly;
GRANT SELECT ON myschema.table2 TO readonly;
GRANT SELECT ON myschema.table3 TO readonly;
GRANT readonly TO myenduser;

Assign this role to all the users!!

[debasisdas]

1. Create read only views on the desired tables
2. Grant select access to another user.
3. create DBLINK for that user .

[vikas000000a]

1. Create read only views on the desired tables
2. Grant select access to another user.
3. create DBLINK for that user .

Hi Deba
Your solution seems to be OK, but I have a doubt regarding the third step. As far as I could collect, DB Links can be made for the entire database only and not for only a single user or specific users. Once the DB Link is in place, if people come to know, somehow or the other, the username and passwords for other users, they will have a read-write access to our precious data.

Seems me to be a dangerous solution to be implemented.

Please clarify.

[debasisdas]

Hi Deba
Your solution seems to be OK, but I have a doubt regarding the third step. As far as I could collect, DB Links can be made for the entire database only and not for only a single user or specific users. Once the DB Link is in place, if people come to know, somehow or the other, the username and passwords for other users, they will have a read-write access to our precious data.

Seems me to be a dangerous solution to be implemented.

Please clarify.

When you create a dblink ,you need to specify the username,passwo rd and sid to connect. How can one connect to other users using the DBLINK. You need to ensure that other users don't have create dblink Priviliges.

[amitpatel66]

Hi Deba
Your solution seems to be OK, but I have a doubt regarding the third step. As far as I could collect, DB Links can be made for the entire database only and not for only a single user or specific users. Once the DB Link is in place, if people come to know, somehow or the other, the username and passwords for other users, they will have a read-write access to our precious data.

Seems me to be a dangerous solution to be implemented.

Please clarify.

Check for PRIVATE DATABASE LINK Creation. Dont create PUBLIC DATABASE LINK