create objects/grants in another schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: create objects/grants in another schema

  1. #1
    Join Date
    Jan 2000
    Location
    *
    Posts
    37

    Question

    I have a batch user that will be creating a table and index in another schema. Which I can do without a problem but how can I have that same batch user create a public synonym and grant select privileges on that same table? The batch user does not and will not have dba privileges.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    There is a system privilege called "CREATE PUBLIC SYNONYM" that you will have to grant to your batch user. Instead of dropping and recreating the table every time, you could truncate the table. That way, you only have to assign permissions once.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2000
    Location
    *
    Posts
    37
    Unfortunately it's a huge table that is much faster to recreate then insert into. Especially since I can create it unrecoverable with a select clause. Ok so I can have the batch user create public synonyms but what about select privileges on the new table?

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    Only the owner of an object can grant object privileges to other users. Actually also users who have grant option on the object can grant object privileges. But the first grant option has to be granted by the owner. Granting select privileges on the table without connecting as owner is not possible.

    An option is to create a view in the schema of the table owner, just a 'select * from your_table'. You can grant select privileges on this view to public, and create a public synonym with the name of the table for the view. Now the batch user only has to drop and recreate the table. View, public synonym and grants don't have to be recreated.


  5. #5
    Join Date
    Jan 2000
    Location
    *
    Posts
    37
    Thanks for your response. I don't want the batch user to own any objects in the database. What I was hoping to do was somehow to execute a stored procedure with dynamic sql in it to do the grants. Anyone have any thoughts on that? I know that I can run a sql script file to log in as the table owner (hiding the password) to do the grants, etc but I was hoping to find a different way of doing it.
    Thanks for all your help.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    You can create a package grant_privs owned by schemax that you grant execute permissions to your batchuser. batchuser would then execute package schemax.grant_privs which would then run as schemax and grant privs back to the batchuser.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width