DEV Community

Cong Li
Cong Li

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Assign permission to the user1 (assuming user1).

> grant connect to user1;
Permission granted.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

Assign permission to the user1 user.

> grant select on t1 to user1;
Permission granted.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Assign additional permission to the user1 user.

> grant insert on t1 to user1;
Permission granted.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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!

Top comments (0)