I'm starting to learn Oracle 12c so please excuse my bad terminology. I wrote this basic script to set up a user and privileges:
CREATE USER wertyq_db_usr IDENTIFIED BY justatest ACCOUNT UNLOCK DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT"; ALTER USER wertyq_db_usr QUOTA 100M ON USERS; GRANT RESOURCE, CONNECT TO wertyq_db_usr; GRANT EXECUTE ON CTX_DDL TO wertyq_db_usr; GRANT EXECUTE ON "CTXSYS"."CTX_DDL" TO wertyq_db_usr; GRANT UNLIMITED TABLESPACE TO wertyq_db_usr; CREATE ROLE wertyq_usr_role; GRANT CREATE PROCEDURE TO wertyq_usr_role; GRANT CREATE TRIGGER TO wertyq_usr_role; GRANT CREATE SESSION TO wertyq_usr_role; GRANT CREATE VIEW TO wertyq_usr_role; GRANT wertyq_usr_role TO wertyq_db_usr;
The goal is to create a user and a role, then grant privileges to the role, then assign the role to the user so the user inherits the privileges. I should also mention that I can connect to the database as
However, a third party application I am also using, which should connect to my Oracle database using this user created above, it behaves like the user doesn't have any privileges and it won't create any tables/views/ etc in the database. So I grant the privileges directly to the user and the third party application functions correctly. So I remove the privileges from the user and grant the role to the user again, and the third party application again won't create tables/views/ etc.
So I am wondering if the third party application has a bug, or if an Oracle user doesn't inherit privileges from roles in the manner I thought it would? I would appreciate your insights :)