DB2 : can't read data

--FP01
-- Membuat roles
CREATE ROLE admin_role;
CREATE ROLE distributor_role;
CREATE ROLE sales_role;
CREATE ROLE inventory_manager_role;

-- Membuat user admin
CREATE USER useradmin WITH PASSWORD 'rahasia';
GRANT CONNECT ON DATABASE TO USER useradmin;
GRANT ROLE admin_role TO USER useradmin;

-- Membuat user distributor
CREATE USER userdistributor WITH PASSWORD 'rahasia';
GRANT CONNECT ON DATABASE TO USER userdistributor;
GRANT ROLE distributor_role TO USER userdistributor;

-- Membuat user untuk role sales
CREATE USER usersales WITH PASSWORD 'rahasia';
GRANT CONNECT ON DATABASE TO USER usersales;
GRANT ROLE sales_role TO USER usersales;

-- Membuat user untuk role inventory manager
CREATE USER userinvmngr WITH PASSWORD 'rahasia';
GRANT CONNECT ON DATABASE TO USER userinvmngr;
GRANT ROLE inventory_manager_role TO USER userinvmngr;

-- Memberikan hak akses penuh ke admin
GRANT ALL PRIVILEGES ON TABLE Pabrik TO ROLE admin_role;
GRANT ALL PRIVILEGES ON TABLE Senjata TO ROLE admin_role;
GRANT ALL PRIVILEGES ON TABLE Distributor TO ROLE admin_role;
GRANT ALL PRIVILEGES ON TABLE Pemesanan TO ROLE admin_role;

-- Memberikan hak akses ke distributor
GRANT SELECT ON TABLE Senjata TO ROLE distributor_role;
GRANT SELECT ON TABLE Pemesanan TO ROLE distributor_role;

-- Memberikan hak akses ke sales
GRANT SELECT ON TABLE Pemesanan TO ROLE sales_role;
GRANT SELECT ON TABLE Distributor TO ROLE sales_role;
GRANT SELECT ON TABLE Senjata TO ROLE sales_role;

-- Memberikan hak akses ke inventory manager
GRANT SELECT, INSERT, UPDATE ON TABLE Senjata TO ROLE inventory_manager_role;
GRANT SELECT, INSERT, UPDATE ON TABLE Pabrik TO ROLE inventory_manager_role;

-- Membuat permission untuk row-level access pada tabel Pemesanan
CREATE PERMISSION pemesanan_row_access ON Pemesanan
FOR ROWS WHERE 
(
    verify_role_for_user(SESSION_USER, 'distributor_role') = 1
    AND Pemesanan.DistributorID = (SELECT DistributorID FROM Distributor WHERE DistributorID = SESSION_USER)
)
OR
(
    verify_role_for_user(SESSION_USER, 'admin_role') = 1
)
ENFORCED FOR ALL ACCESS
ENABLE;

-- Mengaktifkan Row Access Control untuk tabel Pemesanan
ALTER TABLE Pemesanan ACTIVATE ROW ACCESS CONTROL;

-- Membuat permission untuk row-level access pada tabel Senjata
CREATE PERMISSION senjata_row_access ON Senjata
FOR ROWS WHERE 
(
    verify_role_for_user(SESSION_USER, 'sales_role') = 1
    -- Memberikan akses untuk melihat semua senjata
)
OR
(
    verify_role_for_user(SESSION_USER, 'inventory_manager_role') = 1
    -- Memberikan akses untuk melihat dan memodifikasi data senjata
)
OR
(
    verify_role_for_user(SESSION_USER, 'admin_role') = 1
    -- Memberikan akses penuh kepada admin
)
ENFORCED FOR ALL ACCESS
ENABLE;

-- Mengaktifkan Row Access Control untuk tabel Senjata
ALTER TABLE Senjata ACTIVATE ROW ACCESS CONTROL;

