Secure

40.4.1 Create an Oracle Machine Learning for SQL User

An OML4SQL user is a database user account that has privileges for performing machine learning activities.

Example 40-6 shows how to create a database user. Example 40-7 shows how to assign machine learning privileges to the user.

Note:

To create a user for the OML4SQL examples, you must run two configuration scripts as described in Install the OML4SQL Examples.

Example 40-6 Creating a Database User in SQL*Plus

Log in to SQL*Plus with system privileges.

 
Enter user-name: sys as sysdba
Enter password: password

To create a user named oml_user, type these commands. Specify a password of your choosing.
```
CREATE USER oml_user IDENTIFIED BY password
       DEFAULT TABLESPACE USERS
       TEMPORARY TABLESPACE TEMP
       QUOTA UNLIMITED ON USERS;
Commit;
```
The USERS and TEMP tablespaces are included in Oracle Database. USERS is used mostly by demo users; it is appropriate for running the examples described in About the OML4SQL Examples. TEMP is the temporary tablespace that is shared by most database users.

Note:

Tablespaces for OML4SQL users must be assigned according to standard DBA practices, depending on system load and system resources.

To log in as oml_user, enter the following.


CONNECT oml_user
Enter password: password

40.4.1.1 Grant Privileges for Oracle Machine Learning for SQL

The CREATE MINING MODEL is a privilege that you must have to create and perform operations on your model. Some other machine learning privileges can be assigned by issuing GRANT statements.

You must have the CREATE MINING MODEL privilege to create models in your own schema. You can perform any operation on models that you own. This includes applying the model, adding a cost matrix, renaming the model, and dropping the model.

The GRANT statements in the following example assign a set of basic machine learning privileges to the oml_user account. Some of these privileges are not required for all machine learning activities, however it is prudent to grant them all as a group.

Additional system and object privileges are required for enabling or restricting specific machine learning activities.

The following table lists the system privileges required for running the OML4SQL examples.

Table 40-2 System Privileges Granted by dmshgrants.sql to the OML4SQL User

Privilege	Allows the OML4SQL User To
`CREATE SESSION`	Log in to a database session
`CREATE TABLE`	Create tables, such as the settings tables for `CREATE_MODEL`
`CREATE VIEW`	Create views, such as the views of tables in the `SH` schema
`CREATE MINING MODEL`	Create OML4SQL models
`EXECUTE ON ctxsys.ctx_ddl`	Run procedures in the `ctxsys.ctx_ddl` PL/SQL package; required for text mining

Example 40-7 Privileges Required for Machine Learning

This example grants the required privileges to the user oml_user.

GRANT CREATE SESSION TO oml_user;
GRANT CREATE TABLE TO oml_user;
GRANT CREATE VIEW TO oml_user;
GRANT CREATE MINING MODEL TO oml_user;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO oml_user;

READ or SELECT privileges are required for data that is not in your schema. For example, the following statement grants SELECT access to the sh.customers table.

GRANT SELECT ON sh.customers TO oml_user;

40.4.2 System Privileges for Oracle Machine Learning for SQL

A system privilege confers the right to perform a particular action in the database or to perform an action on a type of schema objects. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges.

You can perform specific operations on machine learning models in other schemas if you have the appropriate system privileges. For example, CREATE ANY MINING MODEL enables you to create models in other schemas. SELECT ANY MINING MODEL enables you to apply models that reside in other schemas. You can add comments to models if you have the COMMENT ANY MINING MODEL privilege.

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege.

The system privileges listed in the following table control operations on machine learning models.

Table 40-3 System Privileges for Oracle Machine Learning for SQL

System Privilege	Allows you to....
`CREATE MINING MODEL`	Create machine learning models in your own schema.
`CREATE ANY MINING MODEL`	Create machine learning models in any schema.
`ALTER ANY MINING MODEL`	Change the name or cost matrix of any machine learning model in any schema.
`DROP ANY MINING MODEL`	Drop any machine learning model in any schema.
`SELECT ANY MINING MODEL`	Apply a machine learning model in any schema, also view model details in any schema.
`COMMENT ANY MINING MODEL`	Add a comment to any machine learning model in any schema.
`AUDIT_ADMIN` role	Generate an audit trail for any machine learning model in any schema. (See Oracle Database Security Guide for details.)

Example 40-8 Grant System Privileges for Oracle Machine Learning for SQL

The following statements allow oml_user to score data and view model details in any schema as long as SELECT access has been granted to the data. However, oml_user can only create models in the oml_user schema.

GRANT CREATE MINING MODEL TO oml_user;
GRANT SELECT ANY MINING MODEL TO oml_user;

The following statement revokes the privilege of scoring or viewing model details in other schemas. When this statement is run, oml_user can only perform machine learning activities in the oml_user schema.

REVOKE SELECT ANY MINING MODEL FROM oml_user;

Related Topics

40.4.3 Object Privileges for Oracle Machine Learning for SQL Models

Learn about machine learning object privileges.

An object privilege confers the right to perform a particular action on a specific schema object. For example, the privilege to delete rows from the SH.PRODUCTS table is an example of an object privilege.

You automatically have all object privileges for schema objects in your own schema. You can grant object privilege on objects in your own schema to other users or roles.

The object privileges listed in the following table control operations on specific machine learning models.

Table 40-4 Object Privileges for Oracle Machine Learning for SQL Models

Object Privilege	Allows you to....
`ALTER MINING MODEL`	Change the name or cost matrix of the specified machine learning model object.
`SELECT MINING MODEL`	Apply the specified machine learning model object and view its model details.

Example 40-9 Grant Object Privileges on Oracle Machine Learning for SQL Models

The following statements allow oml_user to apply the model testmodel to the sales table, specifying different cost matrixes with each apply. The user oml_user can also rename the model testmodel. The testmodel model and sales table are in the sh schema, not in the oml_user schema.

GRANT SELECT ON MINING MODEL sh.testmodel TO oml_user;
GRANT ALTER ON MINING MODEL sh.testmodel TO oml_user;
GRANT SELECT ON sh.sales TO oml_user;

The following statement prevents oml_user from renaming or changing the cost matrix of testmodel. However, oml_user can still apply testmodel to the sales table.

REVOKE ALTER ON MINING MODEL sh.testmodel FROM oml_user;