Method and system for realizing Trino authority management based on jdbc

Abstract

The invention discloses a method and system for achieving Trino authority management based on jdbc, and belongs to the technical field of databases, the method comprises the steps that a TUAM database is designed, a user table is used for storing user information, and a user authority table is used for managing user authority; modifying a Trino source code, opening a user management interface, encrypting a password by using a bcrypt algorithm and storing the password, and developing the interface for querying all data sources, modes, tables and fields; dynamically calling the user management interface to realize real-time updating of user information; querying all permissions, distributing the permissions to users, and storing the data in a database; and setting a timed task, extracting permission information, processing the permission information, and storing the processed permission information to Trino in a JSON format. According to the method, the problems of complexity of user management, difficulty of fine granularity authority management and limitation that dynamic authority cannot be realized are solved, and the data security and the management efficiency are effectively improved.

Description

Technical Field

[0001] The invention relates to the technical field of databases, in particular to a method and system for implementing Trino authority management based on JDBC. Background Art

[0002] Trino (formerly known as PrestoSQL) was born at Facebook to solve the problem of slow interactive queries in MapReduce. Trino supports queries on relational databases, non-relational databases, and a variety of other types of data storage and data sources, forming a wide range of data access capabilities; Trino uses memory parallel processing technology to execute query tasks in parallel across multiple cluster nodes, improving query speed.

[0003] With the widespread application of big data technology, enterprises are increasingly relying on efficient data analysis to drive business decisions, and these analyses often involve the processing of a large amount of sensitive information, so it is crucial to ensure the security of Trino during data processing. To prevent information leakage, non-administrator-level user access will be restricted, and only authorized data content that matches their roles and responsibilities can be viewed. In terms of data security, two core elements are particularly emphasized: efficient isolation of user resources and fine-grained allocation of library and table permissions to users. Trino provides two types of permission control by default. First, it provides a file-based access control system. When faced with frequent permission changes and the need for large-scale user management, the cost of manual adjustment and maintenance increases significantly, and it lacks dynamic adaptability. Second, Trino also provides Open Policy Agent (OPA) access control. Using this method will increase the complexity of the system and maintenance costs. Summary of the invention

[0004] The technical task of the present invention is to address the above shortcomings and provide a method and system for implementing Trino permission management based on JDBC, which can overcome the complexity of user management, the difficulty of fine-grained permission management and the limitation of inability to implement dynamic permissions in the prior art, and provide a powerful and flexible permission management mechanism for Trino data access, effectively improving data security and management efficiency.

[0005] The technical solution adopted by the present invention to solve its technical problem is:

[0006] A method for implementing Trino authority management based on JDBC, the implementation of the method includes:

[0007] Design the TUAM (Trino User Authority Manager) database, including the user table and the user_authority table. The user table is used to store user information, and the user_authority table is used to manage user permissions. The user_authority table refines permissions to the data source, mode, table, and field levels, covering the permission allocation from the data source to the specific field. The permission types include "read-only" and "read-write";

[0008] Modify the Trino source code, open the user management interface, use the bcrypt algorithm to encrypt and store passwords, and develop an interface for querying all data sources, modes, tables, and fields;

[0009] Develop TUAM user management function, dynamically call the user management interface, and realize real-time update of user information; realize the authority allocation function, query all authorities, assign them to users, and save the data to the database;

[0010] Set up a scheduled task to extract permission information from the user_authority table and store it in Trino in JSON format after processing.

[0011] Furthermore, the user_authority table implements fine-grained access control through the ColumnAllow flag.

[0012] Furthermore, the user_authority table implements data masking through the ColumnMask field.

[0013] Furthermore, the specific implementation of this method is as follows:

[0014] Step 1: Start Trino's HTTPS service;

[0015] Step 2: Configure Trino to enable password service;

[0016] Step 3: Design the database required by TUAM, including the user table and user_authority table. The user table stores the user information (user name, password) for connecting to Trino, and the user_authority table stores the user permissions of Trino.

[0017] Step 4: Modify the Trino source code and open interfaces for creating users, deleting users, and resetting user passwords. At the same time, develop interfaces for retrieving information about all data sources, modes, tables, and fields.

