HSQLDB-如何授予特权? [英] HSQLDB - how to grant privileges?

问题描述

当尝试在HSQLDB上运行SQL时，出现了此异常:

While trying to run SQL on HSQLDB I got this exception:

java.sql.SQLSyntaxErrorException:用户缺少特权或对象没有 在org.hsqldb.jdbc.JDBCUtil.sqlException上找到ADMIN(来源不明) 在org.hsqldb.jdbc.JDBCUtil.sqlException(未知源)处 org.hsqldb.jdbc.JDBCPreparedStatement.(来源未知) org.hsqldb.jdbc.JDBCConnection.prepareStatement(未知来源)

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ADMIN at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source) at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)

这就是我创建Connection对象的方式:

That's how I'm creating the Connection object:

public static Connection getConnection(){
    Connection conn = null;

    try {   
        Class.forName("org.hsqldb.jdbcDriver");
        conn = DriverManager.getConnection("jdbc:hsqldb:mem:test://localhost","sa","");         
    } 
    catch (Exception e) {
        e.printStackTrace();
    }   
    return conn;
}

这是我准备的声明:

    java.sql.PreparedStatement prSelect = con.prepareStatement("SELECT * FROM PUBLIC.ADMIN Where USERNAME=? AND PASSWORD=?;");

如何授予用户特权?目前，我正在使用SA用户.

How can I grant privileges to the user? Currently I'm using SA user.

这是我建立的架构-

EDIT 2: This is the schema that I built -

CREATE TABLE PUBLIC.SHOW_ORDER(
    ORDER_CODE INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
    CLIENT_ID VARCHAR(10),
    NUMBER_OF_TICKETS INTEGER,  
    SHOW_CODE INTEGER,
    CLIENT_NAME VARCHAR(25),
    TOTAL_PRICE INTEGER,
    PRIMARY KEY (ORDER_CODE),
);

CREATE TABLE PUBLIC.SHOW
(
    CODE INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
    MUSIC_TYPE INTEGER,
    NUMBER_OF_SEATS INTEGER,
    DESCRIPTION VARCHAR(100),
    S_DATE DATE,
    SEATS_TAKEN INTEGER,
    PRIMARY KEY (CODE)
);
CREATE TABLE PUBLIC.SYS_MUSIC_TYPE (
    CODE INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
    DESCRIPTION VARCHAR(100),
    PRIMARY KEY (CODE)
);
CREATE TABLE PUBLIC.ADMIN
(
    USERNAME VARCHAR(25),
    PASSWORD VARCHAR(25) NOT NULL,
    PRIMARY KEY (USERNAME)
);

推荐答案

一些对您最有用的DDL语句是CREATE USER，CREATE ROLE和GRANT.

Some of the DDL statements that would be most helpful to you are CREATE USER, CREATE ROLE, and GRANT.

这是DDL语句，用于使用分配的密码创建新用户:

This is the DDL statement to create a new user with the assigned password:

CREATE USER "CENASSIST" PASSWORD 'thepassword';

此DDL语句创建一个ROLE，可以为其分配一组权限.然后将角色分配给上面创建的用户:

This DDL statement creates a ROLE, to which a group of permissions may be assigned. The ROLE is then assigned to the user created above:

CREATE ROLE "FRONT_END";
GRANT FRONT_END TO "CENASSIST";

此DDL块为创建的FRONT_END角色分配权限.权限用于表访问:

This block of DDL assigns the permissions for the FRONT_END role that was created. The Permissions are for table access:

GRANT SELECT, UPDATE, INSERT ON TABLE Clients TO FRONT_END;
GRANT SELECT, UPDATE, INSERT ON TABLE Rotations TO FRONT_END;
GRANT SELECT, UPDATE, INSERT ON TABLE Institutions TO FRONT_END;
GRANT SELECT, UPDATE, INSERT ON TABLE Client_MRNs TO FRONT_END;
GRANT SELECT, UPDATE, INSERT ON TABLE Payers TO FRONT_END;

HSQLDB具有出色的在线手册，其中详细介绍了用于权限和访问控制的DDL语句.

HSQLDB has an excellent online manual that details the DDL statements used for permissions and access control.

这篇关于HSQLDB-如何授予特权?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！