Insufficient Privileges on CTXSYS

Question

I'm working in a 19c Autonomous Database trying to create a user “analyst” to do text and entity extraction of documents. When granting CTXSYS privileges, some work and some fail. From lines 7-14 below, 4 grants succeed and 4 grants fail due to insufficient privileges. Are there changes I need to make to my admin account to enable me to grant the privileges to analyst?

create user analyst identified by XXX default tablespace analyst_ts;

grant create session, create table , create view, create type, create procedure, create sequence to analyst;

grant create job to analyst;

grant ctxapp to analyst;

grant select on V$PARAMETER to analyst;

grant execute on ctx_entity to analyst;

GRANT EXECUTE ON CTXSYS.CTX_CLS to analyst; --failed ora-01031 insufficient privileges

GRANT EXECUTE ON CTXSYS.CTX_DDL to analyst;

GRANT EXECUTE ON CTXSYS.CTX_DOC to analyst; --failed ora-01031 insufficient privileges

GRANT EXECUTE ON CTXSYS.CTX_OUTPUT to analyst;

GRANT EXECUTE ON CTXSYS.CTX_QUERY to analyst; --failed ora-01031 insufficient privileges

GRANT EXECUTE ON CTXSYS.CTX_REPORT to analyst; --failed ora-01031 insufficient privileges

GRANT EXECUTE ON CTXSYS.CTX_THES to analyst;

grant execute on CTXSYS.CTX_ULEXER to analyst;

grant execute on dbms_crypto to analyst;

grant alter session to analyst;

I've tried altering permissions of the admin account, but I'm not sure what changes to make.

Answer 1

The four packages that errored are packages that by default have execute privs granted to PUBLIC. Since the grant to public is not with grant option, nobody can grant it to someone else unless they have the GRANT ANY OBJECT PRIVILEGE system privilege. But there's no reason to be trying to grant them, as everyone can execute those packages already.

The others that did not error are not by default granted to public, so your admin account must have been given exec privs on them with grant option, allowing you to further grant those to others. But even those grants are unnecessary, because by granting the CTXAPP role, you implicitly given exec privs on all those packages.

Unless "ANALYST" will have stored PL/SQL programs needing those privileges (which won't honor roles), I would just grant CTXAPP and omit all the specific CTX-related object grants until you find that they are needed for some reason and not already included in the CTXAPP role. But even more importantly, consult Oracle documentation on the proper model for granting permissions for this particular feature. It is unlikely it was intended that folks would be granting privs on it piece-meal and there may be specific guidance for this product.