• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

GRANT Select for Dynamically created view

Hi Experts,

Need help .  Stored proc creates view dynamically and grants select priv to the list of user . It works for user who created stored proc.
When other user runs  it gets message that user doesn't have privelege to grant priv to the view .

Any help greatly appreaciated

Thank you
0
juliakir
Asked:
juliakir
  • 6
  • 6
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Once you create the view / table dynamically in your procedure, run GRANT statement to grant rights to other users in your database so that they can access it out..

Since views are created dynamically, the next step should be creating GRANT statements dynamically in your procedure to handle this out.
0
 
juliakirAuthor Commented:
debugging somebody else code ..

grants are created dynamically after view is created  in the sp. error comes up -
H:\>db2 ? sql551


SQL0551N "<authorization-ID>" does not have the privilege to
          perform operation "<operation>" on object
          "<name>".
when particalar user runs stored proc ..

works fine for user who created sp

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Kindly confirm whether the user who is obtaining this error has rights to create the view or other operations involved in the procedure..
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
juliakirAuthor Commented:
user is able to create view but unable to grant select priveleges to this view to other users. WHat should be done so this user can grant as well ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
To Grant access to an object, the user should fall under any one of the below:

"The privileges held by the authorization ID of the statement must include at least one of the following:

    * CONTROL privilege on the referenced table, view, or nickname
    * The WITH GRANT OPTION for each identified privilege. If ALL is specified, the authorization ID must have some grantable privilege on the identified table, view, or nickname.
    * SYSADM or DBADM authority

DBADM or SYSADM authority is required to grant the CONTROL privilege, or to grant privileges on catalog tables and views."

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000966.htm

Kindly confirm..
0
 
juliakirAuthor Commented:
no this is not the case

looks like problem is fixed if user has select, grantable priv on all underlyuing tables in the view
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> looks like problem is fixed if user has select, grantable priv on all underlyuing tables in the view

If they don't have SELECT privileges on that table, they wont be able to create the view successfully..
Kindly confirm
0
 
juliakirAuthor Commented:
they can create view . they coudn't grant select priv to the view
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes.. They need rights on the underlying tables to grant permissions to others..
0
 
momi_sabagCommented:
can you post the entire code ?
if a user is able to create a view, it should be able to grant privileges on that view
0
 
juliakirAuthor Commented:
Hi momi

as per db2 doc
The definer of a view always receives the SELECT privilege on the view as well as the right to drop the view. The definer of a view will get CONTROL privilege on the view only if the definer has CONTROL privilege on every base table, view, or nickname identified in the fullselect, or if the definer has SYSADM or DBADM authority.

my issue was fixed after i gave that user select with grant priv on all tables in the view created dynamically

I would paste the code but view is huge and would not be helpfull...
0
 
momi_sabagCommented:
so you solved the problem or not?
0
 
juliakirAuthor Commented:
looks like problem is solved . thank you as always
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If it is solved, then kindly close the question accordingly..
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now