SA 17.0.9.4913: How to set the right to "select" for "database variable" for a simple user ?

[former_SQLA_member1694881]

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" ?

[VolkerBarth]

See that doc topic on SQL variables - to quote:

Database-scope variables owned by users
When a database-scope variable is owned by a user, only that user can select from, and update, that variable, and can do so regardless of the connection.
Database-scope variables can also be owned by a role. However, the only access to a database-scope variable owned by a role is through the stored procedures, user-defined functions, and events owned by that role.

Database-scope variables owned by PUBLIC
Database variables owned by PUBLIC are available to all users and connections provided the users have the right system privileges.

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.