Pivotal Knowledge Base

Follow

How to confirm Table Permissions in SQLFire/GemfireXD

Applies to

SQLFire 1.x to GemfireXD 1.x   

Purpose

This document describes a method to confirm table permissions in SQLFire and GemfireXD.

Solution

Since system table [SYSTABLEPERMS] stores the table permissions that have been granted, we can create a view which selects tablename from system table [SYSTABLES] AND selects user&Privileges from system table [SYSTABLEPERMS].

For example:

CREATE VIEW TablePermissionView 
(TableName, Users, SELECT_PRIV, DELETE_PRIV, INSERT_PRIV, UPDATE_PRIV, REFERENCES_PRIV, TRIGGER_PRIV, ALTER_PRIV)
AS
SELECT t.TABLENAME,p.GRANTEE,p.SELECTPRIV,p.DELETEPRIV,p.INSERTPRIV,p.UPDATEPRIV,p.REFERENCESPRIV,p.TRIGGERPRIV,p.ALTERPRIV FROM sys.SYSTABLEPERMS p, sys.SYSTABLES t WHERE p.TABLEID=t.TABLEID;
gfxd> select * from TablePermissionView oder by tablename, users;
TABLENAME |USERS |SELECT_PRIV|DELETE_PRIV|INSERT_PRIV|UPDATE_PRIV|REFERENCES_PRIV|TRIGGER_PRIV|ALTER_PRIV
-------------------------------------------------------------------------------------------------------------------
TABLEA |USER1 |y |N |N |N |N |N |N
TABLEA |USER2 |y |N |N |N |N |N |N
TABLEB |USER1 |N |N |N |y |N |N |N
TABLEB |USER2 |N |N |N |y |N |N |N

Reference

  1. SYSTABLEPERMS System Table description.
  2. SYSTABLES Table description.

Comments

Powered by Zendesk