1 Reply Latest reply on Sep 9, 2020 10:18 PM by EdStevens

    Automatically grant permissions to new objects in a schema

    3313546

      Hello,

       

      Is there a way to automatically grant object permissions to a newly created object within a schema to another schema without explicitly doing this?

       

      In other words, I am the DBA, and I created a new schema for a tenant. The tenant wants  certain roles to automatically inherit certain object priviliges to any newly created object within this new schema.

       

      Is there a way to achieve this in Oracle without explicitly specifying the object privilege grant at the time of creation of the new object?

       

      Perhaps like a trigger like this:

      create or replace TRIGGER schema.auto_grant after CREATE ON schema_name.SCHEMA

      declare

          new_object new_object_name;

      begin

      case

          if dictionary_obj_type = 'TABLE' then grant SELECT on schema_name.new_object to DB_role;

          if dictionary_obj_type = 'SEQUENCE' then grant SELECT on schema_name.new_object to DB_role;

          if dictionary_obj_type = 'VIEW' then grant SELECT on schema_name.new_object to DB_role;

          if dictionary_obj_type = 'TRIGGER' then grant SELECT on schema_name.new_object to DB_role;

          if dictionary_obj_type = 'PACKAGE' then grant SELECT on schema_name.new_object to DB_role;

          if dictionary_obj_type = 'FUNCTION' then grant execute on schema_name.new_object to DB_role;

          if dictionary_obj_type = 'PROCEDURE' then grant execute on schema_name.new_object to DB_role;

          if dictionary_obj_type = 'TYPE' then grant SELECT on schema_name.new_object to DB_role;

          end if;

      end;

      /