Citação de Marcos Soares em 20/12/2022, 17:52

Olá, pessoal. Compartilhar com vocês um script que automatiza o GRANT para as tabelas de um determinado SCHEMA.

Query bastante útil para liberação de acesso para novos usuários. Deixo abaixo scripts.

--TABELAS, VIEWS
BEGIN
FOR R IN (SELECT owner, object_name FROM ALL_OBJECTS WHERE owner='DBAPORTAL' and object_type = 'TABLE') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.object_name||' to USER_BI';
END LOOP;
END;

BEGIN
FOR R IN (SELECT owner, object_name FROM ALL_OBJECTS WHERE owner='DBAPORTAL' and object_type = 'VIEW' AND STATUS ='VALID') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.object_name||' to USER_BI';
END LOOP;
END;
SELECT * FROM ALL_OBJECTS WHERE owner='DBAMV' and object_type = 'VIEW'

 

--FUNCTION, PROCEDURE, PACKAGE
BEGIN
FOR R IN (SELECT owner, object_name
FROM ALL_OBJECTS WHERE owner='DBAPORTAL' and object_type = 'FUNCTION') LOOP
EXECUTE IMMEDIATE 'grant execute on '||R.owner||'.'||R.object_name||' to USER_BI';
END LOOP;
END;

BEGIN
FOR R IN (SELECT owner, object_name
FROM ALL_OBJECTS WHERE owner='DBAPORTAL' and object_type = 'PROCEDURE') LOOP
EXECUTE IMMEDIATE 'grant execute on '||R.owner||'.'||R.object_name||' to USER_BI';
END LOOP;
END;

BEGIN
FOR R IN (SELECT owner, object_name
FROM ALL_OBJECTS WHERE owner='DBAPORTAL' and object_type = 'PACKAGE') LOOP
EXECUTE IMMEDIATE 'grant execute on '||R.owner||'.'||R.object_name||' to USER_BI';
END LOOP;
END;

 

Espero que lhe seja útil.