Accessing tables in one schema thru another schema

A)Schema Owners and Application Users

In the context of this article, the schema owner represents the Oracle user that owns all your database objects, while application users are Oracle users that need access to those schema objects.

Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far to many privileges, which can easily result in damage to your data and the objects themselves. Instead, it is better to define application users and grant those users the necessary privileges on the schema owners objects.

CURRENT_SCHEMA Approach:

1) First, we create the schema owner and an application user.

CREATE USER schema_owner IDENTIFIED BY password
GRANT CONNECT, CREATE TABLE TO schema_owner;

CREATE USER app_user IDENTIFIED BY password
GRANT CONNECT TO app_user;

2)Next, we create some roles to allow read-write and read-only access.

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;

3)We want to give our application user read-write access to the schema objects, so we grant the relevant role.

GRANT schema_rw_role TO app_user;
4)Now we are ready to create an object in the schema owner.

CONN schema_owner/password

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

Notice how the privileges are granted to the relevant roles.

This method is ideal where the application user is requiring no objects of its own. It is clean and doesn't require management of thousands of synonyms

Synonym Approach:

This method relies on synonyms owned by the application user to point to the correct location of the schema objects.

1)First, we create the users in a similar way to the previous example.

CREATE USER schema_owner IDENTIFIED BY password
GRANT CONNECT, CREATE TABLE TO schema_owner;

CREATE USER app_user IDENTIFIED BY password
GRANT CONNECT, CREATE SYNONYM TO app_user;

2)Next, we create some roles to allow read-write and read-only access and grant the read-write role to the application user.

CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;

3)We want to give our application user read-write access to the schema objects, so we grant the relevant role.

GRANT schema_rw_role TO app_user;

4)Now we are ready to create an object in the schema owner in the same way we did in the previous example.

CONN schema_owner/password

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;

If we now connect to the application user we are not able to see the object without qualifying it with a schema name

SQL> CONN app_user/password
Connected.
SQL> DESC test_tab
ERROR:
ORA-04043: object test_tab does not exist

SQL> DESC schema_owner.test_tab
Name Null? Type
----------------------------------------------------- ---------
ID NOT NULL NUMBER
DESCRIPTION VARCHAR2(50)

SQL> CREATE SYNONYM test_tab FOR schema_owner.test_tab;

Synonym created.

SQL> DESC test_tab
Name Null? Type
----------------------------------------------------- -------- -------------
ID NOT NULL NUMBER
DESCRIPTION VARCHAR2(50)

When there are a large number of application users, this method is cumbersome due to the huge number of synonyms required.

Obviously, it is possible to use public synonyms, but this can be problematic when you have multiple application schemas on a single instance.

B)Db Link :

DB Links are pretty much the name of the game here. If you can't get one created on your own,
then check if there are any public DB links that you could use.

CREATE DATABASE LINK new_york
CONNECT TO tom IDENTIFIED BY thumb
USING 'NEWYORK.world'; -- entry from tnsnames.ora

select * from emp@new_york
union
select * from dick.emp;