Question:

I just wondering if there any option to grant permission to create synonyms on different schema without giving 'ANY' option. I just want to narrow down the grant to provide permission with what is required for security purpose.

We have created a schema name A which related to application product. But the application suppose to access the object through another (login) schema B. We have granted resource to schema A so schema A owner can creates its own objects. What grant syntax i need to use to grant Schema A to create synonyms on schema B, so it can create synonyms.

End result should be as below and can be created by schema owner A without interference of DBA

B.b_synonym maps to A.b_object

You need the CREATED ANY SYNONYM privilege to do that as A, therefore

GRANT CREATE ANY SYNONYM TO A;

EDIT: To avoid the ANY privilege, do this:

a) as A:

GRANT SELECT ON mytable1 TO B; GRANT SELECT, INSERT, UPDATE, DELETE ON mytable2 TO B;

b) as B:

CREATE SYNONYM a_mytable1 FOR A.mytable1; CREATE SYNONYM a_mytable2 FOR A.mytable2;

You can't grant privileges that only apply to one other schema. You would have to grant ANY - even if temporarily, e.g. during the creation/modification of the main A schema, to reduce the security impact - and create all the synonyms in the other B user's schema while you had the privileges. Otherwise user B would have to create the synonyms itself; or user A could create public synonyms.

As an alternative to having any synonyms, you could have user B switch to schema A with:

alter session set current_schema = A;

They could then refer to A's objects without having to prefix them with the schema name, though they then couldn't see any objects in their own schema without prefixing those instead - it doesn't sound like B will have objects but hard to tell.

You can also automate that schema switch via a logon trigger:

create or replace trigger ramread_logon_trigger after logon on database begin if user = 'B' then execute immediate 'alter session set current_schema = A'; end if; end; /

If you actually have multiple users you can use a role instead, and switch schema for any user that has that role, by testing with dbms_session.is_role_enabled. The same role could be granted the necessary permissions to access A's objects, which you will need to grant somehow - a synonym doesn't itself give any access privileges.