issue with grant

srinivas_naikk · New User Joined: 23 Jul 2007 Posts: 26 Location: India

Hi All,

I am facing issues while granting access on the views to other users in DB2 v10

I have created a table TAB1 using ID1 and granted select on those tables to others. for eg: ID2 using

GRANT SELECT ON TAB1 to ID2;

the above statment is successful

I have created a view VIEW1 on TAB1 and trying to grant select on that view to ID2. It is failing with insufficient authority to grant on view

when I query, SYSIBM.SYSTABAUTH for table, SELECTAUTH has G which means, it has an option to grant to other users

However, when I query SYSIBM.SYSTABAUTH for view, under SELECTAUTH only Y is there and no with grant option G.

The creator of the view is not getting with Grant option.

ID1 has SYSCTRL privilege.

but if I grant select on those views using authorization ID which has SYSADM privileges, it is working.

Let me know where is the issue

thanks in advance

sushanth bobby · Posted: Thu Jul 14, 2011 8:11 pm

Srinivas,

SYSCTRL authority cannot SELECT user data.

Thanks,
Sushanth

gylbharat · Posted: Fri Jul 15, 2011 11:37 am

srinivas_naikk · New User Joined: 23 Jul 2007 Posts: 26 Location: India

Hi Sushanth,

Yes, I know SYSCTRL will not have access to USER DATA.

Hence I am trying grant SELECT access on the tables created

But I am unable to grant it and is failing while granting though the owner of the table is trying to grant on the view.

Granting on table is successful, but granting on the view created for the table is failing

Regards,
Srinivas

dick scherrer · Posted: Fri Jul 15, 2011 9:22 pm

Hello,

srinivas_naikk · New User Joined: 23 Jul 2007 Posts: 26 Location: India

Hi,

Sorry my previous post was confusing.

Let me explain again.

I have created a table and was able to grant SELECT access to others on the table. The users are able to query the table.

I have also created a VIEW on that table. I am trying to grant SELECT on the view created for other users. But the GRANT SELECT on that view is failing stating "the grantor does not have privileges to grant select on the VIEW".

Am i missing anywhere?

Thanks in advance

Regards,
Srinivas

Nic Clouston · Posted: Fri Jul 15, 2011 11:13 pm

You are missing the fact that a view is data and not a table and that you are not authorised to grant privileges on data.

srinivas_naikk · New User Joined: 23 Jul 2007 Posts: 26 Location: India

Yes, thats true.
I have a query again

Won't the creator of view or table have permission to grant select on the table/views created?

dick scherrer · Posted: Sun Jul 17, 2011 1:35 am

Hello,

As you have seen - this depends on the permissions of the creator. . .