idea Restrict edits to specific fields based on DB role in Attribute Rules Ideas

Restrict edits to specific fields based on DB role

Bud — Thu, 18 Jan 2024 02:48:17 GMT

ArcGIS Pro 3.2.1; Oracle 18c 10.7.1 EGDB

I want to provide a staff department with editing rights to specific fields in a feature class. Rather than set up an attribute rule based on a list of hardcoded user names, I'd like to utilize an existing Oracle role (group of users) in an attribute rule.

I'm aware that an alternative is to create a related table with separate DB privileges. I don't want to do that in this case since it adds complexity in terms of editing and data management. If the relationship is 1:1, it would be cleanest to add additional fields to the FC rather than separate them into a related table.

Could functionality be added to attribute rules that lets me restrict edits to specific fields based on a DB role?

Use case: Prevent edits to specific fields (and manage a report)

Re: Restrict edits to specific fields based on DB role

Jake_S — Wed, 17 Jan 2024 21:07:12 GMT

@Bud One way you can do this is a lookup table instead in the DB.

Create a constraint rule to look up the username then query that table for a match and depending on your logic allow or reject that edit

~ Jake

Re: Restrict edits to specific fields based on DB role

AllanBenvin_yyc — Wed, 17 Jan 2024 21:42:51 GMT

I second the comment by JS_ESRI.

I do a similar thing where I want to restrict editing of certain records in a table to specific users. In my case I have two validation tables: 1) a superuser table for the support team names 2) an maintuser (editor) table.

If the current user is in the superuser table they can edit any record.

If the user is in the maintuser table and the current record corresponds to their currently selected record, then the edit is allowed to proceed.

Code is below for reference. (Don't judge too harshly)

var curUser = GetUser($featureSet).username
var loadTab = $feature.LOAD_INSTANCE+"."+$feature.LOAD_OBJECT_OWNER+"."+$feature.LOAD_OBJECT_NAME

//check if user is in the superuser tab

//Parse curUser to strip out @coc and convert to uppercase
var idx = Find("@",curUser)
if (idx > -1){
curUser = Split(curUser,"@")[0];
}

//Check if user in superuse table
var superTab = FeatureSetByName($datastore,'GBS_UTIL.COC_METADATA.METADATA_SUPERUSERS', ['*'], false);
var superSQL = "UPPER(USERNAME) = UPPER('" + curUser + "')";

//run the query
var foundRecs = Filter(superTab, superSQL);
var superCount = Count(foundRecs);

//If user is in superuser table then return tru and allow edit, no need to go further
if (superCount > 0) {
//return {"errorMessage":"Current was in SuperUserTable:"+curUser+ " count is:"+superCount}
return true
}

//If not in SuperUser table then check privs.
//check if curUser is in an editor:
var maintTab = FeatureSetByName($datastore,"GBS_UTIL.COC_METADATA.METADATA_EDITORS_V", ["*"], false);
var maintSQL = " LOAD_OBJECT_FULLNAME = '"+loadTab+"' "+
"AND "+
"(PORTAL_USER = '"+curUser+"' "+
"OR DB_USERNAME = '"+curUser+"' "+
"OR DB_ALIAS = '"+curUser+"' "+
")"

//run the query
var foundMaintRecs = Filter(maintTab, maintSQL);
var maintCount = Count(foundMaintRecs);

//If user is in superuser table then continue and allow edit
if (maintCount == 0) {
return {"errorMessage":"Current user does not have maint privs on the table. \n "+curUser+" has no privs on "+loadTab+ "maintCount "+maintCount}
}

//If you get to here without a prior return then return true to allow editing
return true