How To
Summary
Example on how to limit an IBM i *USRPRF or *GRPPTF to data in an SQL Table using an SQL View
Objective
Limit a user to only a subset of the data in an SQL Table.
Steps
Row and column access control (RCAC) is the best way to limit access to data in an SQL Table.
Another option is to use an SQL View. Here is an example.
1) Create an underlying SQL Table.
CREATE TABLE KTRISKO.TABLE1 (COL1 CHAR (10 ) NOT NULL WITH DEFAULT,
COL2 CHAR (10 ) NOT NULL WITH DEFAULT)2) Insert a few rows.
INSERT INTO KTRISKO.TABLE1 VALUES ('Kevin', 'Trisko'),
('Paige', 'Trisko')3) Create an SQL View over that SQL Table. You can limit the columns as well as the rows.
CREATE VIEW KTRISKO.VIEW1 (COL1,COL2) AS
(SELECT COL1, COL2
FROM KTRISKO.TABLE1
WHERE COL1 = 'Paige')4) Use EDTOBJAUT to limit access to the SQL Table and only allow read access to the data.
This example limits
IBM i *USRPRF KTUSER. You could also use a Group Profile (*GRPPRF).You need to change
*PUBLIC to *EXCLUDE and remove Object -> Object operational authority and Data -> Execute.
5) Use EDTOBJAUT to set the access to the SQL View to what you need. I picked
*USE in this example.This example uses
IBM i *USRPRF KTUSER. You could also use a Group Profile (*GRPPRF).You need to change
*PUBLIC to *EXCLUDE.
6) You can test by running these SQL queries as
KTUSER.SELECT * FROM KTRISKO.TABLE1;
SELECT * FROM KTRISKO.VIEW1;
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i4eAAA","label":"IBM i Db2->SQL Examples \/ DB Examples \/ Misc how to"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
02 November 2021
UID
ibm16512870