Guide to Viewing User Permissions in GBase 8s

In database management, controlling user permissions is key to ensuring data security and compliance. GBase database provides a detailed permissions management mechanism. This article will thoroughly explain how to view and manage user permissions in GBase 8s database, helping database administrators effectively monitor and maintain the security of data access.

1. Understanding Permissions in GBase 8s

This section mainly introduces "how to view what permissions a user has," with a brief description of database-level and table-level permissions in GBase 8s.

Database-Level Permissions:

Permission	Description
connect	The lowest level of database-level user permission. Users with this permission can execute SELECT, INSERT, UPDATE, and DELETE statements, execute stored procedures on tables, create views of tables, and create temporary tables.
resource	Users with this permission, in addition to having all CONNECT permissions, can create new tables and perform ALTER and DROP operations on tables they create, as well as create indexes.
dba	The creator and owner of the database are automatically granted this permission. Users with this permission, in addition to having all RESOURCE permissions, can also grant or revoke CONNECT, RESOURCE, and DBA permissions to other users.

Table-Level Permissions:

Permission	Description
select	Allows the user to query records in the table (this permission can be extended to specific fields).
delete	Allows the user to delete data in the table.
insert	Allows the user to add new data to the table.
update	Allows the user to modify records in the table.

2. Viewing Database-Level Permissions

To view which users have what permissions on the current database, use the sysusers table and the usertype field (C=Connect, R=Resource, D=DBA).

> database test;

At this point, the gbasedbt user has D (DBA) permissions on the test database.

> select * from sysusers;
username  gbasedbt
usertype  D
priority  9
password  
defrole   
1 row(s) retrieved.

Assign permission to the user1 (assuming user1).

> grant connect to user1;
Permission granted.

At this point, the gbasedbt user has D (DBA) permissions on the test database, and the user1 user has C (connect) permissions.

> select * from sysusers;
username  gbasedbt
usertype  D
priority  9
password  
defrole   
username  user1
usertype  C
priority  5
password  
defrole   
2 row(s) retrieved.

3. Viewing Table-Level Permissions

To view the table-level permissions for user1 on table t1 in the test database:

> database test;
> create table t1(id int,name char(20));

Assign permission to the user1 user.

> grant select on t1 to user1;
Permission granted.

Execute the following SQL. The tabauth field displaying s indicates that the user has select permission.

> select * from systabauth where tabid=(select tabid from systables where tabname='t1')and grantee='user1';
grantor  gbasedbt
grantee  user1
tabid    100
tabauth  s--------
1 row(s) retrieved.

Assign additional permission to the user1 user.

> grant insert on t1 to user1;
Permission granted.

Execute the following SQL. The tabauth field displaying s and i indicates that the user has both select and insert permissions.

> select * from systabauth where tabid=(select tabid from systables where tabname='t1')and grantee='user1';
grantor  gbasedbt
grantee  user1
tabid    100
tabauth  s--i-----
1 row(s) retrieved.

4. Introduction to the sysusers Table

Field	Type	Description
username	char(32)	User/role name.
usertype	char(1)	C = Connect R = Resource D = DBA G = Role U = Default Role
priority	smallint	Reserved field.
password	char(16)	Reserved field.
defrole	char(32)	Name of the default role.

5. Introduction to the systabauth Table

Field	Type	Description
grantor	char(32)	Name of the privilege grantor.
grantee	char(32)	Name of the privilege recipient.
tabid	integer	Identifier (from systables.tabid).
tabauth	char(9)	Pattern of privileges on the table, view, synonym, or sequence s or S = Select u or U = Update * = Column-level privileges i or I = Insert d or D = Delete x or X = Index a or A = Alter r or R = References n or N = Under privilege.

With the detailed introduction in this article, you should now understand how to query and manage user permissions in the GBase database. Proper configuration and regular review of user permissions are essential measures to ensure database data security. We hope this article helps you in managing user permissions. Thank you for reading!