Add users to Oracle

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 AllSource 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

Add a user who can create database objects

You can run the Create Database User tool from ArcGIS AllSource 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 AllSource.
2. Connect to the database or geodatabase as the 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.

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 AllSource (Standard or Advanced) or ArcGIS Server is installed.

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

Your database now has a user who can create tables and feature classes.

Once the tables and feature classes exist, the data owner can grant privileges on the datasets to other users. See Grant and revoke dataset privileges in databases and enterprise geodatabases for instructions.

Create users with Oracle tools

To 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, you can use Oracle tools to do that.

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