-- Membuat permission untuk row-level access pada tabel Pabrik
CREATE PERMISSION pabrik_row_access ON Pabrik
FOR ROWS WHERE 
(
    verify_role_for_user(SESSION_USER, 'inventory_manager_role') = 1
    -- Memberikan akses untuk melihat dan memodifikasi data pabrik
)
OR
(
    verify_role_for_user(SESSION_USER, 'admin_role') = 1
    -- Memberikan akses penuh kepada admin
)
ENFORCED FOR ALL ACCESS
ENABLE;

-- Mengaktifkan Row Access Control untuk tabel Pabrik
ALTER TABLE Pabrik ACTIVATE ROW ACCESS CONTROL;


-- Membuat mask untuk kolom `Jumlah` pada tabel Pemesanan
CREATE MASK jumlah_mask ON Pemesanan
FOR COLUMN Jumlah RETURN
CASE
    WHEN verify_role_for_user(SESSION_USER, 'admin_role') = 1
    THEN Jumlah
    WHEN verify_role_for_user(SESSION_USER, 'inventory_manager_role') = 1
    THEN Jumlah
    ELSE 0
END
ENABLE;

-- Mengaktifkan Column Access Control untuk tabel Pemesanan
ALTER TABLE Pemesanan ACTIVATE COLUMN ACCESS CONTROL;

-- Membuat mask untuk kolom `Kaliber` pada tabel Senjata
CREATE MASK kaliber_mask ON Senjata
FOR COLUMN Kaliber RETURN
CASE
    WHEN verify_role_for_user(SESSION_USER, 'admin_role') = 1
    THEN Kaliber
    WHEN verify_role_for_user(SESSION_USER, 'sales_role') = 1
    THEN Kaliber
    ELSE 'XXX'
END
ENABLE;

-- Mengaktifkan Column Access Control untuk tabel Senjata
ALTER TABLE Senjata ACTIVATE COLUMN ACCESS CONTROL;

-- Membuat mask untuk kolom `AlamatPabrik` pada tabel Pabrik
CREATE MASK alamat_pabrik_mask ON Pabrik
FOR COLUMN AlamatPabrik RETURN
CASE
    WHEN verify_role_for_user(SESSION_USER, 'admin_role') = 1
    THEN AlamatPabrik
    WHEN verify_role_for_user(SESSION_USER, 'inventory_manager_role') = 1
    THEN AlamatPabrik
    ELSE 'XXX'
END
ENABLE;

-- Mengaktifkan Column Access Control untuk tabel Pabrik
ALTER TABLE Pabrik ACTIVATE COLUMN ACCESS CONTROL;

i've tried to log in as useradmin,usersales,userinvmngr even the superuser to any tables but it doesnt show any of the data i previously inputted.(Except for distributor table). is there any way to fix this issue? i already tried using chatgpt but it doesnt fix any of the issueenter image description here

Solution

Your question shows incorrect usage of case for role names, and incorrect syntax for currently released versions of Db2, and has insufficient facts (e.g sample data) to allow your question to be answered fully.

Always specify the Db2-server platform (z/os , i series, linux/unix/windows) and the Db2-server version when asking for help because the answer can depend on these facts. Use the correct tags for the Db2-server platform.

Your question shows create user statements, but that is not valid syntax for any released version of Db2. Have you verified that every statement completed without errors?

Role names are identifiers in Db2. Identifiers are case sensitive, and when identifiers are unquoted, Db2 implicitly converts identifiers to uppercase.

Your example shows rolenames being created unquoted so they will be stored as uppercase, but in your checking expression you quote the names in lowercase so they will not match.

Additionally, SESSION_USER will return an identifier, and that will be implicitly uppercase, so any data in the tables used for joining or matching in the checking-expression of the RBAC-permission will also be case sensitive.

Hence (for example) if SESSION_USER returns USERDISTRIBUTOR and your data in the Distributor.DistributorID is not exactly matching that on case, the verify_role_for_user will return 0 , and no rows will be returned .

So, most likely you need to fix the code and possibly additionally fix the related data to be compliant with Db2 behaviour.