Access privileges on user defined views (views under scheme)

[Former Member]

Hi,

I have created one user defined view under my schema using the below code.

Create view myschema.myview as

select current_date as today from dummy.

I can able to execute the select query from my login but from other login I am getting insufficient privilege error.

I have given catalog_read system privilege to the user.

What is the issue in my code?  Please someone help me to solve the error.

Thanks & Regards,

Ranjith.

[patrickbachmann]

I'm guessing this is because you have Object Privilege CREATE ANY in your user security settings.  But the other user would need this same access to your schema under Object Privileges.

-Patrick

[Former Member]

Hi Patrick,

Thanks for your reply,

I gave all the access for myschema to other users including CREATE ANY also.

so they have full access for myschema.

Thanks and Regards,

Ranjith.

[patrickbachmann]

Ok are you saying it still does not work?

[Former Member]

Yes, Patrick still it is not working.

[Former Member]

Hi Patrick,

I explain my issue clearly.

lets consider two users A and B are there and they both have all access to myschema.

If user A created an user defined view.

User B doesn't have access to the view which is created by A and vice versa.

Thanks and Regards,

Ranjith.

[Former Member]

Hi Ranjith,

Do both the users have complete authorization on the root package? If that does not work try assigning both the users content_admin.

[Former Member]

Hi sanjog,

I am telling about user defined view, I mean views which are under schema not under packages.

[patrickbachmann]

I see where sanjog is going with this though.  He is thinking other authorization issue besides your schema.  ie: Can you try to have this user create a schema view in their own schema?  Does that work?  If not then obviously missing some other authorization that you have.

-Patrick