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.
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.
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?
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.
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.
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.