Create user from string variables in a PL/SQL block

Question

I use Oracle XE for the sole purpose of developing PHP applications and version 11g has apparently lost the GUI tool to manage users which 10g used to have so I'd like to prepare a code snippet to create users from command line. I'm trying to define variables so I don't need to type the same user name 16 times but I can't get the syntax right:

DECLARE
    my_user VARCHAR2(30) := 'foo';
    my_password VARCHAR2(9) := '1234';
BEGIN
    CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE users;

    GRANT CONNECT, RESOURCE TO my_user;
    GRANT CREATE DATABASE LINK TO my_user;
    GRANT CREATE MATERIALIZED VIEW TO my_user;
    GRANT CREATE PROCEDURE TO my_user;
    GRANT CREATE PUBLIC SYNONYM TO my_user;
    GRANT CREATE ROLE TO my_user;
    GRANT CREATE SEQUENCE TO my_user;
    GRANT CREATE SYNONYM TO my_user;
    GRANT CREATE TABLE TO my_user;
    GRANT CREATE TRIGGER TO my_user;
    GRANT CREATE TYPE TO my_user;
    GRANT CREATE VIEW TO my_user;

    GRANT SELECT_CATALOG_ROLE TO my_user;
    GRANT SELECT ANY DICTIONARY TO my_user;
END;
/

        CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE users;
        *
ERROR en línea 5:
ORA-06550: line 5, column 2:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge

Is it forbidden to use a CREATE USER statement from within a PL/SQL block or I simply made a silly typo? Is it mandatory to use SQL*Plus variables?

score 12 · Accepted Answer · edited Feb 23 '19 at 01:17

12

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

The above error is because of the fact that you are using DDL inside PL/SQL. You cannot do that. You must (ab)use EXECUTE IMMEDIATE to issue DDL statements in PL/SQL.

For example,

SQL> DECLARE
  2    my_user     VARCHAR2(30) := 'foo';
  3    my_password VARCHAR2(9)  := '1234';
  4  BEGIN
  5    EXECUTE IMMEDIATE 'CREATE USER '||my_user||' IDENTIFIED BY '||my_password;
  6    EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO '||my_user;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> conn foo/1234@pdborcl
Connected.
SQL> SHOW USER
USER is "FOO"

Quick reference from documentation,

Executing DDL and SCL Statements in PL/SQL

Only dynamic SQL can execute the following types of statements within PL/SQL program units:

Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE

Session control language (SCL) statements such as ALTER SESSION and SET ROLE

The TABLE clause in the SELECT statement

On a side note,

Creating users and granting privileges are usually database administration tasks taken care by the DBA. It is not a frequent activity done via PL/SQL program. DBA creates the users and grants the necessary privileges as a one time activity.

edited Feb 23 '19 at 01:17

László Székely-Tóth

3
2

answered Nov 16 '15 at 09:31

Lalit Kumar B

47,486
13
97
124

Loud and clear, thank you very much. I guess this helps to explain why SQL*Plus variables are such a popular choice. – Álvaro González Nov 16 '15 at 09:44
@ÁlvaroGonzález You're welcome! Please note that, these are database administration tasks and not done usually via PL/SQL program. You create the user and grant privileges only once. The DBA needs to do it, usually not by a PL/SQL developer. I don't know your exact requirement, so you might have a use case to do so though ;-) – Lalit Kumar B Nov 16 '15 at 09:46
My exact requirement is that there is no DBA in town but I still need Oracle ;-) – Álvaro González Nov 16 '15 at 09:59
@ÁlvaroGonzález LOL, good luck with that. I would prefer SQL*Plus scripts, declare the variables once and use them throughout your script. You could put entire DDL inside a `.sql` file and pass the variables as arguments. Please mark it as answered, would help others. – Lalit Kumar B Nov 16 '15 at 10:33
I would like to emphasize, since it isn't mentioned with these words, it is a security risk to create users like this depending on where this PL/SQL code resides. If the code is once off code only accessible to you when you're running DBA tasks, then this should be fine. If, however, this code is part of an application somewhere and is programmatically run from a call up some call chain of routines... this is very dangerous. There could be SQL injection with `my_user` and `my_password` or simply unwanted user creation. – Jason Aug 28 '21 at 16:04

Create user from string variables in a PL/SQL block

1 Answers1

Linked

Related