x

Grant access to all objects in a schema to another user/schema

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?

more ▼

asked Feb 10, 2010 at 08:35 PM in Default

Martin Wills gravatar image

Martin Wills
42 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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?

There's no single command that will do that.

You can write a simple PL/SQL loop to do so, for example:

BEGIN
FOR i IN (SELECT object_name FROM user_objects WHERE object_type='PROCEDURE' )
LOOP
EXECUTE IMMEDIATE 'GRANT EXECUTE ON '||i.object_name||' TO other_username';
END LOOP;
END;

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.

more ▼

answered Feb 10, 2010 at 08:59 PM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

(comments are locked)
10|1200 characters needed characters left

3rd party tools like Toad (and I'm sure there are others) can make this pretty close to a single-click operation.

more ▼

answered Feb 10, 2010 at 09:51 PM

HillbillyToad gravatar image

HillbillyToad
1k 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 11, 2010 at 07:58 AM

Martin Wills gravatar image

Martin Wills
42 1 1 1

I've edited the code to add the semicolon.
Feb 11, 2010 at 08:36 AM Andrew Mobbs
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 19, 2011 at 10:37 PM

OcaVid gravatar image

OcaVid
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x33
x31

asked: Feb 10, 2010 at 08:35 PM

Seen: 15998 times

Last Updated: Feb 10, 2010 at 08:35 PM