Cong LiIn 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!
