GBase 8a Privilege System: Five Levels and the WITH GRANT OPTION Clause

The privilege model in GBase 8a follows a "broad to fine" hierarchy across five levels. The WITH GRANT OPTION clause additionally controls whether a user can delegate the privileges they receive. Understanding both is essential for building a secure, maintainable gbase database.

The Five Privilege Levels

Level	Syntax	Example	Scope
Global	*.*	GRANT ALL ON *.* TO user1;	All database objects across the cluster or VC
Database	db_name.*	GRANT SELECT ON sales.* TO user1;	All tables, views, and routines in a database
Table	db_name.table_name	GRANT INSERT, UPDATE ON hr.employees TO user1;	All columns in the specified table
Column	db_name.table_name(col_list)	GRANT SELECT(id, name) ON emp.info TO user1;	Specific columns only
Routine	db_name.procedure_name	GRANT EXECUTE ON finance.calc_bonus TO user1;	A specific stored procedure or function

Key notes:

• VC prefix: In multi‑VC environments, qualify the object with the VC name: vc_name.db_name.object.
• Privilege inheritance: Higher‑level privileges implicitly include lower‑level ones. Granting ALL at the database level, for example, gives ALL on every table in that database.

What WITH GRANT OPTION Does

Without WITH GRANT OPTION	With WITH GRANT OPTION
The grantee can use the privilege but cannot pass it on. The delegation chain stops here. This is the default and safest mode for regular users.	The grantee can re‑grant or revoke the privilege for other users/roles. This creates a privilege propagation chain and is suitable for departmental admins, but risks uncontrolled privilege spread if overused.

Examples:

-- Standard grant: user1 cannot re‑grant
GRANT SELECT ON db1.t1 TO user1;

-- Grant with delegation: user1 may now grant SELECT to user2
GRANT SELECT ON db1.t1 TO user1 WITH GRANT OPTION;

Important: the ALL privilege is the set of all simple privileges except GRANT OPTION. Even with ALL, a user cannot re‑grant unless WITH GRANT OPTION is explicitly specified.

Best Practices

• Least privilege: Grant at the most specific level required. Use column‑level grants when a user only needs certain columns.
• Limit WITH GRANT OPTION: Reserve it for trusted users with administrative responsibilities. Avoid on regular application accounts.
• Audit regularly: Run SHOW GRANTS FOR user to review who holds GRANT OPTION and keep the privilege map clean.

A well‑structured privilege hierarchy keeps your gbase database both secure and manageable as your team grows.