Problem granting user privileges via roles in Oracle 12c

oracleoracle-12cpermissionsrole

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 SYSTEM user.

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 🙂

Thank you!

Best Answer

You did not mention it, but this is quite typical when using PL/SQL.

Privileges granted through roles are disabled for named PL/SQL blocks that are defined to execute with definer's rights.

How Roles Work in PL/SQL Blocks

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.

More details you can find in the documentation referenced above.

Possible workarounds involve granting the privileges directly (as you already did) or define the PL/SQL program unit to execute with invoker's rights.

Another possibility starting with 12c, you can grant roles to PL/SQL program units. So if you create tables with the procedure called p1 using dynamic SQL, you can grant the above role to it as:

grant wertyq_usr_role to procedure p1;

Choose the one that suits your needs.

Related Question