Skip to Main Content
  • Questions

  • Insufficient Privileges - C##CLOUD$SERVICE.DBMS_CLOUD

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pradeep.

Asked: November 16, 2022 - 1:22 am UTC

Last updated: January 21, 2025 - 5:16 pm UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.1.0

Viewed 10K+ times! This question is

You Asked

While we are working on dbms_cloud API in ATP DB, we are facing some issues.

When we are calling the API directly from SQL Developer, it works perfectly fine and data is getting loaded into the tables.

BEGIN
  dbms_output.put_line('Start Time : '||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
 
  dbms_cloud.copy_data(table_name      => 'PD_TEST_TAB'
                     , credential_name => 'PD_TEST_CREDS'
                     , file_uri_list   => 'https://aws.s3/'
                     , format          => json_object('type'                 VALUE 'csv'
                                                    , 'skipheaders'          VALUE '1'
                                                    , 'ignoremissingcolumns' VALUE 'true'));

  dbms_output.put_line('End Time : '||TO_CHAR(SYSDATE, 'MM/DD/YYYY HH24:MI:SS'));
END;


But, when we wrap this seeded API into a custom API we are facing some access related issues. For ex, 

CREATE OR REPLACE PROCEDURE pd_tab_imp_prc
AS
BEGIN
  dbms_cloud.copy_data(table_name      => 'PD_TEST_TAB'
                     , credential_name => 'PD_TEST_CREDS'
                     , file_uri_list   => 'https://aws.s3/'
                     , format          => json_object('type'                 VALUE 'csv'
                                                    , 'skipheaders'          VALUE '1'
                                                    , 'ignoremissingcolumns' VALUE 'true'));
END pd_tab_imp_prc;


When we call pd_test_prc we are facing following errors

Error report -
ORA-20000: ORA-01031: insufficient privileges
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1322
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 5781
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 5804
ORA-06512: at "YLPOICDEV.PD_TAB_IMP_PRC", line 4
ORA-06512: at line 1
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.


Can you please check and let us know we are missing anything here?

---------------------------------------------------------------------
----------------------- Here is test log ----------------------------
---------------------------------------------------------------------
--------------------
-- Package Grants --
--------------------
SELECT *
  FROM dba_tab_privs
 WHERE table_name = 'DBMS_CLOUD';
​
----------------
-- SQL Output --
----------------
GRANTEE  OWNER             TABLE_NAME  GRANTOR           PRIVILEGE  GRANTABLE  HIERARCHY  COMMON  TYPE     INHERITED
-------  -----             ----------  -------           ---------  ---------  ---------  ------  ----     ---------
DWROLE   C##CLOUD$SERVICE  DBMS_CLOUD  C##CLOUD$SERVICE  EXECUTE NO        NO         NO   PACKAGE  NO
​
---------------------
-- User Privileges --
---------------------
SELECT *
  FROM dba_role_privs
 WHERE grantee = 'PDDEV';
​
----------------
-- SQL Output --
----------------
GRANTEE  GRANTED_ROLE  ADMIN_OPTION  DELEGATE_OPTION  DEFAULT_ROLE  COMMON  INHERITED
-------  ------------  ------------  ---------------  ------------  ------  ---------
PDDEV    DWROLE        NO            NO               YES         NO     NO
​
--------------------
-- Test Procedure --
--------------------
CREATE PROCEDURE pd_tab_imp_prc
AS
BEGIN
  dbms_cloud.copy_data(table_name      => 'PD_TEST_TAB'
                     , credential_name => 'PD_TEST_CREDS'
                     , file_uri_list   => 'https://aws.s3/'
                     , format          => json_object('type'                 VALUE 'csv'
                                                    , 'skipheaders'          VALUE '1'
                                                    , 'ignoremissingcolumns' VALUE 'true'));
END pd_tab_imp_prc;
​
------------------------
-- Compilation Errors --
------------------------
Procedure PD_TAB_IMP_PRC compiled
​
LINE/COL  ERROR
--------- -------------------------------------------------------------
4/3       PL/SQL: Statement ignored
4/3       PLS-00201: identifier 'DBMS_CLOUD' must be declared
Errors: check compiler log
​
-------------------
-- Grant Execute --
-------------------
-- From Admin user
GRANT EXECUTE ON DBMS_CLOUD TO PDDEV;
​
----------------
-- SQL Output --
----------------
Grant succeeded.
​
--------------------
-- Test Procedure --
--------------------
CREATE OR REPLACE PROCEDURE pd_tab_imp_prc
AS
BEGIN
  dbms_cloud.copy_data(table_name      => 'PD_TEST_TAB'
                     , credential_name => 'PD_TEST_CREDS'
                     , file_uri_list   => 'https://aws.s3/'
                     , format          => json_object('type'                 VALUE 'csv'
                                                    , 'skipheaders'          VALUE '1'
                                                    , 'ignoremissingcolumns' VALUE 'true'));
END pd_tab_imp_prc;
​
----------------
-- SQL Output --
----------------
Procedure PD_TAB_IMP_PRC compiled
​
-----------------------
-- Execute Procedure --
-----------------------
EXEC pd_tab_imp_prc;
​
----------------------
-- Execution Errors --
----------------------
Error report -
ORA-20000: ORA-01031: insufficient privileges
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1322
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 5781
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 5804
ORA-06512: at "YLPOICDEV.PD_TAB_IMP_PRC", line 4
ORA-06512: at line 1
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

and Connor said...

Take a look here

https://asktom.oracle.com/Misc/RolesAndProcedures.html

Whatever DBMS_CLOUD is trying to do will need to be granted directly (not via a role) to the user concerned.

So besides EXECUTE you'll probably need:

- read/write on any directory involved
- create table if the copy is creating a new table

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Were you able to resolve this issue?

A reader, July 19, 2023 - 7:21 pm UTC

Were you able to resolve this issue?
Connor McDonald
August 04, 2023 - 5:34 am UTC

I don't know - they never responded

AUTHID CURRENT_USER

Ishiko, September 13, 2023 - 4:02 am UTC

I was facing the same problem.
I solved it by specifying AUTHID CURRENT_USER in the procedure definition part when creating the stored procedure.
This makes the executor of the procedure the executor of the procedure.

------------
CREATE OR REPLACE PROCEDURE xxxx
AUTHID CURRENT_USER
IS
....
Chris Saxon
September 27, 2023 - 1:43 pm UTC

Thanks for sharing your solution

"enablelogs" set to FALSE.

Rajeshwaran Jeyabal, October 17, 2023 - 5:41 am UTC

Team,

the below demo was from my Free Tier ATP 19c instance.

though we set "enablelogs" to False, can you help me to understand why still we need read and write privilege on the default directory "DATA_PUMP_DIR"? 

demo_user@ATP19C> select utl_raw.cast_to_varchar2(dbms_cloud.get_object('my_demo_cred',:filename)) from dual;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_CLOUD.GET_OBJECT('MY_DEMO_CRED',:FILENAME))
-------------------------------------------------------------------------
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-1980 12:00:00 AM,800,,20
7499,"ALLEN","SALESMAN",7698,20-FEB-1981 12:00:00 AM,1600,300,30
7521,"WARD","SALESMAN",7698,22-FEB-1981 12:00:00 AM,1250,500,30
7566,"JONES","MANAGER",7839,02-APR-1981 12:00:00 AM,2975,,20
7654,"MARTIN","SALESMAN",7698,28-SEP-1981 12:00:00 AM,1250,1400,30
7698,"BLAKE","MANAGER",7839,01-MAY-1981 12:00:00 AM,2850,,30
7782,"CLARK","MANAGER",7839,09-JUN-1981 12:00:00 AM,2450,,10
7788,"SCOTT","ANALYST",7566,19-APR-0087 12:00:00 AM,3000,,20
7839,"KING","PRESIDENT",,17-NOV-1981 12:00:00 AM,5000,,10
7844,"TURNER","SALESMAN",7698,08-SEP-1981 12:00:00 AM,1500,0,30
7876,"ADAMS","CLERK",7788,23-MAY-0087 12:00:00 AM,1100,,20
7900,"JAMES","CLERK",7698,03-DEC-1981 12:00:00 AM,950,,30
7902,"FORD","ANALYST",7566,03-DEC-1981 12:00:00 AM,3000,,20
7934,"MILLER","CLERK",7782,23-JAN-1982 12:00:00 AM,1300,,10

