arcpy.management.CreateDatabaseUser(input_database, {user_authentication_type}, user_name, {user_password}, {role}, {tablespace_name})
|
Name
|
Explanation
|
Data type
|
|
input_database
|
The connection file to an Oracle, PostgreSQL, or SQL Server database or an enterprise geodatabase in those databases. Ensure that the connection is made as a user with privileges to create users in the database. When connecting to Oracle, you must connect as the sys user.
|
Workspace
|
|
user_authentication_type
(Optional)
|
Specifies the authentication type for the user. If you specify OPERATING_SYSTEM_USER, an operating system login must already exist for the user you will create. Operating system users are only supported for SQL Server and Oracle databases.
OPERATING_SYSTEM_USER—An operating system-authenticated user will be created. The corresponding login must already exist. If your database management system is not configured to allow operating system authentication, do not use this option.
DATABASE_USER—A database-authenticated user will be created. This is the default. If your database management system is not configured to allow database authentication, do not use this option.
|
Boolean
|
|
user_name
|
The name of the new database user.
If you create a database user for an operating system login, the username must be the same as the login name.
|
String
|
|
user_password
(Optional)
|
The password for the new user. The password policy of the underlying database is enforced.
If you create a database user for an operating system login, no input is required.
|
Encrypted String
|
|
role
(Optional)
|
The name of the existing database role to which the new user will be added.
|
String
|
|
tablespace_name
(Optional)
|
The name of the tablespace that will be used as the default tablespace for the new user in an Oracle database. You can specify a preconfigured tablespace, or, if the tablespace does not exist, it will be created in the Oracle default storage location with its size set to 400 MB. If no tablespace is provided, the user's default tablespace will be set to the Oracle default tablespace.
|
String
|
Derived output
|
Name
|
Explanation
|
Data type
|
|
out_result
|
Whether the tool completed successfully.
|
Boolean
|
Code sample
CreateDatabaseUser example 1 (Python window)
This sample script 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
arcpy.management.CreateDatabaseUser("C:/myconnections/oracledb1.sde",
"DATABASE_USER", "map", "Pam987", "sdetbs")
CreateDatabaseUser example 2 (Python window)
This sample script creates an input workspace (pgconn.sde) in a folder named connections and creates a database login role and schema in PostgreSQL.
import arcpy
arcpy.management.CreateDatabaseConnection("C:/connections", "pgconn.sde",
"POSTGRESQL", myserver, mypgdb,
"DATABASE_AUTH", "ela", "3L@pwd",
"SAVE_USERNAME")
arcpy.management.CreateDatabaseUser("C:/connections/pgconn.sde",
"DATABASE_USER", "dataowner", "N0look")
CreateDatabaseUser example 3 (Python window)
This sample script 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
arcpy.management.CreateDatabaseUser("C:/gdbconnections/connection_ssi.sde",
"OPERATING_SYSTEM_USER", "mynet\\vorhoos")
CreateDatabaseUser example 4 (stand-alone script)
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
"""
# Import system modules
import arcpy
import os
import optparse
import 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 Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS 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...
arcpy.management.CreateDatabaseConnection(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.management.CreateDatabaseUser(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)