[0018] Step 5: Design the user management function in TUAM, including creating, deleting and resetting user options. These operations will directly call the interface defined in step 4 to achieve instant update and dynamic management of Trino user information;

[0019] Step 6: Design the user permission function in TUAM. First, call step 4 to query all permissions, then assign permissions to users and store permission data in the database to ensure the persistence and traceability of permission assignment.

[0020] Step 7: Develop a scheduled task to extract permission information from the user_authority table at regular intervals, output the information in JSON format after processing, and store it in the Trino system to ensure real-time synchronization of permission information and internal consistency of the system.

[0021] Furthermore, the step 1, starting the HTTPS service of Trino includes generating an SSL certificate, then adjusting the settings of the config.properties file located in the etc directory, activating the HTTPS service, setting the HTTPS port and indicating the storage path of the SSL certificate.

[0022] Furthermore, the step 2, configuring Trino to enable the password service includes: creating a password.db file to store the password, modifying the config.properties file in the etc directory to enable the password authentication function; creating a password-authenticator.properties file to specify the file to store the password.

[0023] Furthermore, in step 4, calling the create user and reset user password interface will use the bcrypt algorithm to encrypt the password, and then store the encrypted hash value and user name in the password.db file.

[0024] The present invention also claims a system for implementing Trino authority management based on JDBC, comprising:

[0025] TUAM database, including user table and user_authority table. The user table stores the user information (user name, password) connected to Trino, and the user_authority table stores the user permissions of Trino.

[0026] Trino password module, modify the Trino source code, open the interface for creating users, deleting users, and resetting user passwords;

[0027] TUAM user management module, used to realize dynamic management of Trino user information and authority allocation;

[0028] The timing calibration module extracts permission information at regular intervals to ensure the real-time synchronization of permission information and the internal consistency of the system;

[0029] The system specifically implements Trino authority management through the above-mentioned method for implementing Trino authority management.

[0030] The present invention also claims a device for implementing Trino authority management based on JDBC, comprising: at least one memory and at least one processor;

[0031] The at least one memory is used to store a machine-readable program;

[0032] The at least one processor is used to call the machine-readable program to implement the above method.

[0033] The present invention also claims protection for a computer-readable medium having computer instructions stored thereon, which implement the above method when executed by a processor.

[0034] Compared with the prior art, the method and system for implementing Trino authority management based on JDBC of the present invention have the following beneficial effects:

[0035] The present invention solves the complexity of user management, the difficulty of fine-grained permission management, and the limitation of being unable to implement dynamic permissions. Trino user permission management is implemented by redesigning the Trino source code. The user management part provides a Web interface to simplify the Trino user management process, supports functions such as creating and deleting users, resetting user passwords, and can synchronize user information to the Trino server in real time. In terms of permission allocation, multi-level and fine-grained permission control is implemented, including data sources, data models, tables, and field levels, supporting read and write permissions and column-level access control, and data masking. All permission changes are also synchronized to Trino in real time to ensure that any adjustments take effect immediately, thereby providing a powerful and flexible permission management mechanism for Trino data access, effectively improving data security and management efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 is a diagram of the TUAM functional architecture provided by an embodiment of the present invention;

[0037] Figure 2 It is a diagram of the TUAM process provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0038] The present invention will be further described below in conjunction with specific embodiments.

[0039] The embodiment of the present invention provides a method for implementing Trino authority management based on JDBC, and the implementation of the method includes:

[0040] Design the TUAM (Trino User Authority Manager) database, including the user table and the user_authority table. The user table is used to store user information (user name, password), and the user_authority table is used to manage user permissions. The user_authority table refines permissions to the data source, mode, table, and field levels, covering the permission allocation from the data source to a specific field. The permission types include "read-only" and "read-write". Fine-grained access control and data masking are implemented through the ColumnAllow flag and the ColumnMask field.

[0041] Modify the Trino source code, open the user management interface, use the bcrypt algorithm to encrypt passwords and store them in password.db, and develop an interface for querying all data sources, modes, tables, and fields;

[0042] Develop TUAM user management function, dynamically call the user management interface, and realize real-time update of user information; realize the authority allocation function, query all authorities, assign them to users, and save the data to the database;

[0043] Set up a scheduled task to extract permission information from the user_authority table and store it in Trino in JSON format after processing.

