Oracle : Is it possible to create a Role in a Trigger?

Question

I have a separate table with some data and I would like to a create a new role each time a new row is inserted in that table.

I created the following trigger:

create or replace 
TRIGGER TestTrigger
BEFORE INSERT ON TestTable
REFERENCING OLD AS OLD NEW AS NEW 

FOR EACH ROW BEGIN 
    create role :New.RoleName;
END;

but the trigger doesn't compile. I have the error: PLS-00103: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Is that possible ?

I suspect you can't use DDL in a trigger in this way. You could try using an Execute Immediate in order to execute your CREATE, but I have no idea if Roles can be created like this. — Drumbeg
You can create a role with execute immediate; but not inside a trigger because you can't commit inside a trigger, and DDL does an implicit commit. You can get around that by making it autonomous, or by submitting a job, but this is getting a bit convoluted. Seems an odd requirement. If you have to create roles from your application, why not only create a role - what does duplicating it in your table add? — Alex Poole
The reason I'm duplicating them is that for each role I need some additional information : Description, Creation Date, ID, etc. And since I can not add these columns to the "default" role table in Oracle, I'm creating a separate table to handle the additional information. However I still need to care about coherence between my table and oracle roles one. — Thomas Carlton
Hmm, even if you can do it from a trigger, I'd rather create a job to parse the table and create the roles... for security purposes. — Sebas

APC APC · Accepted Answer · 2013-12-23T07:41:58

We cannot natively execute DDL in any form of PL/SQL. including triggers. To do that we need to use dynamic SQL.

Triggers have an additional wrinkle: they are fired as part of the transaction, and they have a limitation that forbids us from issuing a commit inside their body. In Oracle, any DDL command issues two commits, one before and one after the DDL statement is executed. So, to execute DDL in a trigger we must use the autonomous_transaction pragma, which means the DDL runs in a separate, nested transaction.

create or replace TRIGGER TestTrigger
    BEFORE INSERT ON TestTable
    REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
declare
    pragma autonomous_transaction;
BEGIN 
    execute immediate 'create role '|| :New.RoleName;
END;

Autonomous transaction are one of those constructs which are easy for us to misuse and sabotage our own applications. In your scenario the snag is that the CREATE ROLE can succeed in its transaction bubble whilst the INSERTT into TestTable fails; such is the meaning of "autonomous transaction". So you are still not guaranteed "coherence between [your] table and oracle roles one".

A better solution would be to wrap both statements into a procedural call, rather than attempting to trick DML into doing something it is not supposed to do.

create or replace procedure create_role
     ( p_role_name in user_roles.role%type
       , p_desc in testtable.description%type )
is
    pragma autonomous_transaction;
begin 
    insert into testtable
        ( id, creationdate, rolename, description) 
    values
        ( some_seq.nextval, sysdate, p_role_name, p_desc );
    execute immediate 'create role '|| p_role_name;
end;

Oracle : Is it possible to create a Role in a Trigger?

1 Answers