topic Re: Python script to set privileges / add users to Enterprise Geodatabase in ArcGIS Enterprise Questions

Python script to set privileges / add users to Enterprise Geodatabase

TylerT — Mon, 09 Sep 2024 13:01:53 GMT

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

Re: Python script to set privileges / add users to Enterprise Geodatabase

AzinSharaf — Thu, 12 Sep 2024 17:23:23 GMT

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)

Re: Python script to set privileges / add users to Enterprise Geodatabase

TylerT — Tue, 08 Oct 2024 17:28:04 GMT

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

Re: Python script to set privileges / add users to Enterprise Geodatabase

AzinSharaf — Tue, 08 Oct 2024 18:47:37 GMT

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", )

Re: Python script to set privileges / add users to Enterprise Geodatabase

HerveHabonimana — Thu, 09 Jan 2025 17:58:06 GMT

Thanks a lot for sharing this.

Can you share details about this find function?

sde_conn_admin = find(pattern="*admin*", path=sde_connections_folder)[0]

Re: Python script to set privileges / add users to Enterprise Geodatabase

HerveHabonimana — Thu, 09 Jan 2025 18:08:36 GMT

Ignore my question. I was able to figure it out myself.

Thanks

Re: Python script to set privileges / add users to Enterprise Geodatabase

HerveHabonimana — Thu, 09 Jan 2025 21:00:51 GMT

After struggling with this issue, here is how I resolved it in Oracle Database

# this script assign SELECT, INSERT, UPDATE, DELETE import arcpy, os, oracledb dbuser = 'dbuser' # the user who needs access # path to sde path = r'path to .sde' # path to connection file sde_path = os.path.join(path, f'file.sde') USER = "sde level username" PASSWORD = "psw" DSN = "dns" oracledb.init_oracle_client(lib_dir=None) conn = oracledb.connect(user=USER, password=PASSWORD, dsn=DSN) curs = conn.cursor() writer_role_name = f"the name of the role you want to assign to user" db_name = "the name of the schema" # assign the writer role to the user arcpy.CreateRole_management(sde_path, writer_role_name, "GRANT", dbuser) feature_lists = ['fc1', 'fc2'] for fc in feature_lists: fc_ = f"{db_name}.{fc}" print(fc_) try: print(f"Granting privileges to {writer_role_name} on {fc_}...") curs.execute(f"GRANT SELECT, INSERT, UPDATE, DELETE ON {fc_} TO {writer_role_name}") conn.commit() print(f"Privileges granted to {writer_role_name} on {fc_}") except Exception as e: print(f"Failed to grant privileges to {writer_role_name} on {fc_}") print(e)