Insufficient privileges when GRANT EXECUTE ON DBMS_ALERT

Question

I'm installing new Oracle 12.1.0.2 in docker. After installation I just want to execute few of prepared (not by me) scripts.

One of them on which execution is failing is

GRANT EXECUTE ON DBMS_ALERT to TESTUSR;

ORA-01031: insufficient privileges

I'm executing scripts with SYSTEM user from SQLDeveloper. Can somebody tell me what do I need to do to be able to execute this script?

Answer 1

Admin Privileges or the EXECUTE_CATALOG_ROLE Role Required to Execute DBMS_ALERTS

Per the documentation here, https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG99875 (section "User Access to Objects in the SYS Schema")

It states: Users with explicit object privileges or those who connect with administrative privileges (SYSDBA) can access objects in the SYS schema.

Since you are using the system account, you could execute the package, DBMS_ALERTS, logging in as system@db as sysdba. See below screenshot to see how to add the dbs role or sysdba privilege

---

Alternatively, you could use the EXECUTE_CATALOG_ROLE to execute DBMS_ALERTS.

You can see user or roles whom have this access here:

usr@db>SELECT
  2      grantee,
  3      granted_role,
  4      default_role
  5  FROM
  6      dba_role_privs
  7  WHERE
  8      1 = 1
  9      AND   grantee IN (
 10          'DBA',
 11          'SYS',
 12          'SYSTEM'
 13      )
 14          AND   granted_role IN (
 15          'DELETE_CATALOG_ROLE',
 16          'EXECUTE_CATALOG_ROLE',
 17          'SELECT_CATALOG_ROLE'
 18      )
 19  ORDER BY
 20      1,
 21      2;
GRANTEE   GRANTED_ROLE           DEFAULT_ROLE
DBA       DELETE_CATALOG_ROLE    YES
DBA       EXECUTE_CATALOG_ROLE   YES
DBA       SELECT_CATALOG_ROLE    YES
SYS       DELETE_CATALOG_ROLE    YES
SYS       EXECUTE_CATALOG_ROLE   YES
SYS       SELECT_CATALOG_ROLE    YES


6 rows selected.

usr@db>SELECT
  2      grantee,
  3      granted_role
  4  FROM
  5      dba_role_privs
  6  WHERE
  7      1 = 1
  8      AND   granted_role = 'DBA'
  9  ORDER BY
 10      1,
 11*     2;
usr@db>/
GRANTEE   GRANTED_ROLE
SYS       DBA
SYSTEM    DBA

It is bad practice to use the sys account even though it has adequate privileges.

The best solution is to grant the dba role to a user account, eg usr.

Using the sysdba privilege (e.g. system@db as sysdba), create a user having the needed privileges:

create user usr identified by Pa$$W0rD;
grant create session to usr;
grant dba to usr;

Consequently, you could log in with the dba role to execute this package.

In SQL*Plus, this would look like this (assumes usr has been granted the dba role):

usr@db as dba

In SQL Developer, you would set-up your connection as seen below, selecting the role, dba: