ArcGIS Help 10.2 - Create Database User (Data Management)

License Level:Basic Standard Advanced

Summary

The Create Database User tool creates a database user with privileges sufficient to create data in the database.

Usage

This tool can be used only with Oracle, Microsoft SQL Server, or PostgreSQL. (Not supported with Windows Azure SQL Database.)
For Oracle and SQL Server, if an operating system login already exists, the Create Database User tool can add that login as a user to the specified database.
You cannot create a database user for a Windows group.

Users created in the database have the following privileges granted to them:

DBMS	Privileges
Oracle	CREATE SESSION CREATE SEQUENCE CREATE TRIGGER CREATE VIEW CREATE TABLE SELECT ON DBA_ROLES
PostgreSQL	USAGE on the sde schema if the user is created in a geodatabase or a database that has the ST_Geometry type installed SELECT, INSERT, UPDATE, DELETE on the public.geometry_columns table and SELECT on the public.spatial_ref_systable if the PostGIS geometry type is installed
SQL Server	CREATE TABLE CREATE PROCEDURE CREATE VIEW VIEW DEFINITION

DBMS

Privileges

Oracle

CREATE SESSION

CREATE SEQUENCE

CREATE TRIGGER

CREATE VIEW

CREATE TABLE

SELECT ON DBA_ROLES

PostgreSQL

USAGE on the sde schema if the user is created in a geodatabase or a database that has the ST_Geometry type installed

SELECT, INSERT, UPDATE, DELETE on the public.geometry_columns table and SELECT on the public.spatial_ref_systable if the PostGIS geometry type is installed

SQL Server

CREATE TABLE

CREATE PROCEDURE

CREATE VIEW

VIEW DEFINITION

If the login does not exist in the SQL Server instance or PostgreSQL database cluster, the Create Database User tool adds the login, creates a user in the database specified for the Input Database, and creates a schema for the user in the database. The specified database is set as the user's default database in SQL Server.
If the login already exists in the SQL Server instance, the Create Database User tool adds the user to the database you specify for the Input Database and creates a matching schema. The user's default database is not changed in SQL Server.
If the login already exists in the PostgreSQL database cluster, the Create Database User tool creates a matching schema in the database you specify for the Input Database.
You cannot create a user named sde with this tool. The sde user is a geodatabase administrator user and requires more privileges than the Create Database User tool grants.
You cannot use delimeters, such as double-quote marks, when specifying a user name. Therefore, the user name can only contain characters supported by the underlying database management system when provided without delimeters.

Syntax

CreateDatabaseUser_management (input_database, {user_authentication_type}, user_name, {user_password}, {role}, {tablespace_name})

Parameter	Explanation	Data Type
input_database	Specify the connection file to a database or enterprise geodatabase in Oracle, PostgreSQL, or SQL Server. Be sure the connection file connects directly to the database (no ArcSDE service), and that the connection is made as a database administrator user. When connecting to Oracle, you must connect as the sys user.	Workspace
user_authentication_type (Optional)	Use this only if an operating system login exists for which you want to create a database user. Only enabled for SQL Server and Oracle databases. DATABASE_USER —Create a database-authenticated user. This is the default. If your DBMS is not configured to allow database authentication, do not use this option. OPERATING_SYSTEM_USER —Create an operating system-authenticated user. The corresponding login must already exist. If your DBMS is not configured to allow operating system authentication, do not use this option.	Boolean
user_name	Type a name for the new database user. If you chose to create a database user for an operating system login, the user name must match the login name.	String
user_password (Optional)	Type a password for the new user. The password policy of the underlying database will be enforced. If you chose to create a database user for an operating system login, no input is required.	Encrypted String
role (Optional)	If you want to add the new user to an existing database role, type the name of the role.	String
tablespace_name (Optional)	When creating a user in an Oracle database, type the name of the tablespace to be used as the default tablespace for the user. You can specify a preconfigured tablespace, or, if the tablespace does not already exist, it will be created in the Oracle default storage location with its size set to 400 MB. If no tablespace is specified, the user's default tablespace will be set to the Oracle default tablespace.	String

Code Sample

CreateUser example 1

Uses a predefined database connection file (oracledb1.sde) to create a database user in Oracle and creates a default tablespace (sdetbs) for the user

#Import arcpy module
import arcpy

CreateDatabaseUser_management("C:/myconnections/oracledb1.sde", "DATABASE_USER", "map", "Pam987", "sdetbs")

CreateUser example 2

Creates an input workspace (pgconn.sde) in the default ArcGIS for Desktop connection location, creates a database login role and schema in PostgreSQL

#Import arcpy module
import arcpy

CreateDatabaseConnection_management("Database Connections", "pgconn.sde", "POSTGRESQL", myserver, mypgdb, "DATABASE_AUTH", "ela", "3L@pwd", "SAVE_USERNAME")

CreateDatabaseUser_management("C:/connections/pgconn.sde", "DATABASE_USER", "dataowner", "N0look")

CreateUser example 3

Creates a database user mapped to an existing operating system login (mynet\vorhoos) in SQL Server and uses a predefined database connection file (connection_ssi.sde)

#Import arcpy module
import arcpy

CreateDatabaseUser_management("C:/gdbconnections/connection_ssi.sde", "OPERATING_SYSTEM_USER", "mynet\\vorhoos", "", "")

CreateUser example 4

The following stand-alone script allows you to provide information specific to your site using options to create a database user:

"""
Name: create_database_user.py
Description: Provide connection information to a database user.
Type create_database_user.py -h or create_database_user.py --help for usage
Author: Esri
"""

