Friday, October 8, 2021

DBMS_SPACE.create_index_cost ORA-01031: insufficient privileges

Running the script 

----------------------------------------------------------
-- File name:   cs_estimate_index_size.sql
-- Purpose:     Estimate Index Size
-- Author:      Carlos Sierra
-- Version:     2020/12/06
-- Usage:       Execute connected to PDB.
--
-- Example:     $ sqlplus / as sysdba
--              SQL> @cs_estimate_index_size.sql
--
-- Notes:       Developed and tested on 12.1.0.2.
----------------------------------------------------------
--
SET HEA ON LIN 2490 PAGES 100 TAB OFF FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF LONG 240000 LONGC 2400 NUM 20 SERVEROUT OFF;
PRO
PRO 1. Enter Index Name:
DEF index_name = '&1.';
UNDEF 1;
--
COL schema_name NEW_V schema_name NOPRI;
SELECT owner AS schema_name FROM dba_indexes WHERE index_name = '&&index_name.';
--
VAR v_used_bytes NUMBER;
VAR v_alloc_bytes NUMBER;
BEGIN
  DBMS_SPACE.create_index_cost (
    ddl             => DBMS_METADATA.get_ddl('INDEX', '&&index_name.', '&&schema_name.'),
    used_bytes      => :v_used_bytes,
    alloc_bytes     => :v_alloc_bytes
  );
END;
/
COL used_gb FOR 999,990.000;
COL alloc_gb FOR 999,990.000;
SELECT :v_used_bytes/1e9 AS used_gb, :v_alloc_bytes/1e9 AS alloc_gb FROM DUAL;
--
ROLLBACK;
DELETE plan_table;
BEGIN
EXECUTE IMMEDIATE('EXPLAIN PLAN FOR '||DBMS_METADATA.get_ddl('INDEX', '&&index_name.', '&&schema_name.'));
END;
/
COMMIT;
SET HEA ON PAGES 0;
PRO
SELECT plan_table_output FROM
TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', NULL, 'ADVANCED'))
/
SET HEA ON PAGES 100;
CLEAR COLUMNS;

the developer got an

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 2833
ORA-06512: at "SYS.DBMS_SPACE", line 2854
ORA-06512: at line 5

If the developer run this script as SYS - then it works well. But on the production system the developer  works as specially created DEV_USER and this script produce error. And developer don't understand why, because it has all rights needed:

CREATE USER DEV_USER
  IDENTIFIED BY ...
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE ...
  ACCOUNT UNLOCK;

ALTER USER DEV_USER QUOTA 1g ON USERS;

GRANT CONNECT TO DEV_USER;
GRANT PERFORMANCE_TUNE TO DEV_USER;
ALTER USER DEV_USER DEFAULT ROLE ALL;

GRANT ANALYZE ANY TO DEV_USER;
GRANT ANALYZE ANY DICTIONARY TO DEV_USER;
GRANT CREATE PROCEDURE TO DEV_USER;
GRANT CREATE TABLE TO DEV_USER;
GRANT CREATE INDEX to DEV_USER;
GRANT SELECT ANY DICTIONARY TO DEV_USER;
GRANT SELECT ANY TABLE TO DEV_USER;

GRANT EXECUTE ON SYS.DBMS_METADATA TO DEV_USER;
GRANT EXECUTE ON SYS.DBMS_SPACE TO DEV_USER;

After we created a test case to run DBMS_SPACE manually:

declare
      x int;
      y int;
    begin
      dbms_space.CREATE_INDEX_COST(
         'create index ABCD.TAB1_COL1_ID on ABCD.TAB1 (COL1_ID)'
         ,used_bytes=>x
         ,alloc_bytes=>y);
      dbms_output.put_line(x);
     dbms_output.put_line(y);
   end;

Error at line 1
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 2833
ORA-06512: at "SYS.DBMS_SPACE", line 2854
ORA-06512: at line 5

Key point is: DEV_USER run DBMS_SPACE which try to create index in the foreign schema ! To be able to do this DEV_USER should have an "GRANT CREATE ANY INDEX TO DEV_USER " right.

 

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

How to disable/setup autostart parameters for specified instance ?

Q: We have a 4-node RAC. I need to disable autostart of the DB on one node only.    How to do it and how to see autostart parameters, confir...