emo_user@ATP19C> create table emp_demo as
  2  select *
  3  from demo.emp
  4  where 1 = 0 ;

Table created.

demo_user@ATP19C> begin
  2     dbms_cloud.copy_data(
  3             table_name =>'emp_demo',
  4             credential_name =>'my_demo_cred',
  5             file_uri_list => :filename,
  6             format => json_object('delimiter' value ',',
  7                             'dateformat' value 'dd-mon-yyyy hh12:mi:ss am',
  8                             'quote' value '"','enablelogs' value false,
  9                             'skipheaders' value '1') );
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-20000: ORA-06564: object DATA_PUMP_DIR does not exist
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1693
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 8286
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 8309
ORA-06512: at line 2


demo_user@ATP19C>
demo_user@ATP19C> conn admin/"********"@atp19c
Connected.
admin@ATP19C> grant write on directory data_pump_dir to demo_user;

Grant succeeded.

admin@ATP19C> conn demo_user/"********"@atp19c
Connected.
demo_user@ATP19C> begin
  2     dbms_cloud.copy_data(
  3             table_name =>'emp_demo',
  4             credential_name =>'my_demo_cred',
  5             file_uri_list => :filename,
  6             format => json_object('delimiter' value ',',
  7                             'dateformat' value 'dd-mon-yyyy hh12:mi:ss am',
  8                             'quote' value '"','enablelogs' value false,
  9                             'skipheaders' value '1') );
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-20000: ORA-06564: object DATA_PUMP_DIR does not exist
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1693
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 8286
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 8309
ORA-06512: at line 2