# Import system modules
import arcpy, os, optparse, sys


# Define usage and version
parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for 10.1 release")

#Define help and options
parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, or POSTGRESQL.")                   
parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
parser.add_option ("--auth", dest="Account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
parser.add_option ("-U", dest="Dbms_admin", type="string", default="", help="DBMS administrator user")
parser.add_option ("-P", dest="Dbms_admin_pwd", type="string", default="", help="DBMS administrator password")
parser.add_option ("--utype", dest="user_type", type ="choice", choices=['DATABASE_USER', 'OPERATING_SYSTEM_USER'], default='DATABASE_USER', help="Authentication type options (case-sensitive):  DATABASE_USER, OPERATING_SYSTEM_USER.  Default=DATABASE_USER")
parser.add_option ("-u", dest="dbuser", type="string", default="", help="database user name")
parser.add_option ("-p", dest="dbuser_pwd", type="string", default="", help="database user password")
parser.add_option ("-r", dest="role", type="string", default="", help="role to be granted to the user")
parser.add_option ("-t", dest="Tablespace", type="string", default="", help="Tablespace name")
# Check if value entered for option
try:
	(options, args) = parser.parse_args()

	#Check if no system arguments (options) entered
	if len(sys.argv) == 1:
		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
		parser.print_help()
		sys.exit(3)

	#Usage parameters for spatial database connection
	database_type = options.Database_type.upper()
	instance = options.Instance
	database = options.Database.lower()	
	account_authentication = options.Account_authentication.upper()
	dbms_admin = options.Dbms_admin
	dbms_admin_pwd = options.Dbms_admin_pwd
	dbuser = options.dbuser
	dbuser_pwd = options.dbuser_pwd	
	tablespace = options.Tablespace
	user_type = options.user_type
	role = options.role

	
	if (database_type == "SQLSERVER"):
		database_type = "SQL_SERVER"
	
	if( database_type ==""):	
		print " \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified.")
		parser.print_help()
		sys.exit(3)		
	
	if(database_type == "SQL_SERVER"):
		if( account_authentication == "DATABASE_AUTH" and dbms_admin == ""):
			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified with database authentication")
			sys.exit(3)
		if( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != ""):
			print "\nWarning: %s\n" % ("Ignoring DBMS administrator specified when using operating system authentication...")	
	else:		
		if( dbuser.lower() == ""):
			print "\n%s: error: %s\n" % (sys.argv[0], "Database user must be specified.")
			sys.exit(3)		
		if( dbms_admin == ""):
			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified!")
			sys.exit(3)

	if ( user_type == "DATABASE_USER" and (dbuser =="" or dbuser_pwd =="")):
		print " \n%s: error: \n%s\n" % (sys.argv[0], "To create database authenticated user, user name and password must be specified!")
		parser.print_help()
		sys.exit(3)	

	# Get the current product license
	product_license=arcpy.ProductInfo()
	
	# Checks required license level
	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
		print "\n" + product_license + " license found!" + "  Creating a user in an enterprise geodatabase or database requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
		sys.exit("Re-authorize ArcGIS before creating a database user.")
	else:
		print "\n" + product_license + " license available!  Continuing to create..."
		arcpy.AddMessage("+++++++++")

	# Local variables
	instance_temp = instance.replace("\\","_")
	instance_temp = instance_temp.replace("/","_")
	instance_temp = instance_temp.replace(":","_")
	Conn_File_NameT = instance_temp + "_" + database + "_" + dbms_admin   

	if os.environ.get("TEMP") == None:
		temp = "c:\\temp"	
	else:
		temp = os.environ.get("TEMP")
	
	if os.environ.get("TMP") == None:
		temp = "/usr/tmp"		
	else:
		temp = os.environ.get("TMP")  

	Connection_File_Name = Conn_File_NameT + ".sde"
	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
	
	# Check for the .sde file and delete it if present
	arcpy.env.overwriteOutput=True
	if os.path.exists(Connection_File_Name_full_path):
		os.remove(Connection_File_Name_full_path)

	try:
		print "\nCreating Database Connection File...\n"	
		# Process: Create Database Connection File...
		# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
		#arcpy.CreateDatabaseConnection_management(temp , Connection_File_Name, database_type, instance, database, account_authentication, dbms_admin, dbms_admin_pwd, "TRUE")
		arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=dbms_admin, password=dbms_admin_pwd, save_user_pass="TRUE")
	        for i in range(arcpy.GetMessageCount()):
			if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
				arcpy.AddReturnMessage(i)
				arcpy.AddMessage("\n+++++++++")
				arcpy.AddMessage("Exiting!!")
				arcpy.AddMessage("+++++++++\n")
				sys.exit(3)            
			else:
				arcpy.AddReturnMessage(i)
				arcpy.AddMessage("+++++++++\n")

		print "Creating database user...\n"
		arcpy.CreateDatabaseUser_management(input_workspace=Connection_File_Name_full_path, user_authentication_type=user_type, user_name=dbuser, user_password=dbuser_pwd, role=role, tablespace_name=tablespace)
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
		arcpy.AddMessage("+++++++++\n")
	except:
		for i in range(arcpy.GetMessageCount()):
			arcpy.AddReturnMessage(i)
			
#Check if no value entered for option	
except SystemExit as e:
	if e.code == 2:
		parser.usage = ""
		print "\n"
		parser.print_help()   
		parser.exit(2)

Environments

This tool does not use any geoprocessing environments

Licensing Information

ArcGIS for Desktop Basic: No

ArcGIS for Desktop Standard: Yes

ArcGIS for Desktop Advanced: Yes

5/7/2015