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

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

sa17 variable permissions

asked 13 Mar '19, 07:26

Stalker
460●28●30●45
accept rate: 11%

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 13 Mar '19, 08:00

Volker Barth
39.5k●355●539●811
accept rate: 34%

edited 13 Mar '19, 08:04

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

permissions ×28
sa17 ×18
variable ×14

question asked: 13 Mar '19, 07:26

question was seen: 1,107 times

last updated: 13 Mar '19, 08:04

SAP SQL Anywhere

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

Follow this question

Related questions