In ORACLE user = schema.
How can a user grant access to all his objects to another user, without listing them one by one?
For example, I wish to GRANT EXECUTE to another user for all my stored procedures. How?
asked Feb 10, 2010 at 08:35 PM in Default
There's no single command that will do that.
You can write a simple PL/SQL loop to do so, for example:
If you have to do this sort of thing as more than an exceptional case, you should look at roles. Grant the object permissions to a role, then grant the role to the users who need it.
3rd party tools like Toad (and I'm sure there are others) can make this pretty close to a single-click operation.
answered Feb 10, 2010 at 09:51 PM
Thank you, Andrew, for a straight and accurate answer.
The mention of roles raises other questions but, if I need the answers, I'll raise them separately.
Cannot flag up your answer as helpful since I'm a newcomer to this site. Sorry.
The EXECUTE statement needs a semi-colon at the end.
And I'll check out TOAD for this feature, Hillbilly.
answered Feb 11, 2010 at 07:58 AM
we have more than 20 users with different rights in the data base that needs to add, change or access the LARGE OBJECT in our database. when we upgraded to PostgreSQL 9.0.3 all of a sudden only superusers can access the lo. are there ways that we can do that any of the users that have the right in table where lo is can add, delete and change the contents of LO without having to go through all granting of rights to those users to that newly added lo?
(we ask in the IRC about this we was told that we have to grant the users to the lo one by one which we think is ridiculous.)
our application currently would not function well because of these. the only way we could to now is to grant all of them superuser role which is a very bad idea. Please help.
thank you, OcaVid
answered Apr 19, 2011 at 10:37 PM