3.4.5 Create New Users for On-Premises Oracle Database

The pyquser.sql script is a convenient way to create a new OML4Py user for on on-premises database.

About the pyquser.sql Script

The pyquser.sql script is a component of the on-premises OML4Py server installation. The script is in the server directory of the installation. The sysdba privilege is required to run the script.

The pyquser.sql script grants the new user the required on-premises Oracle database privileges and, optionally, grants the PYQADMIN database role. The PYQADMIN role is required for creating and managing scripts in the OML4Py script repository for use in Embedded Python Execution.

The pyquser.sql script takes the following five positional arguments:

• Username
• User's permanent tablespace
• User's temporary tablespace
• Permanent tablespace quota
• PYQADMIN role

When you run the script, it prompts you for a password for the user.

Create a New User

To use the pyquser.sql script, go the server subdirectory of the directory that contains the extracted OML4Py server installation files. Run the script as a database administrator.

The following examples use SQL*Plus and the sysdba user to run the pyquser.sql script.

Example 3-1 Creating New Users

This example creates the user oml_user with the permanent tablespace USERS with an unlimited quota, the temporary tablespace TEMP, and grants the PYQADMIN role to the oml_user.

sqlplus / as sysdba
@pyquser.sql oml_user USERS TEMP unlimited pyqadmin

Enter value for password: <type your password>

For a pluggable database:

sqlplus / as sysdba
alter session set container=<PDBNAME>
@pyquser.sql oml_user USERS TEMP unlimited pyqadmin

The output is similar to the following:

SQL> @pyquser.sql oml_user USERS TEMP unlimited pyqadmin
Enter value for password: welcome1
old   1: create user &&1 identified by &password
new   1: create user oml_user identified by welcome1
old   2: default tablespace &&2
new   2: default tablespace USERS
old   3: temporary tablespace &&3
new   3: temporary tablespace TEMP
old   4: quota &&4 on &&2
new   4: quota unlimited on USERS

User created.

old   4:     'create procedure, create mining model to &&1';
new   4:     'create procedure, create mining model to pyquser';
old   6:   IF lower('&&5') = 'pyqadmin' THEN
new   6:   IF lower('pyqadmin') = 'pyqadmin' THEN
old   7:     execute immediate 'grant PYQADMIN to &&1';
new   7:     execute immediate 'grant PYQADMIN to pyquser';

PL/SQL procedure successfully completed.

This example creates the user oml_user2 with 20 megabyte quota on the USERS tablespace, the temporary tablespace TEMP, and without the PYQADMIN role.

sqlplus / as sysdba
@pyquser.sql oml_user2 USERS TEMP 20M FALSE

Enter value for password: <type your password>