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. GrantingALLat the database level, for example, givesALLon 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: RunSHOW GRANTS FOR userto review who holdsGRANT OPTIONand keep the privilege map clean.
A well‑structured privilege hierarchy keeps your gbase database both secure and manageable as your team grows.