68826

grant to create synonyms on another schema (Oracle)

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

Answer1:

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;

Answer2:

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.

Recommend

  • CoreBluetooth connection setup time varies quite a bit
  • How to run a linux command to compile a c program, from php script
  • How to record user generated sound output on iPhone
  • how to make PHP lists all Linux Users?
  • How to extract a number from a string [duplicate]
  • Google Calendar Api is not showing event list
  • How can I emulate a recursive type definition in C++?
  • Java Garbage collection, setting reference to null
  • How to get a list with description of all dba packages
  • How to retrieve multiple columns from non-entity type sql query?
  • Organizing large javascript files [closed]
  • Refactoring advice: maps to POJOs
  • JavaScriptCore External Arrays
  • Nested projects in multiproject visual studio templates
  • Single django queryset to get n adjacent items
  • Double dispatch in Java example
  • conditions for accessors in Coldfusion ORM
  • Retaining data after updating application
  • Django return user model id with L
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • Examples of how to a STS in .Net 4.5 using WCF
  • In Java, how can I construct a File from a resource?
  • Do query loads all the data in memory
  • Bigquery event streaming and table creation
  • Question about instantiating object
  • Cannot upload to OneDrive using the new SDK
  • JBoss External Properties Files in Classpath
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Debug.DrawLine not showing in the GameView
  • ActiveRecord query for a count of new users by day
  • Email verification using google app script and google forms
  • Change Inet root folder for iis 7
  • CSS Linear-gradient formatting issue accross different browsers
  • Launch Runnable Jar from Web Start
  • In LanguageTool, how do you create a dictionary and use it for spell checking?
  • php design question - will a Helper help here?
  • AngularJs get employee from factory
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • Python/Django TangoWithDjango Models and Databases