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

asked 13 Mar '19, 07:26

Stalker's gravatar image

Stalker
515293151
accept rate: 11%


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%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 13 Mar '19, 08:04

Your answer
toggle preview

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

Question tags:

×28
×22
×14

question asked: 13 Mar '19, 07:26

question was seen: 1,311 times

last updated: 13 Mar '19, 08:04