Por default não é permitido conceder ou revogar grants para uma procedure no banco de dados Oracle além de EXECUTE e DEBUG. No entanto, caso aja a necessidade de visualizar o conteúdo de uma procedure, você poderá criar uma view e conceder o grant de select com a opção de grant na view criada. Com isso, segue os passos:
- Criação de uma view com a opção de consulta no campo text da dba_source (caso deseja visualizar todo o conteúdo do objeto e não somente o texto, você poderá colocar o * ao invés do campo mencionado):
create view SELPROC as select text from dba_source where TYPE='PACKAGE BODY' and owner='owner here'; SQL> select distinct(TYPE) from dba_source; TYPE ------------ TYPE BODY PROCEDURE TYPE LIBRARY FUNCTION TRIGGER PACKAGE BODY PACKAGE 8 rows selected.
- Conceder o grant de consulta na view ao usúario que deseja consultar o conteúdo da package:
grant select on SELPROC to your_account with grant option;
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)