RDSADMIN.RDS_FILE_UTIL.LISTDIR works from block and not in procedure

0

Hi gurus,

Today, I followed the instructions given in the Link: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.html to perform Oracle RDS integration with S3 to import files from S3 bucket into Database directory.
I was able to perform all the steps well and able to see the files imported from my S3 bucket in the DATA_PUMP_DIR directory on the RDS instance.
When I run the query

SELECT filename FROM table(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')) order by mtime;
I get the output listing the files I imported.

Now, I am planning to get these files in a PLSQL block and the issue arises here:
When I run something like this:

DECLARE
BEGIN
  FOR fn in (SELECT * FROM table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime)
  LOOP
	dbms_output.put_line('File name is ' || fn.filename);
  END LOOP;  
END;

I can see the output in the dbms output window.

However, when I try to call this inside a procedure like the following:

CREATE OR REPLACE PROCEDURE test1 IS
BEGIN
  FOR fn in (SELECT * FROM table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime)
  LOOP
	dbms_output.put_line('File name is ' || fn.filename);
  END LOOP;  
END test1;

I receive the error:
+3/14 PL/SQL: SQL Statement ignored
3/43 PL/SQL: ORA-01031: insufficient privileges+

I searched online for this error and couldn't get any leads.
I tried writing the procedure with invoker rights

CREATE OR REPLACE PROCEDURE test1 AUTHID CURRENT_USER IS

and it still gave me the same error.

Can someone please throw light on this?

Regards,
Rakesh Devalapally

2개 답변
0

Hi Rakesh,

Oracle PL/SQL does not load privileges from roles (other than PUBLIC) when compiling or executing Definer's Rights objects, or when compiling Invoker's Rights objects. For additional details on privilege management in PL/SQL, please refer to Oracle's documentation.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/configuring-privilege-and-role-authorization.html#GUID-5C57B842-AF82-4462-88E9-5E9E8FD59874
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-subprograms.html#GUID-41D23DE7-3C07-41CF-962B-F92B696594B5

In most cases, you will have been given access to RDSADMIN packages via a role, which will work in standard SQL and anonymous blocks but not in named blocks. To get around this, you will need to directly grant object privileges to the owner of the PL/SQL block.

grant execute on rdsadmin.rds_file_util to <procedure owner>;

Hope this helps. Thanks!
Michael

AWS
답변함 5년 전
0

Thank you, Michael, for your response.
I have only one master user for the database schema and I am trying to write this named block (pl/sql procedure) in the same user.
When I tried the grant execute on rdsadmin.rds_file_util to user, it returns a you do not have permission to GRANT error.
I am troubling an Oracle DBA to help me with this. If we get this resolved, I will post the solution that worked for us.

답변함 5년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