User permissions between oracle databases


I appreciate your collaboration with the following situation. I have two different databases (ex: orcl1 and orcl2 ), the database orcl1 has several users-schemas ( esquema1 , esquema2 ), the database orcl2 has a user -schema usrdb . I must give permissions so that the user usrdb of the database orcl2 can do crud on the tables of the schemas esquema1 and esquema2 of the database orcl1 . The question I have is how can I grant those permissions among database users?

asked by isaac 19.10.2018 в 20:51

1 answer


The short answer is: You can not grant privileges to a user from another database in oracle.

Making this answer longer:

This does not mean that someone connected to a database can not modify information that resides in another database, simply that it must use a mechanism of authentication controlled by what is known as db links In essence, the db-link defines the parameters of connection to the database and the way to authenticate in it, which can be through a username and password that are burned in the db link ; that is: all the users of database A, when they appear as the same user when performing operations in database B, or it can be CURRENT USER .

The simplest is the first one, for example, in the host where your database Orcl2 is, you configure a TNS alias with the parameters of Orcl1 and you execute this instruction:

   CONNECT TO Orcl2User IDENTIFIED BY Orcl2Password
   USING 'Orcl1';

In the Orcl1 database there must be the user Orcl2User and its password Orcl2Password .

From there, all Orcl2 users will be able to access Orcl1 objects with the Orcl2User permissions, for example, perform:

select * from [email protected];

If that is not enough for you, then let's talk about database links for CURRENT USER :

In this case, users must be global users that must exist in both databases (LDAP).

The creation of the database link is like this:

   USING 'Orcl1';

The use is the same, but this time, each user will have their own privileges (which are granted locally in each database, since users exist in both).

For more information, visit the documentation for the clause GLOBALLY of create user , which is complemented by the one in the example Defining a CURRENT_USER Database Link from CREATE DATABASE LINK documentation

answered by 19.10.2018 / 23:34