demo_user@ATP19C> conn admin/"********"@atp19c
Connected.
admin@ATP19C> grant read on directory data_pump_dir to demo_user ;

Grant succeeded.

admin@ATP19C>  conn demo_user/"********"@atp19c
Connected.
demo_user@ATP19C> begin
  2     dbms_cloud.copy_data(
  3             table_name =>'emp_demo',
  4             credential_name =>'my_demo_cred',
  5             file_uri_list => :filename,
  6             format => json_object('delimiter' value ',',
  7                             'dateformat' value 'dd-mon-yyyy hh12:mi:ss am',
  8                             'quote' value '"','enablelogs' value false,
  9                             'skipheaders' value '1') );
 10  end;
 11  /

PL/SQL procedure successfully completed.

demo_user@ATP19C> select * from emp_demo;

     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-0087       3000                    20
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-0087       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-1981        950                    30
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10

14 rows selected.

demo_user@ATP19C>

GRANT ANY OBJECT PRIVILEGE not working as expected??

Narendra, January 20, 2025 - 4:21 pm UTC

Hello Connor/Chris,

I must be missing something obvious but in a 19c multitenant database, a LOCAL user with GRANT ANY OBJECT PRIVILEGE is unable to grant execute on SYS packages to individual LOCAL users. There is no database vault configured/enabled.
Is this expected/documented or am I missing something obvious?

20-JAN-2025 16:09:07 LADS@PDB1 > set lines 300 pages 999
20-JAN-2025 16:09:18 LADS@PDB1 > select * from dba_dv_status ;

NAME                STATUS
------------------- ----------------------------------------------------------------
DV_APP_PROTECTION   NOT CONFIGURED
DV_CONFIGURE_STATUS FALSE
DV_ENABLE_STATUS    FALSE

20-JAN-2025 16:09:25 LADS@PDB1 > select * from session_privs where privilege like 'GRANT ANY%' ;

PRIVILEGE
----------------------------------------
GRANT ANY ROLE
GRANT ANY PRIVILEGE
GRANT ANY OBJECT PRIVILEGE

20-JAN-2025 16:09:47 LADS@PDB1 > GRANT execute ON sys.DBMS_AQIN TO PRACTICAL with grant option;
GRANT execute ON sys.DBMS_AQIN TO PRACTICAL with grant option
                     *
ERROR at line 1:
ORA-01031: insufficient privileges

Chris Saxon
January 21, 2025 - 5:16 pm UTC

Note this line in the docs for GRANT:

Note that ANY system privileges, for example, SELECT ANY TABLE, will not work on SYS objects or other dictionary objects.

So you still need privileges on the object itself because it's in SYS.


Re: GRANT ANY OBJECT PRIVILEGE not working as expected??

Narendra, January 21, 2025 - 10:53 pm UTC

Thanks Chris for your response.

Note that ANY system privileges, for example, SELECT ANY TABLE, will not work on SYS objects or other dictionary objects.

I read that line in the document but interpreted that as "A user having SELECT ANY TABLE privilege, can not query SYS objects".
My test case is about the inability of a user with GRANT ANY OBJECT PRIVILEGE, to grant EXECUTE privilege on DBMS_AQADM (SYS-owned object).

Thanks in advance

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.