1

Assuming the following user with these privileges granted:

-- create admin user on CDB
CREATE USER c##myadmin IDENTIFIED BY myadmin DEFAULT TABLESPACE system QUOTA UNLIMITED ON system ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER c##myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/ 
-- grant needed permissions
GRANT DBA to c##myadmin                            ;
GRANT CREATE SESSION TO c##myadmin                 ;
GRANT CREATE TABLE TO c##myadmin                   ;
GRANT EXECUTE_CATALOG_ROLE TO c##myadmin           ;
GRANT EXECUTE ON DBMS_LOGMNR TO c##myadmin         ;
GRANT SELECT ON V_$DATABASE TO c##myadmin          ;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##myadmin   ;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##myadmin      ;
GRANT SELECT ON V_$LOG TO c##myadmin               ;
GRANT SELECT ON V_$LOGFILE TO c##myadmin           ;
GRANT RESOURCE, CONNECT TO c##myadmin              ;

Now, when I connected as my myadmin, I can run the following:

BEGIN 
  DECLARE v NUMBER := 0;
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/log/arc0000013.0001', Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(StartScn=>23456789, EndScn=>23567890,  Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
  select count(*) into v from v$logmnr_contents;
END;
END;
/
PL/SQL procedure successfully completed.

but when it created as a procedure it failed with insufficient privileges:

Create Or Replace Procedure Test AS
 v NUMBER:=0;
BEGIN
 DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/log/arc0000013.0001', Options=>DBMS_LOGMNR.new);
 DBMS_LOGMNR.START_LOGMNR(StartScn=>23456789, EndScn=>23567890,  Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT); 
 Select Count(*) into v from v$logmnr_contents;
END;
/
Exec Test
/
Procedure Test compiled


Error starting at line 9 in command -
BEGIN Test; END;
Error report - 
ORA-01031: insufficient privileges
ORA-06512: at "C##MYADMIN.TEST", line 6
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

If I comment out the select the procedure succeeds.

Is there an additional privilege to enable it to run the select from a procedure?

1

V$LOGMNR_CONTENTS

V$LOGMNR_CONTENTS contains log history information. To query this view, you must have the LOGMINING privilege.

The LOGMINING privilege was granted to DBA role. When running an anonymous block, all your privileges granted through a role are effective. When running a stored procedure defined with the default definers rights option, privileges granted through a role are ignored. The LOGMINING privilege should be granted directly to your user:

grant logmining to c##myadmin;
2
  • You are right. it works. but why it works without GRANT LOGMINING when it been called directly? – SHR Jan 19 at 13:47
  • @SHR "When running an anonymous block, all your privileges granted through a role are effective." – Balazs Papp Jan 19 at 13:50
0

You need the ability to REFERENCE the table/view in order to include in a precompiled ptogram.

For trial and error testing ONLY, you could grant ALL permissions. But, this is a serious security risk.

3
  • But why this table\view is different from other tables\views? for example: I can select count(*) from v$database – SHR Jan 19 at 13:06
  • No, this is not correct. The REFERENCES privilege serves a different purpose. – Balazs Papp Jan 19 at 13:43
  • "why this table\view is different from other tables\views?" Because access to it has more security implications. – EdStevens Jan 19 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.