Published on January 25, 2022.

A few weeks ago, I helped one of my NetSuite clients with their quarterly audit. One of the more interesting requests that the auditors made involved integrations. Specifically, the auditors wanted a list of the active access tokens, and the permissions that are available to each token.

In this post, I'll share a few queries that show how I was able to fulfill this request.

The oAuthToken Table

The key to fulfilling the request is a table named "oAuthToken." The table includes information about all of the tokens, regardless of whether or not they are active or have been revoked.

Here's a simple query that lists all of the active tokens that have not been revoked.

SELECT
	TBA_Token_Name AS TokenName,		
	BUILTIN.DF( TBA_App_Name ) AS Application,
	BUILTIN.DF( Entity_Name ) AS Entity,
	BUILTIN.DF( Role_Name ) AS Role,	
	DCreated AS DateCreated,
	BUILTIN.DF( CreatedBy ) AS CreatedBy
FROM
	oAuthToken
WHERE
	( BINACTIVE = 'F' )
	AND ( BREVOKED = 'F' )
ORDER BY
	TBA_Token_Name

The results of that query, as it was run in the SuiteQL Query Tool, will look something like this.

Click the image to view a larger version.

As you can see, the results include the token name, the application that it is associated with, the entity (employee, customer, vendor, etc) that it was issued for, as well as the role that it is based on, and both the date that the token was created and the employee that created it.

Role Permissions

To get the permissions associated with the tokens, I simply joined directly from the oAuthToken table to a table named RolePermissions. This is the same table that I referred to in a November 2020 blog posted titled "Access Employees, Supervisors, Roles, and Permissions Using SuiteQL."

Here's the revised query.

SELECT
	TBA_Token_Name AS TokenName,		
	BUILTIN.DF( TBA_App_Name ) AS Application,
	BUILTIN.DF( Entity_Name ) AS Entity,
	BUILTIN.DF( Role_Name ) AS Role,	
	DCreated AS DateCreated,
	BUILTIN.DF( CreatedBy ) AS CreatedBy,
	RolePermissions.Name AS PermissionName,
	BUILTIN.DF( RolePermissions.PermLevel ) AS PermissionLevel
FROM
	oAuthToken
	INNER JOIN RolePermissions ON
		( RolePermissions.Role = oAuthToken.Role_Name )
WHERE
	( oAuthToken.BINACTIVE = 'F' )
	AND ( oAuthToken.BREVOKED = 'F' )
ORDER BY
	oAuthToken.TBA_Token_Name

Again, I'm simply joining from the oAuthToken table to the RolePermissions table, and the join is based on the oAuthToken's "Role_Name" column. Note that the name of that column is somewhat confusing, because the actual value is the internal ID of the associated role (not the role's name).

Here's an example of the query results.

Click the image to view a larger version.

As you can see, we now have the access token information, and rows for each permission that a token has, based on the associated role. I've also listed the permission level for each permission.

Identifying Access Tokens Based On A Permission

In a follow-up request, the auditors asked for a query that would give them the ability to search for access tokens that have a specific permission. All that this involved is adding an additional filter to the WHERE clause and specifying a permission name.

For example, to find tokens that have permissions on "Documents and Files," the query would look like this.

SELECT
	TBA_Token_Name AS TokenName,		
	BUILTIN.DF( TBA_App_Name ) AS Application,
	BUILTIN.DF( Entity_Name ) AS Entity,
	BUILTIN.DF( Role_Name ) AS Role,	
	DCreated AS DateCreated,
	BUILTIN.DF( CreatedBy ) AS CreatedBy,
	RolePermissions.Name AS PermissionName,
	BUILTIN.DF( RolePermissions.PermLevel ) AS PermissionLevel
FROM
	oAuthToken
	INNER JOIN RolePermissions ON
		( RolePermissions.Role = oAuthToken.Role_Name )
WHERE
	( oAuthToken.BINACTIVE = 'F' )
	AND ( oAuthToken.BREVOKED = 'F' )
	AND ( RolePermissions.Name = 'Documents and Files' )
ORDER BY
	oAuthToken.TBA_Token_Name

Wrapping Up

I'm receiving these audit-type query requests pretty frequently. As I mentioned above, I've previously written about a similar request to use SuiteQL to report on roles and permissions. I've also written about queries that are being used to analyze login attempts and roles being used, identify employees that have not been logging in and more recently, to identify roles that are not being used.

I hope you find this query, and the queries in those previous posts, to be helpful as you analyze and audit your NetSuite account.