🔧 GBase 8s Database User Management: Guide to Creating and Granting Permissions

Database security and user management are critical components in safeguarding enterprise data assets. GBase 8s database provides a comprehensive user creation and authorization mechanism to ensure the security and compliance of data operations. This article provides a detailed guide on how to create users, grant permissions, and manage user passwords in GBase 8s, offering database administrators a thorough operational manual.

1. Steps to Add a User

1) Switch Database User

• Log in to the database server and switch to the database user gbasedbt
• Command: su - gbasedbt

2) Check Database Status

• Ensure the database is running (the standby node will prompt initialization)
• Command: onstat -
• Expected result: On-Line status

3) Modify Database Configuration Parameters

(The prerequisite database is enabled by default, this step is for verification)

• Online modification of the USERMAPPING parameter
• Command: onmode -wf USERMAPPING=ADMIN (For the standby node, manually modify the /home/gbasedbt/gbase/etc/onconfig.gbaseserver file using vi, find the USERMAPPING parameter, and change it to ADMIN, then save and exit)

4) Confirm Parameter Modification

• Command: onstat -m
• Expected result:

5) Create a New Operating System User

• Command: useradd test1 and specify the database user password using passwd
• (Use the root user to create)

6) Log in to the Database to Create User

• Switch to gbasedbt user and execute the dbaccess command
• Expected result:

7) Select Database

• Press Enter to list all databases and select the database to be authorized, such as baidulast

8) Create Database User

• Command: create user test1 with password "test1" (password should match the OS passwd password)
• After entering, press ESC, then run the command

9) Execute SQL to Create the User

• Command: create user test1 with password "test1" (Ensure that the password set here matches the system passwd setting. By default, the database user password is based on the system passwd password).

• After entering, press ESC to enter the following page:

• The cursor will automatically stop at the Run option. Press Enter directly. If successful, the following screenshot will be shown with a prompt at the bottom:

10) Grant Database Permissions

• Move the cursor to the New option, press Enter, and input the authorization SQL
• Command: grant dba to test1

• After entering, press ESC, then run the command, it this be shown after the authorization is successful:

11) Single Node Database User Creation and Authorization Completed
- Subsequent configuration for the standby node is required
- Add OS user in the same way, refer to Step 5
- Modify the standby node configuration file, refer to Step 3

12) Revoke Permissions
- Command: revoke dba from test1
- Enter the interactive mode in the same way

2. Change Password

• Use the root user to execute passwd username to change the database user password, which requires 900 seconds for internal synchronization

3. Create Non-OS Database User

Database mapping user:

1) Create an OS User

useradd gbasetest
passwd gbasetest /gbasetest

2) Modify allowed.surrogates Configuration File

• This configuration file is located in the etc directory of the database software installation. Copy the std file to the /etc/gbasedbt (or /etc/gbasedbt, depending on the version) directory. It requires root user ownership and 600 permissions. Modify the file at the end with (user: gbasetest group: gbasetest).

3) Execute with gbasedbt User

onmode -cache surrogates

• Check logs for success
• Expected log entry:

Entries in the surrogates file /etc/gbasedbt/allowed.surrogates are loaded into surrogate cache

4) Log in to the Database System Library

Execute dbaccess sysuser, can use either default mapping or specify mapping user for each added user (recommended to set default mapping user)

• Command A: CREATE DEFAULT USER WITH PROPERTIES USER 'gbasetest'; then dbaccess sysuser execute

CREATE USER zwq1 WITH PASSWORD "GBase001"; 

• Command B: Do not create the default group execute

create user zwqtest1 with password "GBase001" properties user "gbasetest";

5) Grant Permissions

• After that, you can use gbasetest01 to log in to the database and connect to the corresponding database with the appropriate permissions.

To reset the user password:

• Execute the SQL command: set user password old "XXXXXXXX" new "XXXXXXXXX" — This method requires logging in with the user whose password you want to change.

• Execute the SQL command: alter user xxzxnbgl modify password '1qaz@WSXqwe' — This method requires logging in with the gbasedbt user to change any user's password.

4. Grant SELECT (Read-Only) Permissions

1) Grant Connect Permission

• Command: GRANT CONNECT TO user1;

2) Create a Stored Procedure

Note: The owner of the table (i.e., the user who created the table) whose permissions need to be revoked should be used to create and execute the following stored procedure. If there are tables created by multiple users in a database, the following stored procedure needs to be created and executed by each user separately (the stored procedure names can be chm1, chm2, etc.). To find the owner of each table, use the query:

select tabname, owner from systables where tabid > 99;

Stored procedure content:

   drop procedure if exists chm;
   create procedure chm(username varchar(255))
   returning varchar;
   define tname varchar(255);
   define towner varchar(255);
   define gsql varchar(255);
   define rmsql varchar(255);
   foreach cur for select tabname, owner into tname, towner from systables where tabid > 99 and tabtype<>'s'
     let rmsql = "REVOKE ALL ON " || tname || " FROM PUBLIC";
     execute immediate rmsql;
     let gsql = "grant select on " || tname || " to " || username || " as " || towner;
     execute immediate gsql;
   end foreach;
   end procedure;

3) Execute the Stored Procedure

execute procedure chm('user1');

With this detailed guide, you should now have a comprehensive understanding of how to create and grant permissions to users in the GBase 8s database. Proper management of database users and permissions is crucial for ensuring data security and improving operational efficiency. We hope this article helps database administrators manage users more effectively and contributes to protecting enterprise data assets.