[0044] TUAM functional architecture diagram Figure 1 shown.

[0045] Combination Figure 2 The TUAM flow chart shown in the figure, the specific implementation of the method is as follows:

[0046] Step 1: Start Trino's HTTPS service. This process includes generating an SSL certificate, then adjusting the settings in the config.properties file located in the etc directory, activating the HTTPS service, setting the HTTPS port and indicating the storage path for the SSL certificate.

[0047] Step 2: Configure Trino to enable the password service, create a password.db file to store passwords, modify the config.properties file in the etc directory, enable the password authentication function; create a password-authenticator.properties file to specify the file to store passwords.

[0048] Step 3: Design the database required by TUAM, including the user table and the user_authority table. The user table stores the user information (user name, password) for connecting to Trino, and the user_authority table stores the user permissions of Trino. The user_authority table not only records the user name, but also deeply refines the permission hierarchy, including the permission settings at the data source, data model, table, and field levels, achieving comprehensive coverage from the data source to a certain field in the table. The permission types are divided into "read-only" and "read-write", and different types of permissions can be assigned to the data. In addition, by introducing the ColumnAllow flag field, fine-grained control of access rights to specific database fields is achieved, and the ColumnMask field is used to define data masking rules, thereby enhancing data security and privacy protection.

[0049] Step 4: Modify the Trino source code and open interfaces for creating users, deleting users, and resetting user passwords; calling the create user and reset password interfaces will use the bcrypt algorithm to encrypt their passwords, and then store the encrypted hash value and user name in the password.db file. At the same time, the development interface is used to retrieve information about all data sources, modes, tables, and fields.

[0050] Step 5: Design the user management function in TUAM, including creating, deleting and resetting user options. These operations will directly call the interface defined in step 4 to achieve instant update and dynamic management of Trino user information.

[0051] Step 6: Design the user permission function in TUAM. First, call step 4 to query all permissions, then assign permissions to users, and store the permission data in the database to ensure the persistence and traceability of permission allocation.

[0052] Step 7: Develop a scheduled task to extract permission information from the user_authority table at regular intervals, output the information in JSON format after processing, and store it in the Trino system to ensure real-time synchronization of permission information and internal consistency of the system.

[0053] The embodiment of the present invention also provides a system for implementing Trino authority management based on JDBC, including:

[0054] TUAM database, including user table and user_authority table. The user table stores the user information (user name, password) of Trino, and the user_authority table stores the user permissions of Trino. The user_authority table refines permissions to the data source, mode, table, and field levels, covering the permission allocation from the data source to the specific field. The permission types include "read-only" and "read-write", and fine-grained access control and data masking are achieved through the ColumnAllow flag and ColumnMask field.

[0055] Trino password module, modify Trino source code, open interfaces for creating users, deleting users, and resetting user passwords; calling the create user and reset password interfaces will use the bcrypt algorithm to encrypt their passwords, and then store the encrypted hash value and user name in the password.db file. At the same time, the development interface is used to retrieve information about all data sources, modes, tables, and fields.

[0056] TUAM user management module is used to implement dynamic management of Trino user information and authority allocation, including creating, deleting and resetting user options. These operations will directly call the interface defined by the Trino password module to implement instant update and dynamic management of Trino user information. To design the user authority function in TUAM, first call the Trino password module to query all authorities, then assign authorities to users, and store the authority data in the database to ensure the persistence and traceability of authority allocation.

[0057] The timing calibration module extracts the permission information from the user_authority table at regular intervals, outputs the JSON format file after processing, and stores it in the Trino system to ensure the real-time synchronization of the permission information and the internal consistency of the system.

[0058] The system specifically implements Trino authority management through the method for implementing Trino authority management based on JDBC described in the above embodiment.

[0059] The embodiment of the present invention also provides a device for implementing Trino authority management based on JDBC, comprising: at least one memory and at least one processor;

[0060] The at least one memory is used to store a machine-readable program;

[0061] The at least one processor is used to call the machine-readable program to implement the method for implementing Trino authority management based on JDBC described in the above embodiment.

[0062] The embodiment of the present invention also provides a computer-readable medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the method for implementing Trino authority management based on JDBC described in the above embodiment can be implemented. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device can read and execute the program code stored in the storage medium.

