3

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!

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.