setTableSensitiveColumn

setTableSensitiveColumn(table, colName, option, [func])

This function sets or unsets colName in table as a sensitive column.

Once the column is marked as sensitive, only the creator of the table, administrators, and users with the DB_SENSITIVE_VIEW or TABLE_SENSITIVE_VIEW permissions can access the plaintext data of that column. Other users can only access the data that has been de-sensitized by func.

table is a DFS table object.

colName is a STRING scalar indicating a column name.

option is a Boolean value. true indicates that colName will be set as a sensitive column, and false means that the column will no longer be treated as sensitive.

func (optional) is a unary function that defines the desensitization algorithm. It can only be specified when option is true. The default algorithm is nullification masking. If different types need to be handled, the func should define the processing logic for each type.

// create database and partitioned table
login(`admin,`123456)
db = database(directory="dfs://sensitive", partitionType=VALUE, partitionScheme=1..5)
t = table(1..5 as tag, ["John","Emily","Michael","James","Tommy"] as userId, format(rand(100000,5),"000000") as password)
pt = createPartitionedTable(dbHandle=db, table=t, tableName="pt", partitionColumns="tag")
pt.tableInsert(t)

// set sensitive columns
def encryptId(str){
    return str[0]+"******"
}
setSensitiveColumn(table=loadTable("dfs://sensitive","pt"), colName="userId", option=true, func=encryptId)

def encryptPw(str) {
    return regexReplace(str, "[0-9]+", "#")
}
setSensitiveColumn(table=loadTable("dfs://sensitive","pt"), colName="password", option=true, func=encryptPw)

// create user1
createUser(`user1,`123456)
grant(userId=user1, accessType=TABLE_READ, objs="dfs://sensitive/pt")

// user1 reads the desensitized data
login(`user1,`123456)
select * from loadTable("dfs://sensitive","pt")

// grant TABLE_SENSITIVE_VIEW permission to user1
login(`admin,`123456)
grant(userId=user1, accessType=TABLE_SENSITIVE_VIEW, objs="dfs://sensitive/pt")

// user1 reads plaintext data
login(`user1,`123456)
select * from loadTable("dfs://sensitive","pt")