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;
/