Hi All, There is a code: CREATE DATABASE VARIABLE "dba"."varTest" INTEGER DEFAULT 1; CREATE USER "Test2" IDENTIFIED BY "1"; CREATE VIEW "dba"."TestView" AS select ID, (if exists(select * from dba.D1 where D1.DTYPE = dba.varTest) then '*' else NULL endif) as IS_D1 from dba.TEST; GRANT SELECT ON "dba"."TestView" TO "Test2"; Next, the user "Test2" makes this request select * from dba.TestView or such request select * from dba.D1 where D1.DTYPE = dba.varTest In both cases, I get an error: "Permission denied: you do not have permission to select from "varTest"". Question: How can the user be given the right to read only the value (NOT TO CHANGE) for a variable of type "database variable" ? |
See that doc topic on SQL variables - to quote:
So a database variable created by user "dba" cannot be directly accessed by other users. You would need to create it as a PUBLIC variable and user "Test" would need SELECT PUBLIC DATABASE VARIABLE to have read-only access. If "dba" is a role and all access to the variable by a user is done via objects owned by "dba" (such as procedures or your view), that might also work when the user has the according privilege. |