ORACLE PLSQL Create user with trigger identified by :new username and password

Question

I'm writing an online videogame Database in SQL using ORACLE for an accademic project, and i'm trying to create a trigger that for every user that submit their information in my ACCCOUNT TABLE

CREATE TABLE ACCOUNT (
USERNAME              VARCHAR(20),
PASSWORD              VARCHAR(20)            NOT NULL,
NATIONALITY           VARCHAR(15),
CREATION DATE         DATE,
EMAIL_ACCOUNT         VARCHAR(35)            NOT NULL,
CONSTRAINT            KEYACCOUNT          PRIMARY KEY(USERNAME),
CONSTRAINT            NO_USER_CSPEC          CHECK(REGEXP_LIKE(USERNAME, '^[a-zA-Z0-9._]+$') AND USERNAME NOT LIKE '% %'),
CONSTRAINT            NO_EASY_PASS           CHECK(REGEXP_LIKE(PASSWORD, '^[a-zA-Z0-9._!#£$%&/()=?]') AND PASSWORD NOT LIKE '% %'),
CONSTRAINT            LENGHTUSER          CHECK(LENGTH(USERNAME)>3),
CONSTRAINT            LENGHTPASS          CHECK(LENGTH(PASSWORD)>5),
CONSTRAINT            FK_EMAIL               FOREIGN KEY(EMAIL_ACCOUNT) REFERENCES PERSONA(EMAIL) ON DELETE CASCADE
);

Will fire a trigger that will create a new user with the new username and password just inserted.

this is the code i tried to wrote

 CREATE OR REPLACE TRIGGER NEW_USER
 AFTER INSERT ON ACCOUNT
 FOR EACH ROW
 BEGIN
 CREATE USER :NEW.USERNAME IDENTIFIED BY :NEW.PASSWORD;
 GRANT ROLE_NAME TO :NEW.USERNAME
 END;

Why i'm tyring to do this ? Basically because i'd like to give specific view on specific row that regards only the specific user. ( imagine if, while managing your account you can access to every other row stored in the table ACCOUNT )

After creating that specific user i can create some procedure that have in input the username ( of a successfully created user ) and give back the view on that specific row.

is there a way to do this ?

Answer 1

At first, you can't use DDL statement in trigger body as a open source, you should put it in execute immediate command. And also you should pay attention to user privileges which will execute then trigger, and role which will be granted to user, are there all priveleges granted, for create session, execute statements and so on. But if I were you I'll put user opening process in separate procedure, I think it won't be so simple code, so it will be easy to edit package procedure.

You can create context for you user sessions, wrap all your table where you want to control access into views and then filter view by user context. For example you table TAB_A with many rows, in table you store column ACS_USER and wrap table to V_TAB_A , when you can control access to table via view, all user access object will use views like

select * from V_TAB_A where ACSUSER = SYS_CONTEXT('USERENV','SESSION_USER')

Answer 2

Alternatively, you can use a database stored procedure instead of a trigger to do the DDL operations.

This is a pseudo code, make necessary changes as per your requirement. You can build your logic on top of this and if you are stuck, always post a question here in SO.

Table

CREATE TABLE account_info
(
   user_name   VARCHAR (20),
   user_password   VARCHAR (20) NOT NULL
);

Procedure

CREATE OR REPLACE PROCEDURE test_procedure (
   user_name       IN account_info.user_name%TYPE,
   user_password   IN account_info.user_password%TYPE)
AS
BEGIN
   INSERT INTO account_info (user_name, user_password)
        VALUES ('ABC', 'password123');

   -- check the user exists or not, if yes proceed

   EXECUTE IMMEDIATE
      'CREATE USER ' || user_name || ' IDENTIFIED BY ' || user_password;
-- do the rest of the activities such as grant roles, privilges etc.

END;
/

Answer 3

The main problem I see here is grant to create user. You probably don't want your schema to be able to create users. So trigger (of course as other answers states this need to be execute immediate) shouldn't directly call create user. I would create procedure that create user in other schema than your working schema. That external schema would have grants to create user and your schema would have only grant to execute that one procedure from strong priviledged schema. In that case trigger will only call single procedure from external schema.

So to recap:

 CREATE OR REPLACE TRIGGER your_schema.NEW_USER
 AFTER INSERT ON ACCOUNT
 FOR EACH ROW
 BEGIN
   STRONG.CREATE_USER(:NEW.PASSWORD,:NEW.USERNAME);
 END;

CREATE OR REPLACE PROCEDURE STRONG.CREATE_USER(PASS VARCHAR2, USERNAME VARCHAR2) AS
DECLARE    
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  execute immediate 'CREATE USER ' || USERNAME || ' IDENTIFIED BY ' || PASS;
  execute immediate 'GRANT ROLE_NAME, CONNECT, RESOURCE TO ' || USERNAME; --and whatever user needs more
END;

Where STRONG user have rights to create user and your_schema has grant to execute STRONG.CREATE_USER

Additional thing. Never store passwords in plain text. Use some hash.