ORACLE PLSQL创建用户，触发器由：new用户名和密码标识

Question

我正在使用ORACLE为SQL项目编写在线视频游戏数据库，而我正在尝试为每个在我的ACCCOUNT表格中提交其信息的用户创建一个触发器

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
);

将触发一个触发器，该触发器将使用刚刚插入的新用户名和密码创建一个新用户。

这是我尝试编写的代码

 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;

为什么我要这样做呢？基本上是因为我想在仅涉及特定用户的特定行上提供特定视图。（想象一下，如果在管理帐户时可以访问表ACCOUNT中存储的所有其他行）

创建该特定用户后，我可以创建一些过程，并在其中输入用户名（成功创建用户的用户名），并返回该特定行上的视图。

有没有办法做到这一点 ？

Answer 1

我在这里看到的主要问题是授予create user。您可能不希望您的架构能够创建用户。因此，触发器（当然是其他答案指出需要这样做execute immediate）不应直接调用create user。我将创建在您的工作模式以外的其他模式中创建用户的过程。该外部模式将授予create user您的权限，而您的模式将仅授予从强特权模式执行该过程的权限。在这种情况下，触发器将仅从外部模式调用单个过程。

因此，回顾一下：

 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;

STRONG用户有权创建用户，而your_schema有权执行 STRONG.CREATE_USER

额外的东西。切勿以纯文本形式存储密码。使用一些哈希。