Python script to set privileges / add users to Enterprise Geodatabase

[TylerT]

Hello,

Does somebody have a sample Python script they could share that adds users to Enterprise Geodatabase tables?  I am looking for the scripted analogy of this GUI workflow...(right click on EGDB table)

...of all or list of tables in the Enterprise Geodatabase.

Thank you,

Tyler

[AzinSharaf]

i use this one. it is for OS authentication and using DB rules. You may need to tweek it for your need.

import arcpy

def create_database_user(gdb, username, role):
    # Connect to the geodatabase as an admin
    arcpy.env.workspace = gdb

    # Check if user exists
    users = [user.Name for user in arcpy.ListUsers(gdb)]
    if username in users:
        print(f"User {username} already exists.")
        return

    # Create the user and assign to a role
    arcpy.management.CreateDatabaseUser(input_database=gdb,
                                        user_authentication_type="OPERATING_SYSTEM_USER",
                                        user_name=username,
                                        user_password='',
                                        role=role,
                                        tablespace_name="")
    
    print(f"User {username} created and assigned to role {role}.")



# inputs

geodatabase = r"<database>.sde"
username = "DOMAIN\\username"
role = "DB Writer Role"

create_database_user(geodatabase, username, role)

[TylerT]

Thank you, but your script is for adding users to EGDB.  My question is about assigning User privilege's to EGDB tables.  The add user dialogue in the GUI confuses the situation a bit, but I don't want to add users to EGDB, rather add users to tables in order to set to set User permissions, which is done through the dialogues shown.

For context, if a table is dropped and re-added, the User privilege's are wiped out.  We need to automate adding User privilege's back in to tables after dropping tables.

Thank you,

Tyler

[AzinSharaf]

this is what i am doing. hope it gives you the idea.

I create two database roles (<name>_r and <name>_w) and assign the privileges of each feature dataset to it. I always add Roles in the Privilage menu, not individual users.

how to create SQL roles in gdb:

def create_sql_roles(sde_connections_folder):
    # This function creates Reader and Writer database roles in SQL Server.
    arcpy.AddMessage("Creating reader and writer database roles in SQL Server...")
    sde_conn_admin = find(pattern="*admin*", path=sde_connections_folder)[0]
    sde_conn_reader = find(pattern="*reader*", path=sde_connections_folder)[0]
    sde_conn_writer = find(pattern="*writer*", path=sde_connections_folder)[0]

    admin_account_name = arcpy.Describe(sde_conn_admin).connectionProperties.user
    reader_account_name = arcpy.Describe(sde_conn_reader).connectionProperties.user
    writer_account_name = arcpy.Describe(sde_conn_writer).connectionProperties.user

    db_instance = arcpy.Describe(sde_conn_admin).connectionProperties.instance.lstrip(
        "sde:sqlserver:"
    )
    db_name = arcpy.Describe(sde_conn_admin).connectionProperties.database

    # connect to database
    # the account that python is running under should be SQL Server sys admin
    conn = pyodbc.connect(
        "DRIVER={ODBC Driver 17 for SQL Server};"
        "SERVER=" + db_instance + ";"
        "DATABASE=" + db_name + ";"
        "Trusted_Connection=yes;"
    )

    conn.autocommit = True
    cursor = conn.cursor()

    # creates DOMAIN\user database user
    arcpy.AddMessage(r"Creating REDACTED database user...")
    sql_string = f"""
                     USE [{db_name}]
                     IF NOT EXISTS (SELECT name
                                    FROM [sys].[database_principals]
                                    WHERE name = N'DOMAIN\REDACTED')
                     BEGIN
                        CREATE USER [DOMAIN\\REDACTED] FOR LOGIN [DOMAIN\\REDACTED]
                     END
                  """

    cursor.execute(sql_string)

    # creating Reader Role in SQL database and add X active directory group to the role
    reader_role_name = f"{db_name}_r"
    arcpy.AddMessage(f"Creating {reader_role_name} role...")
    arcpy.management.CreateRole(
        input_database=sde_conn_admin,
        role=reader_role_name,
        grant_revoke="GRANT",
        user_name=r"DOMAIN\AD Name",
    )



    arcpy.AddMessage(f"Adding {reader_account_name} user to {reader_role_name} role...")

    sql_string = f"""
                        USE [{db_name}]
                        ALTER ROLE {reader_role_name} ADD MEMBER [{reader_account_name}]
                  """
    cursor.execute(sql_string)

    # create Writer Role in SQL database

    writer_role_name = f"{db_name}_w"
    arcpy.AddMessage(f"Creating {writer_role_name} role...")
    arcpy.management.CreateRole(
        input_database=sde_conn_admin,
        role=writer_role_name,
        grant_revoke="GRANT",
        user_name=None,
    )

    arcpy.AddMessage(f"Adding {writer_account_name} user to {writer_role_name} role...")

    sql_string = f"""
                        USE [{db_name}]
                        ALTER ROLE {writer_role_name} ADD MEMBER [{writer_account_name}]
                  """
    cursor.execute(sql_string)

    return reader_role_name, writer_role_name

and how to change the permission on each dataset:

def get_datasets_list(sde_conn):
    arcpy.env.workspace = sde_conn
    datasets_list = arcpy.ListDatasets(wild_card="*", feature_type="Feature")
    table_list = arcpy.ListTables(wild_card="*", table_type="ALL")

    datasets_list = datasets_list + table_list

    return datasets_list




def change_privileges(sde_conn_admin, role, view_access, write_access):
    arcpy.AddMessage(f"Changing privileges for {role} role...")
    datasets_list = get_datasets_list(sde_conn=sde_conn_admin)
    for dataset in datasets_list:
        arcpy.management.ChangePrivileges(
            in_dataset=dataset, user=role, View=view_access, Edit=write_access
        )

and use it like this:

                change_privileges(
                    sde_conn_admin=sde_conn_admin,
                    role=reader_role_name,
                    view_access="GRANT",
                    write_access="AS_IS",
                )

                change_privileges(
                    sde_conn_admin=sde_conn_admin,
                    role=writer_role_name,
                    view_access="GRANT",
                    write_access="GRANT",
                )