User does not have Select Access to View database

Database
Enthusiast

User does not have Select Access to View database

Hi All,

 

I am getting an error while accessing a view with a below error message:

" SELECT Failed.  [3523] The user does not have SELECT access to ViewDb.abc "

 

 

Here, abc is some table name and viewdb is some name of the  View database.

 

The strange part for me was that  I have an access to the underlying database Dbname.abc of the view but still does not have an access to the view.

The view definition is as follows:

REPLACE VIEW ViewDb.abc
AS LOCKING ROW FOR ACCESS
SEL * FROM Dbname.abc

;

The access for Dbname is through the Role on which all the developers have Read access.

Also, the authoriser of the Database has executed the command :

GRANT SELECT ON Dbname TO ViewDb WITH GRANT OPTION;

 

Please suggest if we are doing anything wrong here.

Thanks in Anticipation.

 


Accepted Solutions
Junior Apprentice

Re: User does not have Select Access to View database

Hi,

 

As the message says, you do not have access to the 'view database'.

 

from a security perspective, the 'view database' and the 'tab;e database' are two completely separate objects.

 

You're asking for access an object that you don't have access to, and so the query is rejected.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
3 REPLIES
Enthusiast

Re: User does not have Select Access to View database

I forgot to mention that I think we may  need to ask Authoriser to put the view database as well in the Role.

Because currently only the Database is under the Role.

 

Let me know if this is restricting me to access the view database. 

Junior Apprentice

Re: User does not have Select Access to View database

Hi,

 

As the message says, you do not have access to the 'view database'.

 

from a security perspective, the 'view database' and the 'tab;e database' are two completely separate objects.

 

You're asking for access an object that you don't have access to, and so the query is rejected.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: User does not have Select Access to View database

Thanks Dave for your quick response.. Now that I have asked the authoriser to add the view database in the role which worked perfectly fine!!!