Row-level access controls
Create a table with row-level guards on who can update specific columns.
If you're creating a shared table, it's useful to gate row-level access controls such that only a specific set of users can mutate a row. This setup is quite flexible since controller logic can be anything that's written in a smart contract, but let's take a simple example with OpenZeppelin's access control framework and making checks on the table data.
In contract-owned table use cases, you can often avoid using controllers and simply rely on logic you write in your smart contract. By default, only the table owner can mutate data, so smart contracts that own tables can act as a "forwarding" entity on behalf of the msg.sender
. But, controllers enable more "hybrid" use cases that bring flexibility to collaborative data by letting anyone contribute to your table's data but with custom limitations. They are often useful when developing with the SDK or similar.
Setup
First, install the OpenZeppelin and Tableland contracts:
npm install @openzeppelin/contracts @tableland/evm
Your contract should import the OpenZeppelin Ownable
and Strings
contracts as well Tableland utilities for simpler SQL-in-Solidity syntax. We'll also have the contract create a table during deployment, so onERC721Received
is required for the contract to receive an ERC721 TABLE token. The table has the schema id integer primary key, address text, val text
and will store a user's address with a custom message.
// SPDX-License-Identifier: MIT
pragma solidity >=0.8.10 <0.9.0;
import {Strings} from "@openzeppelin/contracts/utils/Strings.sol";
import {Ownable} from "@openzeppelin/contracts/access/Ownable.sol";
import {IERC721Receiver} from "@openzeppelin/contracts/token/ERC721/IERC721Receiver.sol";
import {TablelandDeployments} from "@tableland/evm/contracts/utils/TablelandDeployments.sol";
import {SQLHelpers} from "@tableland/evm/contracts/utils/SQLHelpers.sol";
contract Example is Ownable {
// Store relevant table info
uint256 private tableId; // Unique table ID
string private constant _TABLE_PREFIX = "access_control"; // Custom table prefix
// Constructor that creates a simple table with a an `id` and `val` column
constructor() {
// Create a table
tableId = TablelandDeployments.get().create(
address(this),
SQLHelpers.toCreateFromSchema(
"id integer primary key,"
"address text,"
"val text",
_TABLE_PREFIX
)
);
}
// Return the table ID
function getTableId() external view returns (uint256) {
return _tableId;
}
// Return the table name
function getTableName() external view returns (string memory) {
return SQLHelpers.toNameFromId(_TABLE_PREFIX, _tableId);
}
// Needed for the contract to own a table
function onERC721Received(address, address, uint256, bytes calldata) external pure returns (bytes4) {
return IERC721Receiver.onERC721Received.selector;
}
}
Notice the getTableId
and getTableName
are public methods that return the table ID and name. These aren't required but can be useful in deployment scripts.
Table mutation methods
Let's create a way for this contract to insert data into the table. We'll create a simple insertIntoTable
method that allows anyone to insert a row into the table, and the Example
contract will control this data by forwarding it to the Tableland registry contract. The updateTable
method will gate row-level access so that only the one who inserted the row can update it (by checking the address
column is the same as the method's caller).
contract Example is Ownable {
// Store relevant table info
uint256 private _tableId; // Unique table ID
string private constant _TABLE_PREFIX = "access_control"; // Custom table prefix
// Other methods here...
// Let anyone insert into the table
function insertIntoTable(string memory val) external {
TablelandDeployments.get().mutate(
address(this), // Table owner, i.e., this contract
_tableId,
SQLHelpers.toInsert(
_TABLE_PREFIX,
_tableId,
"address,val",
string.concat(
SQLHelpers.quote(Strings.toHexString(msg.sender)), // Insert the caller's address
",",
SQLHelpers.quote(val) // Wrap strings in single quotes with the `quote` method
)
)
);
}
// Update only the row that the caller inserted
function updateTable(string memory val) external {
// Set the values to update
string memory setters = string.concat("val=", SQLHelpers.quote(val));
// Specify filters for which row to update
string memory filters = string.concat("address=", SQLHelpers.quote(Strings.toHexString(msg.sender)));
// Mutate a row at `address` with a new `val`—gating for the correct row is handled by the controller contract
TablelandDeployments.get().mutate(
address(this),
_tableId,
SQLHelpers.toUpdate(_TABLE_PREFIX, _tableId, setters, filters)
);
}
}
At this point, Example.sol
owns the table and forwards all SQL to the Tableland registry. The insertIntoTable
and updateTable
methods are marked as external
, so this lets anyone call them and write data to the table based on the logic defined in these methods. But, we can choose to also define a controller contract that gates row-level access controls for use cases outside of the Example.sol
contract.
Once we deploy the controller, we'll call the setAccessControl
to apply the controller to the table. This will use the onlyOnly
modifier to ensure this Example.sol
contract's deployer (i.e., the account that deployed it) is the only one that can change the controller.
contract Example is Ownable {
// Store relevant table info
uint256 private _tableId; // Unique table ID
string private constant _TABLE_PREFIX = "access_control"; // Custom table prefix
// Other methods here...
// Set the ACL controller to enable row-level writes with dynamic policies
function setAccessControl(address controller) external onlyOwner {
TablelandDeployments.get().setController(
address(this), // Table owner, i.e., this contract
_tableId,
controller // Set the controller address—a separate controller contract
);
}
}
Row-level controller
Background
We'll create a "hybrid" framework for the controller of this contract-owned table; for many contract-owned table use cases, this might not be needed. Controllers allow for anyone to write SQL statements against your table without needing to interact directly with your custom Example
contract. It brings more flexibility to decentralized collaboration since you can let others write their own logic for interacting with your table such that they don't have to call your Example
contract's methods and can utilize the Tableland registry alone. But, executed SQL statements will still abide by access control rules that you define in the controller.
Put differently, if someone is developing with the Tableland SDK or CLI, these are just wrappers around the Tableland registry. All calls will directly hit the registry and never go through a forwarding like with Example.sol
. So, controllers let you configure logic such that anyone can write SQL without touching the Example
contract. If you don't set up a controller, only the table owner can make changes to the table.
This will be a simple controller contract that does a few checks:
- If the caller is the table's owner, let the account do anything. This happens when someone calls the
insertIntoTable
orupdateTable
methods (aka the SQL forwarding process), and theExample
contract already manages how data is inserted or updated with WHERE clauses and column setters. - If the caller is not the table's owner, only let the account mutate rows that they inserted via
Example.sol
—the table tracks the address of who inserted the row in theinsertIntoTable
method. - If the caller is not the table's owner, don't allow any inserts nor deletions.
One of the key things to understand is how the registry uses the getPolicy
method:
- When a user calls
Example.sol
'sinsertIntoTable
orupdateTable
and tries to mutate a table, the registry will call thegetPolicy
method on the controller contract, passing in the caller's address and the table ID.- In our setup, the smart contract is forwarding SQL statements on behalf of the
msg.sender
when theinsertIntoTable
andupdateTable
methods are called. - So, the
caller
is the contract's address and not the original caller of the method.
- In our setup, the smart contract is forwarding SQL statements on behalf of the
- The controller contract will then return a
TablelandPolicy
struct that specifies the permissions for the caller. If someone is callinginsertIntoTable
orupdateTable
, it will always return an "allow-all" policy since thecaller
from the registry's point of view is, technically,Example.sol
. - If a user directly hits the Tableland registry and avoids
Example.sol
, it will only allow updates and make a check on thecaller
's address—here, thecaller
is notExample.sol
but could be anyone.
Why is this important? When someone calls insertIntoTable
or updateTable
, if you do any sort of address checking for caller
in the controller contract—such as in the whereClause
or updatableColumns
—the address you're checking against is the contract's address, not the original caller's address. That's why the "allow-all" concept is necessary. In use cases where someone is directly hitting the registry, the caller
is that person's address, so you can do checks on the caller
's address.
Setup
We'll store the address of the table owner (the Example
contract) in a variable called _TABLE_OWNER
. The contract will establish a whereClause
and updatableColumns
to gate write access such that anyone can update their row if it exists. That is, in Example.sol
, the updateTable
method handles how the caller can update the val
column based on the corresponding address
column. If someone hits the registry directly, it will use the controller for checking this—the whereClause
will check that the address
column matches the caller's address and enable the same functionality as Example.sol
.
// SPDX-License-Identifier: MIT
pragma solidity >=0.8.10 <0.9.0;
import {Strings} from "@openzeppelin/contracts/utils/Strings.sol";
import {SQLHelpers} from "@tableland/evm/contracts/utils/SQLHelpers.sol";
import {TablelandController} from "@tableland/evm/contracts/TablelandController.sol";
import {TablelandPolicy} from "@tableland/evm/contracts/TablelandPolicy.sol";
import {Policies} from "@tableland/evm/contracts/policies/Policies.sol";
contract RowController is TablelandController {
// Track the address of the contract owner, which is our `Example` contract
address private _TABLE_OWNER;
// Set the table owner during contract deployment
constructor(address tableOwner) {
_TABLE_OWNER = tableOwner;
}
function getPolicy(
address caller,
uint256
) public payable override returns (TablelandPolicy memory) {
// Return allow-all policy if the caller is the owner—our `Example` contract
if (caller == _TABLE_OWNER) {
return
TablelandPolicy({
allowInsert: true,
allowUpdate: true,
allowDelete: true,
whereClause: "",
withCheck: "",
updatableColumns: new string[](0)
});
}
// For all others, we'll have controls on the update
// First, establish WHERE clauses (i.e., where the address is the caller)
string[] memory whereClause = new string[](1);
whereClause[0] = string.concat(
"address=",
SQLHelpers.quote(Strings.toHexString(caller))
);
// Restrict updates to a single `val` column
string[] memory updatableColumns = new string[](1);
updatableColumns[0] = "val";
// Now, return the policy that gates by the WHERE clause & updatable columns
return
TablelandPolicy({
allowInsert: false,
allowUpdate: true,
allowDelete: false,
whereClause: Policies.joinClauses(whereClause),
withCheck: "",
updatableColumns: updatableColumns
});
}
}
Putting it all together
First, deploy the Example.sol
contract, and afterward, the RowController.sol
contract, passing the address of Example.sol
as a constructor argument. Then, have the deployer of Example.sol
call setAccessControl
, passing the address of RowController.sol
as a parameter. Note that you could choose to have Example.sol
also be the controller, but we've separated them in this walkthrough for clarity.
Now, you can test out the functionality! If any account calls insertIntoTable
, their value will be added to the table. The same goes for updateTable
—if someone inserted a row, they can openly update theirs. Let's review this from an access control perspective:
- If the caller is the table owner, the policy will return "allow all" permissions—inserts, updates, and deletes are all possible.
- If the caller is not the table owner and an insert or delete is attempted, nothing will happen.
- If the caller is not the table owner and an update is attempted, the policy will set the WHERE clause (only update rows where the
address
column matches the caller's address) and the updatable columns (only update theval
column).- Recall: anyone calling
insertIntoTable
orupdateTable
is actually having their SQL statement forwarded by theExample.sol
contract. So, this use case is only useful if the caller is directly interacting with the Tableland registry.
- Recall: anyone calling
You can imagine more complex workflows like gating with NFT ownership or onchain balances, but this should provide a starting point for how to gate row-level access controls! For replicating this setup, you can use the controller contract noted above, and the full Example.sol
contract is below:
// SPDX-License-Identifier: MIT
pragma solidity >=0.8.10 <0.9.0;
import {Strings} from "@openzeppelin/contracts/utils/Strings.sol";
import {Ownable} from "@openzeppelin/contracts/access/Ownable.sol";
import {IERC721Receiver} from "@openzeppelin/contracts/token/ERC721/IERC721Receiver.sol";
import {TablelandDeployments} from "@tableland/evm/contracts/utils/TablelandDeployments.sol";
import {SQLHelpers} from "@tableland/evm/contracts/utils/SQLHelpers.sol";
contract Example is Ownable {
// Store relevant table info
uint256 private _tableId; // Unique table ID
string private constant _TABLE_PREFIX = "access_control"; // Custom table prefix
// Constructor that creates a simple table with a an `id` and `val` column
constructor() {
// Create a table
_tableId = TablelandDeployments.get().create(
address(this),
SQLHelpers.toCreateFromSchema(
"id integer primary key,"
"address text,"
"val text",
_TABLE_PREFIX
)
);
}
// Let anyone insert into the table
function insertIntoTable(string memory val) external {
TablelandDeployments.get().mutate(
address(this), // Table owner, i.e., this contract
_tableId,
SQLHelpers.toInsert(
_TABLE_PREFIX,
_tableId,
"address,val",
string.concat(
SQLHelpers.quote(Strings.toHexString(msg.sender)), // Insert the caller's address
",",
SQLHelpers.quote(val) // Wrap strings in single quotes with the `quote` method
)
)
);
}
// Update only the row that the caller inserted
function updateTable(string memory val) external {
// Set the values to update
string memory setters = string.concat("val=", SQLHelpers.quote(val));
// Specify filters for which row to update
string memory filters = string.concat(
"address=",
SQLHelpers.quote(Strings.toHexString(msg.sender))
);
// Mutate a row at `address` with a new `val`—gating for the correct row is handled by the controller contract
TablelandDeployments.get().mutate(
address(this),
_tableId,
SQLHelpers.toUpdate(_TABLE_PREFIX, _tableId, setters, filters)
);
}
// Set the ACL controller to enable row-level writes with dynamic policies
function setAccessControl(address controller) external onlyOwner {
TablelandDeployments.get().setController(
address(this), // Table owner, i.e., this contract
_tableId,
controller // Set the controller address—a separate controller contract
);
}
// Return the table ID
function getTableId() external view returns (uint256) {
return _tableId;
}
// Return the table name
function getTableName() external view returns (string memory) {
return SQLHelpers.toNameFromId(_TABLE_PREFIX, _tableId);
}
// Needed for the contract to own a table
function onERC721Received(
address,
address,
uint256,
bytes calldata
) external pure returns (bytes4) {
return IERC721Receiver.onERC721Received.selector;
}
}
Hardhat deployment
To demonstrate how this might look in a Hardhat project, here's an example script that deploys the contract and sets the controller.
import { ethers } from "hardhat";
async function main() {
// Deploy the Example contract
const Example = await ethers.getContractFactory("Example");
const example = await Example.deploy();
await example.waitForDeployment();
console.log(`Example contract deployed to '${example.address}'.\n`);
// Deploy the RowController contract
const RowController = await ethers.getContractFactory("RowController");
const rowController = await RowController.deploy(example.address);
await rowController.waitForDeployment();
console.log(`Controller contract deployed to '${rowController.address}'.\n`);
// Set the Example contract's table controller to the RowController contract
let tx = await example.setAccessControl(rowController.address);
await tx.wait();
console.log(
`Example contract's table controller set to '${rowController.address}'.\n`
);
}
main().catch((error) => {
console.error(error);
process.exitCode = 1;
});
We can also do a test insert and update. Note that you can use the Tableland SDK, CLI, or Gateway APIs to check if the table data was mutated correctly.
async function main() {
// Existing code...
// Now, let's insert into the table using some random account
const [other] = await ethers.getSigners();
tx = await example.connect(other).insertIntoTable("init");
await tx.wait();
// We can also update the row we just inserted
tx = await example.connect(other).updateTable("test other");
await tx.wait();
}
At this point, if you query the data, it should look like:
[
{
"address": "0xf39fd6e51aad88f6f4ce6ab8827279cfffb92266",
"id": 1,
"val": "test other"
}
]
If you want to test the full access control functionality, you can update the script even further. Start by installing and importing the @tableland/sdk
to use the Registry
class. This will allow us to test out the controller in the instance someone is directly hitting the registry and not interacting with the Example.sol
contract.
The first time we call mutate
, the SQL will never execute due to an error: insert is not allowed by policy
. In the second case, it will succeed and update the existing row in the table.
import { ethers } from "hardhat";
import { Registry } from "@tableland/sdk";
async function main() {
// Existing code...
// Now, we're going to try & insert into the table by directly hitting the registry
const registry = await Registry.forSigner(other);
const { chainId } = await ethers.provider.getNetwork();
const tableId = await example.getTableId();
const tableName = await example.getTableName();
// The tx will succeed, but the SQL will fail and never mutate the table since
// the caller is not authorized to insert data
let statement = `INSERT INTO ${tableName} (val) VALUES ('this fails')`;
tx = await registry.mutate({ chainId, tableId, statement });
await tx.wait();
// The tx will succeed, and the SQL will execute to mutate the table since
// the caller is allowed to update their row
statement = `UPDATE ${tableName} SET val = 'this succeeds'`;
tx = await registry.mutate({ chainId, tableId, statement });
await tx.wait();
}
Thus, the entry in the table will now be updated:
[
{
"address": "0xf39fd6e51aad88f6f4ce6ab8827279cfffb92266",
"id": 1,
"val": "this succeeds"
}
]
Final thoughts
We walked through how to create a simple controller contract that gates writes to a table! Hopefully, this provided enough detail needed to get started! For more complex examples with NFT gating and balances, check out the example contracts.