Create Role (Data Management Tools)

Summary

Creates a database role, allowing you to add users to or remove them from the role.

Usage

This tool can be used with Oracle, Microsoft SQL Server, or PostgreSQL only.
You cannot use delimiters, such as double quotation marks, when specifying the role. The role can only contain characters supported by the underlying database management system when provided without delimiters.

Label	Explanation	Data type
Input Database Connection	The connection file to a database or enterprise geodatabase. Connect as a database administrator user.	Workspace
Role	The name of the database role to create. If it's an existing role, type the name for the role you want to add users to or remove them from.	String
Grant To or Revoke From User(s) (Optional)	Specifies whether the role will be added to a user or list of users or a user or list of users will be removed from the role. Grant role—The role will be granted to the specified user or users, making them a member of the role. This is the default. Revoke role—The role will be revoked from the specified user or users, removing them from the role.	String
User Name(s) (Optional)	The name of the user whose role membership will change. To specify multiple users, type the user names separated by commas (no spaces).	String

Derived output

Label	Explanation	Data type
Create Role Succeeded	Whether the tool completed successfully.	Boolean

arcpy.management.CreateRole(input_database, role, {grant_revoke}, {user_name})

Name	Explanation	Data type
input_database	The connection file to a database or enterprise geodatabase. Connect as a database administrator user.	Workspace
role	The name of the database role to create. If it's an existing role, type the name for the role you want to add users to or remove them from.	String
grant_revoke (Optional)	Specifies whether the role will be added to a user or list of users or a user or list of users will be removed from the role. `GRANT`—The role will be granted to the specified user or users, making them a member of the role. This is the default. `REVOKE`—The role will be revoked from the specified user or users, removing them from the role.	String
user_name (Optional)	The name of the user whose role membership will change. To specify multiple users, type the user names separated by commas (no spaces).	String

Derived output

Name	Explanation	Data type
out_result	Whether the tool completed successfully.	Boolean

Code sample

CreateRole example 1 (Python window)

Creates a database role named editors.

import arcpy
arcpy.management.CreateRole("C:\\gdbconnections\\gdb_oracle.sde", "editors")

CreateRole example 2 (Python window)

Creates a database group role, drafters, and grants it to users eng1 and eng2.

import arcpy
arcpy.management.CreateRole("C:\\dbconnections\\db_postg.sde", "drafters",
                            "GRANT", "eng1,eng2")

CreateRole example 3 (Python window)

Grants an existing database role, readers, to user auditor.

import arcpy
arcpy.management.CreateRole("C:\\connectionfiles\\db_ss.sde", "readers",
                            "GRANT", "auditor")

CreateRole example 4 (Python window)

Revokes the database role, creators, from database users intern1, intern2, and intern3.

import arcpy
arcpy.management.CreateRole("D:\\myconnectionfiles\\mygdb.sde", "creators",
                            "REVOKE", "intern1,intern2,intern3")

CreateRole example 5 (stand-alone script)

The following stand-alone script allows you to provide information specific to your site using options to do the following:

Create a database connection file, which is used as the input_database.
Create a database role.
Add users to the new role.

"""
Name: create_database_role.py
Description: Provide connection information to a database user.
Type create_database_role.py -h or create_database_role.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 ("-o", dest="operation", type ="choice", choices=['GRANT', 'REVOKE'], default='GRANT', help="Specify which operation to perform: grant to or revoke from the user(s). Options (case-sensitive):  GRANT, REVOKE.  Default=GRANT")
parser.add_option ("-r", dest="role", type="string", default="", help="role to be granted to the user")
parser.add_option ("-u", dest="userlist", type="string", default="", help="List of users to grant to or revoke from, separated by comma.")

# 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
    userlist = options.userlist
    role = options.role
    operation = options.operation

    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 (role == ""):
        print(" \n%s: error: \n%s\n" % (sys.argv[0], "Role name (-r) 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 (dbms_admin == ""):
            print("\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified!"))
            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 role 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 enterprise geodatabase.")
    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, account_authentication, username, password, database, save_username_password(must be true)
        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 role...\n")
        arcpy.management.CreateRole(input_database=Connection_File_Name_full_path, grant_revoke=operation, role=role,  user_name=userlist)
        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

Current Workspace

Licensing information

Basic: No
Standard: Yes
Advanced: Yes

Create Role (Data Management Tools)

Summary

Usage

Parameters

Derived output

Environments

Licensing information

Related topics