GBase 8s, the China‑domestically developed OLTP database from GBASE, enforces security through two layers of permissions: database‑level and object‑level. Understanding how to grant, revoke, and inspect these privileges is fundamental for every DBA and developer working with a gbase database.
1. Database‑Level Privileges
Database‑level privileges control whether a user can connect and administer a database. They come in three tiers:
- CONNECT: The lowest level. Users can run SELECT, INSERT, UPDATE, DELETE, create views and temporary tables.
- RESOURCE: Includes all CONNECT privileges, plus the ability to create tables, indexes, and alter/drop their own tables.
- DBA: The highest level. Inherits all RESOURCE privileges and can grant or revoke any database‑level privilege to other users, and operate on all database objects.
Grant and Revoke Syntax
GRANT CONNECT TO 'username';
GRANT RESOURCE TO 'username';
GRANT DBA TO 'username';
REVOKE CONNECT FROM 'username';
REVOKE RESOURCE FROM 'username';
REVOKE DBA FROM 'username';
When DBA or RESOURCE is revoked, the user is automatically downgraded to CONNECT. Revoking CONNECT entirely removes the user's access to that database.
Viewing Database‑Level Privileges
Query the sysusers system table; the usertype column shows: C = CONNECT, R = RESOURCE, D = DBA, G = Role, U = Default Role.
SELECT usertype FROM sysusers WHERE username = 'username';
Example: Privilege Escalation and Downgrade
GRANT DBA TO user_i_02;
REVOKE DBA FROM user_i_02; -- user automatically becomes CONNECT
REVOKE CONNECT FROM user_i_02; -- access removed
2. Object‑Level Privileges (Tables, Views, Routines)
Object privileges grant fine‑grained control. The main permissions are:
| Privilege | Applies To | Description |
|---|---|---|
| SELECT | Tables, Views | Query data |
| INSERT | Tables, Views | Insert rows |
| UPDATE | Tables, Views | Update rows |
| DELETE | Tables, Views | Delete rows |
| INDEX | Tables | Create indexes (requires at least RESOURCE) |
| ALTER | Tables | Modify structure (requires RESOURCE or above) |
| REFERENCES | Tables | Create foreign key constraints |
| EXECUTE | Routines | Run stored procedures/functions |
| ALL | All | All of the above |
Important: A CONNECT user cannot use INDEX, ALTER, or REFERENCES even if those privileges are explicitly granted — a RESOURCE level or higher is required.
Grant and Revoke Syntax
-- Table/View privileges
GRANT SELECT, INSERT, UPDATE ON table_name TO user/role;
-- Column‑level privilege
GRANT SELECT (col1, col2) ON table_name TO user;
-- Fragment‑level privilege (for expression‑fragmented tables)
GRANT FRAGMENT INSERT ON table_name (part1, part2) TO user;
-- Routine privilege
GRANT EXECUTE ON procedure_name TO user;
-- Revoke
REVOKE SELECT ON table_name FROM user;
-- WITH GRANT OPTION: allows the grantee to grant the same privilege to others
GRANT SELECT ON table_name TO user WITH GRANT OPTION;
-- AS GRANTOR: specify the grantor; only that grantor can later revoke
GRANT SELECT ON table_name TO user AS grantor_name;
Inspecting Object Privileges
-- Table/View privileges (tabauth codes: s=SELECT, u=UPDATE, i=INSERT, d=DELETE, x=INDEX, a=ALTER, r=REFERENCES)
SELECT a.grantor, a.grantee, b.tabname, a.tabauth
FROM systabauth a
JOIN systables b ON a.tabid = b.tabid
WHERE b.tabname = 'table_name';
-- Column privileges
SELECT a.grantor, a.grantee, b.tabname, a.colno, a.colauth
FROM syscolauth a
JOIN systables b ON a.tabid = b.tabid
WHERE b.tabname = 'table_name';
-- Routine privileges
SELECT b.*
FROM sysprocedures a
JOIN sysprocauth b ON a.procid = b.procid
WHERE a.procname = 'proc_name';
Privilege letters appear uppercase in systabauth.tabauth when granted with WITH GRANT OPTION (meaning transferable), and lowercase otherwise.
3. The Public User
GBase 8s has a built‑in public user. Every database user's effective privileges = public privileges + their own. By default, public has SELECT, INSERT, UPDATE, DELETE, and INDEX on all tables. DBAs can tighten security by revoking defaults:
REVOKE ALL ON table_name FROM PUBLIC;
Combining database‑level and object‑level permissions gives you a powerful, layered security model. Mastering these commands will help you lock down your gbase database environment while keeping development and operations running smoothly.
Top comments (0)