How to make database objects accessible using synonyms in oracle.

When it comes to application development it is not always happens that application developer are so much sound with database knowledge. They know their own part of task to execute. Like to connect to database, to bind with the database object and to fetch data, insert / update or delete data. But it is not possible for them to take account of each and every database objects.

Let us start with an example. An application is accessing a database schema ACC_DATA which contains all the tables / views. Now it is obvious that it will never have direct access to this schema user rather it will have to connect from an application user. In our case let us consider a technical user ACC_APP. Now ACC_APP have read / write access to ACC_DATA. That mean it can select / insert / update and delete from tables in ACC_DATA.

Now supposed some new views have been created in ACC_DATA and application developer have been instructed to add those views in some new reports to be displayed in the application GUI. Now these application handles several other schema users data through ACC_APP. So it is not possible for the developer to keep track as from which schema these tables are supposed to fetch data and use schemaname.table name inside the application code and it is not also feasible in all scenario to user this kind of schemaname.tablename as it is not permissible. So what is the solution?

The solution is to create a snyonym for the respective schema.tablename in ACC_APP. And developer can directly user the synonym name which will access the tablename using the synonym thinking it as the actual tablename, so it acts as pseudo tablename.

So whenever a new table is added, the request is send to dba to add one synonym into ACC_APP.

Suppose a new table CUST_DATA is created in ACC_DATA. We already consider that grant select any table privileges is provivded to the user ACC_APP. So exclusive privileges are not required to provide for this table to ACC_APP and we assume the privilege is already provided when the table is created.

So DBA should run one command like CREATE SYNONYM CUST_DATA for ACC_DATA.CUST_DATA.

Now let us consider the situation where none of the SYNONYMS existis in ACC_APP and the dba has to create synonyms for all existing tables / VIEWS in ACC_DATA in ACC_APP. So if the number of tables / views are thousands then dba should be absconding from office from the very next day. So dba shouls create a script which will create SYNONYMS for all existing tables / views in ACC_APP. Here below I an writing down that script.

DECLARE

CURSOR REC IS SELECT TABLE_NAME FROM ALL_USERS WHERE USERNAME='ACC_DATA';
BEGIN
    FOR I IN REC LOOP
        EXECUTE IMMEDIATE 'CREATE SYNONYM ACC_APP.'||I.TABLE_NAME||' FOR ACC_DATA.'||I.TABLE_NAME;
    END LOOP;
END;
/

So all tables / views of ACC_DATA will be accessible with this pseudo tablename / synonyms within ACC_APP.