CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
Paris Karagiannopoulos
Paris Karagiannopoulos

Reputation: 591

Can I create a user inside a trigger?

I was wondering if I can create a user inside a trigger. I am getting errors although I think my code is syntactically correct.

Here is the code

CREATE OR REPLACE TRIGGER addUser
BEFORE INSERT
ON PLSQL_TEST_USERS
FOR EACH ROW
DECLARE
  n VARCHAR2(20) := :new.name;
  p VARCHAR2(20) := :new.password;
BEGIN
  dbms_output.put_line(n);
  EXECUTE IMMEDIATE ('CREATE USER n IDENTIFIED BY p');
END;
/

So is it possible to do this?

Here are the errors I am getting:

INSERT INTO PLSQL_TEST_USERS VALUES (1, 'rob', 'asdf')
Error report -
SQL Error: ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYSTEM.ADDUSER", line 6
ORA-04088: error during execution of trigger 'SYSTEM.ADDUSER'
04092. 00000 - "cannot %s in a trigger"
*Cause: A trigger attempted to commit or rollback.
*Action: Rewrite the trigger so it does not commit or rollback.

Upvotes: 0

Views: 840

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

No, you cannot do this correctly.

You cannot commit inside a trigger (generally, I'll discuss the exception below). DDL like CREATE USER issues two implicit commits (one before the statement and one after the statement) so you cannot put DDL in a trigger.

An exception to the rule about committing inside a trigger occurs if you declare the trigger to use an autonomous transaction. But this doesn't solve the problem correctly for a few reasons. First off, an autonomous transaction is, as the name says, autonomous so it will be committed even if the triggering statement rolls back. That means that if the INSERT statement successfully inserts a row but that change is rolled back, the autonomous transaction remains committed so you'd end up with a user being created but no row in the plsql_test_users table. For write consistency reasons, Oracle may also internally rollback and re-execute a statement which would cause the autonomous transaction to be executed twice, the second of which would fail because the user already existed. Autonomous transactions should really only be used for cases where you want to log information whether or not the underlying change is successful (i.e. logging attempted logins or password changes so that you can detect attacks even if an unsuccessful login or password change doesn't change the state of the data).

You could have your trigger call dbms_job.submit to submit a background job that will run shortly after the statement commits (if it does commit) which actually creates the user. That will involve a small delay between the row being added to the table and the user being created but it will at least be transactionally correct (the delay could grow larger depending on things like how many users you're trying to create and how many background jobs you allow). Unfortunately, you have to use the older dbms_job package rather than the newer dbms_scheduler package to submit the job because there newer package contains implicit commits.

If you do go down the background job path, be aware that statements passed to EXECUTE IMMEDIATE cannot reference values in local variables-- those variables aren't in scope when the dynamic SQL statement is executed. When you write

EXECUTE IMMEDIATE 'CREATE USER n IDENTIFIED BY p';

n and p don't refer to the local variables you've defined, they are the literal identifier. This creates a user "n" with a single-character password "p". Assuming you want to use the username and password from the table, you'd need to use them while assembling the dynamic SQL statement. Something like

EXECUTE IMMEDIATE 
  'CREATE USER ' || :new.name ||
  ' IDENTIFIED BY ' || :new.password;

And this all begs the question of why you're trying to store information in two places. It seems highly unlikely that you really want to have a table plsql_test_users with a bunch of usernames & passwords that are also Oracle users. You either want Oracle to handle authentication or you want to have your application handle authentication, you don't want each to think that it is handling authentication (what happens when passwords change in one but not the other, for example). If your application is going to handle authentication, you'd never store the password (or the encrypted password). You'd store the hash of the password with a salt (in a RAW column). And then you'd compute and verify the hash when the user provides the password at login time and compare the hashes.

Upvotes: 4

Related Questions