0

My database is an XE 18. My user has create user and I can create an user in SQL Plus, using this :

ALTER SESSION SET "_ORACLE_SCRIPT" = true;
CREATE USER auxiliar IDENTIFIED BY auxiliar2020 ;

Then I create a package with a function that should be create users dynamically and the lines are above:

EXECUTE IMMEDIATE 'alter session set "_ORACLE_SCRIPT"=true';
vSql := 'CREATE USER :1 IDENTIFIED BY ":2" ' ;
EXECUTE IMMEDIATE vSql USING vNome_Usuario,vSenha_Usuario;

I get this error:

Relatório de erros -
ORA-65096: nome de atribuição ou de usuário comum inválido
ORA-06512: em "TOKEN.PRC_CRIA_USUARIO", line 3
ORA-06512: em line 3
65096. 00000 - "invalid common user or role name"
*Cause: An attempt was made to create a common user or role with a name that was not valid for common users or roles. In addition to the usual rules for user and role names, common user and role names must consist only of ASCII characters, and must contain the prefix specified in common_user_prefix parameter.
*Action: Specify a valid common user or role name.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Arlen Andrade
  • 1
  • 2
    Apparently you executed your code within a container database, using an invalid username. Make sure you are executing your code in the right DB (application code should always be in a pluggable DB, not the container), and that you are following the rules for the database in which you are creating the user. Users created in a container DB (i.e. common users) by default must have names that begin with a prefix of "C##". – pmdba Sep 14 '20 at 17:57
  • The 'Create user' don´t works only with execute immediate, if i put this on sql plus i can create the user without C## : alter session set "_ORACLE_SCRIPT"=true; / CREATE USER usuariostackoverflow IDENTIFIED BY auxiliar2020 ; / – Arlen Andrade Sep 14 '20 at 18:05

1 Answers1

2

You cannot use bind variable in such scenarios,

Could you try like,

DECLARE
  in_user     VARCHAR2(30) := 'some_user';
  in_password VARCHAR2(9)  := 'changeMe';
BEGIN
  EXECUTE IMMEDIATE 'CREATE USER '||in_user||' IDENTIFIED BY '||in_password;
  EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO '||in_user;
END;
/
Sujitmohanty30
  • 3,256
  • 2
  • 5
  • 23
  • I could have not explained in better words than AskTom, here is a link which exactly tells when and where we can use `bind variables` https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:227413938857 – Sujitmohanty30 Sep 14 '20 at 19:01
  • I created a proc without binds to test create or replace NONEDITIONABLE PROCEDURE PRC_CRIA_USUARIO iS BEGIN EXECUTE IMMEDIATE 'CREATE USER JOAOZINHO IDENTIFIED BY "ASDEWWEFFDS" '; END; If i execute immediate in a pl/sql block it works, but when i put the execute immediate on my proc, don´t work. When I call this proc i get this error : Erro a partir da linha : 25 no comando - BEGIN token.PRC_CRIA_USUARIO(); END; Relatório de erros - ORA-01031: privilégios insuficientes ORA-06512: em "TOKEN.PRC_CRIA_USUARIO", line 3 ORA-06512: em 01031. 00000 - "insufficient privileges" – Arlen Andrade Sep 14 '20 at 19:02
  • And i have the grants, i create this user using EXECUTE IMMEDIATE in an pl/SQL block – Arlen Andrade Sep 14 '20 at 19:02
  • This is a typical problem which you will find many answers on SO. You should use `sys` or `system` to do any kind of administration operations. However if you are trying to create an user by logging into another normal user ,you may have the grants via a role but to execute something like ` create user...` in PLSQL you need to have the `grant create user ...` directly to the user you are logged into .... – Sujitmohanty30 Sep 14 '20 at 19:13
  • See this https://dba.stackexchange.com/questions/158079/problem-granting-user-privileges-via-roles-in-oracle-12c/158081#158081 – Sujitmohanty30 Sep 14 '20 at 19:15