Grant object access from Application Schema to another schema in oracle

On many projects you have a single application schema which contains all the objects necessary for the application. When you have to develop an external utility (external utility can be an desktop or web app or even a mobile app) that has to connect to the application schema, it makes sense to give access to only those objects that the external utility requires.

Lets call the application schema app_schema.

Our first step is to create the external schema and grant the basic schema privileges

SQL>create user ext_schema identified by 'something';

SQL>grant connect to ext_schema;

Once you create the user, your next step is identify the tables that the external schema needs access.The grant statement must be be executed from a schema(either the current application schema or a sys or another schema) which has the necessary privileges

SQL>grant select on app_schema.table_name to ext_schema;

This is one way of doing this..but a better practice is to create a role and assign all the object privileges to that new role and later assign that role to the ext_schema user.

SQL>create role ext_app_role;     --run as a privileged user who has the permissions to create role

--if for example you want to give select access to all tables in the app_schema to ext_schema a script like this would accomplish the task

SQL>begin
 FOR x IN (select * from dba_tables where owner='APP_SCHEMA')
 LOOP
 EXECUTE IMMEDIATE 'GRANT SELECT ON APP_SCHEMA.' || x.table_name || ' TO ext_app_role';
 END LOOP;
end;
/

If you want to give insert/update on select tables, you need to give similar grants as well such as

SQL>grant insert, update on app_schema to ext_app_role

Next, grant the new role to the external schema.

SQL>grant ext_app_role to ext_schema;

There is still one more thing to do. If you have to access the app_schema from ext_schema you would have to type to fully qualified name such as app_schema.table_name to get access.

so if you login as schema ext_schema and execute a statement such as below,

SQL>select * from app_schema_table

then you will get a table or view does not exist error.

If you want to avoid that and rather just use table_name instead of using schema_name.table_name, then you need to create private syonyms by logging in as ext_schema

SQL>connect ext_schema@yourdb
SQL>begin
 FOR t IN (SELECT distinct table_name FROM role_tab_privs WHERE role = 'ext_app_role' and owner = 'app_schema')
 LOOP
 EXECUTE IMMEDIATE 'CREATE SYNONYM ' || t.table_name || ' FOR app_schema.' || t.table_name;
 END LOOP;
end;
/
--then you would be able to access like this

SQL>select * from app_schema_table;