Take the 2-minute tour ×

Consider the following scenerio....

I have a master user MASTER.

I have a test user TEST.

For both users the table structure are same. Both user can be on different oracle servers.

then I create a database link as master_link by logging in as test user to sql plus using the following command


By loggin in as test user and using the database link name i can modify the tables in master user. for example

update table1@master_link set display_title = 'PONDS' ;

This query updates the table table1 of master user.

My requirement is i want to give read only permission to database link (master_link) so that test user can't modify or insert into any table in master user by using database link.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

On whatever database the MASTER schema resides, you would need to create a new user (i.e. MASTER_READ_ONLY). Grant the MASTER_READ_ONLY user SELECT access on all of MASTER's tables (most likely via a role). Optionally, create either public synonyms or private synonyms in the MASTER_READ_ONLY schema that reference the objects in MASTER. Then, when you create the database link, use the MASTER_READ_ONLY account rather than the MASTER account.

Something like

As a DBA

CREATE USER master_read_only
  IDENTIFIED BY password2;

GRANT create session, create synonym
   TO master_read_only;

CREATE ROLE master_ro_role;

GRANT master_ro_role
   TO master_read_only;


  FOR x IN (SELECT * FROM user_tables)
      'GRANT SELECT ON master.' || x.table_name || 
        ' TO master_ro_role';


  FOR x IN (SELECT * FROM all_tables WHERE owner='MASTER')
      'CREATE SYNONYM ' || x.table_name || 
        ' FOR master.' || x.table_name;

On the database where the TEST user has been created

  CONNECT TO master_read_only 
  IDENTIFIED BY password2 
          (ADDRESS_LIST = 
            (ADDRESS = 
              (PROTOCOL = TCP) 
              (HOST =
              (PORT = 1521))) 
            (CONNECT_DATA = (SERVICE_NAME = orcl)))
share|improve this answer
do i have other option? I do not want to create new master_read_only user. Can i achieve it by only master and test user –  ponds Oct 12 '10 at 5:43
As Alex points out, you could create a new user on the local database, create the database link as a private link in that new schema, create views in the new local schema that query the remote tables, then grant the local test user SELECT access on those views. This isn't as secure as creating a read-only user in the master database because the new user in the local database will have complete access to the objects in the master schema. –  Justin Cave Oct 12 '10 at 6:03
@Justin: true, but the test user won't have complete access to the master which seems to be the main point. No-one except @ponds needs to be able to connect as the user that owns the link, and as they have to know the master credentials to create the link that isn't adding much risk (as long as the user is then used for other things as well, or subsequently shared). I agree it's less satisfactory though *8-) –  Alex Poole Oct 12 '10 at 17:11
@Alex: Agreed. But you're just shifting the problem from the TEST user on the local database being able to modify any table in the MASTER schema on the remote database to the new user on the local database being able to do the same thing. If I were in any way involved with the security of the data in the MASTER database, there is no way I'd allow a remote database to connect as MASTER. And if I were only involved with the TEST database, I'd be concerned that any time there were data problems on MASTER, I'd get blamed because the DML could have come from the TEST system. –  Justin Cave Oct 12 '10 at 17:16
@Justin: Can't argue with any of that. Be interesting to know why the connection is needed; kind of hoping MASTER isn't production and they're using live ref data remotely. Also begs the question why someone working on test would know the MASTER credentials in the first place. (Incidentally, could you prevent a remote DB link? Can you tell it apart from a live user?). The only time I can imagine considering this is - maybe - if there were two test instances and you're trying to keep something in step or avoid duplicating set-up; but I can think of better ways. –  Alex Poole Oct 12 '10 at 17:38
show 1 more comment

If you connect as user master anyone using the link has that user's privileges on the remote database. To isolate this you can create a new user on the instance that has the master schema, give that user select privs on (selected) master's tables, and build your database link using the read-only user.

(I'm assuming you don't have update any table granted to public on the master instance...)

Alternatively, if you aren't able to create a new user on the master instance, you could create a new user on the test instance instead. If you create the database link in that new user's schema, you could create read-only views using the link which your test user could access without exposing the database link itself. This may be more complicated for someone coming along later to trace what's happening, but it's an option.

share|improve this answer
add comment

Your Answer