[0063] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0064] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer by a communication network.

[0065] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0066] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0067] The present invention is shown and described in detail above through the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above multiple embodiments, those skilled in the art can know that the code review methods in the above different embodiments can be combined to obtain more embodiments of the present invention, and these embodiments are also within the protection scope of the present invention.

Claims

1. A method for implementing Trino authority management based on jdbc, characterized in that: The implementation of this method includes: Design a TUAM database, including a user table and a user_authority table. The user table is used to store user information, and the user_authority table is used to manage user permissions. The user_authority table refines permissions to the data source, mode, table, and field levels, covering permission allocation from data sources to specific fields. Permission types include read-only and read-write. Modify the Trino source code, open the user management interface, use the bcrypt algorithm to encrypt and store passwords, and develop an interface for querying all data sources, modes, tables, and fields; Develop TUAM user management function, dynamically call the user management interface, and realize real-time update of user information; after querying all permissions, assign them to users and save the data to the database; Set up a scheduled task to extract permission information from the user_authority table and store it in Trino in JSON format after processing.

2. According to a method for implementing Trino authority management based on jdbc according to claim 1, it is characterized in that: The user_authority table implements fine-grained access control through the ColumnAllow flag.

3. According to a method for implementing Trino authority management based on jdbc according to claim 2, it is characterized in that: The user_authority table implements data masking through the ColumnMask field.

4. According to a method for implementing Trino authority management based on jdbc according to claim 1, it is characterized in that: The specific implementation of this method is as follows: Step 1: Start Trino's HTTPS service; Step 2: Configure Trino to enable password service; Step 3: Design the database required by TUAM, including the user table and user_authority table. The user table stores the user information connected to Trino, and the user_authority table stores the user permissions of Trino. Step 4: Modify the Trino source code to open interfaces for creating users, deleting users, and resetting user passwords; Step 5: Design the user management function in TUAM, including creating, deleting and resetting user options. These operations will directly call the interface defined in step 4 to achieve instant update and dynamic management of Trino user information; Step 6: Design the user permission function in TUAM. First, call step 4 to query all permissions, then assign permissions to users and store permission data in the database to ensure the persistence and traceability of permission assignment. Step 7: Develop a scheduled task to extract permission information from the user_authority table at regular intervals, output the information in JSON format after processing, and store it in the Trino system to ensure real-time synchronization of permission information and internal consistency of the system.

5. According to a method for implementing Trino authority management based on jdbc according to claim 4, it is characterized in that: The step 1, starting the HTTPS service of Trino includes generating an SSL certificate, then adjusting the settings of the config.properties file located in the etc directory, activating the HTTPS service, setting the HTTPS port and indicating the storage path of the SSL certificate.

6. According to a method for implementing Trino authority management based on jdbc according to claim 4, it is characterized in that: The second step is to configure Trino to enable the password service, including: creating a password.db file to store the password, modifying the config.properties file in the etc directory to enable the password authentication function; creating a password-authenticator.properties file to specify the file to store the password.

7. A method for implementing Trino authority management based on jdbc according to claim 6, characterized in that: In the step 4, calling the interface for creating a user and resetting a user's password will use the bcrypt algorithm to encrypt the password, and then store the encrypted hash value and user name in the password.db file.

8. A system for implementing Trino authority management based on JDBC, characterized in that: include: TUAM database, including user table and user_authority table. The user table stores information about users connected to Trino, and the user_authority table stores Trino user permissions. Trino password module, modify the Trino source code, open the interface for creating users, deleting users, and resetting user passwords; TUAM user management module, used to realize dynamic management of Trino user information and authority allocation; The timing calibration module extracts permission information at regular intervals to ensure the real-time synchronization of permission information and the internal consistency of the system; The system implements Trino authority management through the method for implementing Trino authority management based on JDBC as described in any one of claims 1 to 7.

9. A device for implementing Trino authority management based on jdbc, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is configured to call the machine-readable program to implement the method according to any one of claims 1 to 7.

10. A computer-readable medium, characterized in that The computer readable medium stores computer instructions, which, when executed by a processor, implement the method according to any one of claims 1 to 7.

Images