How to give permission to create objects on another Owner?

[carlosdev]

Hello everyone,
today in the company we have 2 users for each system.
-xpto_own = User Data Provider and Objects: Packages, Sequences, Views, Triggers, Procedures, Tables and Indexes
-xpto_app = Application user - data consumer and user objects Xpto_own through Grant. The application will connect with this user.

Is it possible to create another XPto_DEV user and give permission to create certain types of objects (packages, sequences, views and procedures) on another Owner? Ex: xpto_own

Note: The XPto_DEV user may not be allowed to create objects to other Owners other than Xpto_own.

We want control of tables but we want to give freedom to developers create packages and procedures.

Thanks,
Carlos Araujo

[stcoutinho]

Ola Carlosdev,

I may not be giving the complete answer, but I think you need to do the following:
a) Solution by Grant / Private Synonym
- Identify all Xpto_own objects that their procedures should access in DML (INS / UPD / DEL / SEL) operations;
- such as Xpto_own, assign the required permissions with the Grant Option option
Example:

Select all

GRANT SELECT ON XPTO_OWN.TABELA_XPTO TO XPTO_DEV WITH GRANT OPTION;

if eventually after development , the goal is to compile these objects in xpto_own, it would be recommended that you create private synonyms in xpto_dev so you do not need to be mentioning xpto_own in your PL / SQL code

b) solution by database trigger

- You can create (within a user of your exclusive use, ex: xpto_dba) a DDL lock trigger, to prevent DDL operations in Schema XPto_own (ALTER / DROP / CREATE);

- This trigger must present a logic, to only allow DDL operations involving these objects: FUNCTION,JAVA CLASS,PACKAGE,PACKAGE BODY,PROCEDURE,TRIGGER,VIEW.

- With this option, you simply simplify Very much your administration, because you do not need to be continually creating Grants and private synonyms. And I suspect your developers will be easier to debug the PL / SQL code with tools like SQL Developer;

- The only care in this situation would never have access to or permission to disable this DDL blocking trigger.

- In the forum you find more information about these triggers.
Good Luck,

Sergio Coutinho


Example:

Select all

CREATE OR REPLACE SYNONYM XPTO_DEV.TABELA_XPTO FOR XPTO_OWN.TABELA_XPTO;