APP_USER should have the privileges to develop TXEventq and Kafka applicaitons

[andytael]

APPUSER should get the privileges to develop applications for TXEventQs (with Kafka compatibility).

GRANT AQ_ADMINISTRATOR_ROLE to user;
GRANT EXECUTE on DBMS_AQ to user;
GRANT EXECUTE on DBMS_AQADM to user;
GRANT EXECUTE on DBMS_AQIN to user;
GRANT EXECUTE on DBMS_AQJMS TO user;
GRANT SELECT on GV_$SESSION to user;
GRANT SELECT on V_$SESSION to user;
GRANT SELECT on GV_$INSTANCE to user;
GRANT SELECT on GV_$LISTENER_NETWORK to user;
GRANT SELECT on GV_$PDBS to user;
GRANT SELECT on USER_QUEUE_PARTITION_ASSIGNMENT_TABLE to user;
exec DBMS_AQADM.GRANT_PRIV_FOR_RM_PLAN('user');

[gvenzl]

Hi @andytael,

Thanks a lot for the request!

I'm hesitant to add additional privileges to the APP_USER besides the intended DB_DEVELOPER_ROLE as it may give Developers a false sense of confidence that privileges will be available in production for them to use as well and they build applications with these assumptions.

The DB_DEVELOPER_ROLE only contains privileges that are safe to grant in production. I'm not sure whether the same can be said for the privileges above.

There is also an element of adding grants for each possible feature a user may use, ultimately leading to the (close) equivalent of granting DBA to the APP_USER, defeating the purpose.

However, there is obviously also the counter-argument that if a user wants to use a given feature, the privileges will be required and how would a user find out? Luckily, I've just added Config scripts to the repository and this would be a great candidate for an additional config script.

Do you have any additional documentation on other potential prerequisites to using TXEventQs that should also be addressed?

[andytael]

@gvenzl Creating the Config scripts feature makes a lot of sense, then users can create different scripts for their needs of privilegies. It is a matter of documentation then :-) I have refined the privs needed (besides the DB_DEVELOPER_ROLE) to something like this, not sure if you need this?

-- Grant AQ/TxEventQ operational privileges
GRANT EXECUTE ON DBMS_AQ TO txeventq_user; -- For enqueue/dequeue operations.
GRANT EXECUTE ON DBMS_TEQK TO txeventq_user; -- If using Kafka APIs

-- Optional: Administrative privileges
GRANT EXECUTE ON DBMS_AQADM TO txeventq_user; -- For creating queues
-- OR
GRANT AQ_ADMINISTRATOR_ROLE TO txeventq_user; -- For full admin control

-- Example: Create a TxEventQ queue (if admin privileges granted)
BEGIN
  DBMS_AQADM.CREATE_TRANSACTIONAL_EVENT_QUEUE(
    queue_name => 'my_txeventq',
    multiple_consumers => TRUE,
    queue_payload_type => 'JSON'
  );
  DBMS_AQADM.START_QUEUE('my_txeventq');
END;
/

[gvenzl]

Thanks @andytael,

As spoken on another channel, I'll await further information on this one.