Back to Top

Add users to Oracle

In this topic

  1. Use geoprocessing to add a user who can create database objects
  2. Create users with Oracle tools

Oracle database administrators create user accounts in the Oracle instance and assign these accounts storage (tablespaces) and privileges.

The database administrator (sys user) can use Oracle tools to create users, a default tablespace for the user, and grant privileges to create database objects.

Alternatively, the database administrator can use the Create Database User geoprocessing tool in ArcGIS Pro or call the management.CreateDatabaseUser ArcPy function in a Python script to create a user to own data.

Database users created with this tool or function are granted the following privileges:

  • CREATE SESSION
  • CREATE SEQUENCE
  • CREATE TABLE
  • CREATE TRIGGER
  • CREATE VIEW
  • SELECT ON DBA_ROLES

Use geoprocessing to add a user who can create database objects

You can run the Create Database User tool from ArcGIS Pro or call the management.CreateDatabaseUser ArcPy function in a Python script to create a database user who can create tables, feature classes, views, triggers, and sequences.

You must be connected to the database as the Oracle sys user to run the Create Database User tool or management.CreateDatabaseUser function.

Use the Create Database User tool

Follow these steps to create a database user in Oracle using the Create Database User tool:

  1. Start ArcGIS Pro.
  2. Connect to the database or geodatabase as the Oracle sys user.
  3. Open the Create Database User tool.

    The tool is in the Geodatabase Administration toolset of the Data Management toolbox.

  4. Specify the database connection for the Input Database Connection.
  5. Provide a name for the user and schema that the tool will create.
  6. Provide a password for the database user.
  7. If you already have a role that you want this user to be a member of, specify that role.
  8. Provide the name of the tablespace you want to set as the user's default tablespace.

    If the tablespace does not already exist, the tool will create it in the Oracle default storage location. The tool creates a 400 MB tablespace.

    If you do not specify a tablespace, the Oracle default tablespace is used.

  9. Click Run.

The database now has a user who can create tables, feature classes, and views.

The new user can connect to the database and create datasets. Once the datasets exist, the data owner can grant privileges on them to other users. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.

Run a Python script

To script user creation, follow these steps:

  1. Create a text file on an ArcGIS client machine and copy one of the following blocks of code into the file.

    Alter the code to use information specific to your site.

    The examples assume you have an existing database connection file that connects as the Oracle sys user. If you do not have a connection file, create one before you run the script.

    # Name: createdatabaseuser_exampleW.py
# Description: Uses existing database connection file
# on a Windows computer to create a database user in Oracle.

# Import arcpy module
import arcpy
 
# Provide a database connection file for the Oracle sys user.
connection = "C:\\ArcGIS\connection_files\<Connection file>.sde"

# Process: Create database user that can create data and a default tablespace for the user.
arcpy.CreateDatabaseUser_management(connection, "DATABASE_USER", "dbuser", "t3mpor@rypL@ceholder1", "usertbsp")
    # Name: createdatabaseuser_exampleL.py
# Description: Uses existing database connection file
# on a Linux computer to create a database user in Oracle.

# Import arcpy module
import arcpy
 
# Provide a database connection file for the Oracle sys user.
connection = "<user>/connections/<Connection_file>.sde"

# Process: Create database user that can create data and a default tablespace for the user.
arcpy.CreateDatabaseUser_management(connection, "DATABASE_USER", "dbuser", "t3mpor@rypL@ceholder1", "usertbsp")

  2. Save the file with a .py extension.
  3. Run the script from a computer where ArcGIS Pro (Standard or Advanced) or ArcGIS Server is installed.

    For information about running Python from an ArcGIS Server machine, see ArcGIS Server and ArcPy.

The database now has a user who can create tables, feature classes, and views.

The new user can connect to the database and create datasets. Once the datasets exist, the data owner can grant privileges on them to other users. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.

Create users with Oracle tools

Alternatively, you can use Oracle tools to create database users. For example, you could create a database user who has different privileges than those granted by the Create Database User tool or has no privileges granted directly to the user.

For instructions to use Oracle tools to create database users, consult the Oracle documentation.

For information about what privileges are required for different user types, see Privileges for geodatabases in Oracle or Privileges for using ArcGIS with an Oracle database.

Feedback on this topic?

In this topic
  1. Use geoprocessing to add a user who can create database objects
  2. Create users with Oracle tools