ORACLE PLSQL创建用户，触发器由:new用户名和密码标识 [英] ORACLE PLSQL Create user with trigger identified by :new username and password

问题描述

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

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.

这是我尝试编写的代码

 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表中的所有其他行)

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 ?

推荐答案

我在这里看到的主要问题是授予 create user .您可能不希望您的架构能够创建用户.因此，触发器(当然，如其他答案所述，需要<立即执行> )不应直接调用创建用户.我将创建在您的工作模式以外的其他模式中创建用户的过程.该外部模式将授予 create user 的权限，而您的模式将仅授予从强特权模式执行该过程的权限.在这种情况下，触发器将仅从外部架构调用单个过程.

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.

所以回顾一下:

 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

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.

这篇关于ORACLE PLSQL创建用户，触发器由:new用户名和密码标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！