Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: grant and role

Re: grant and role

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Wed, 09 May 2001 22:38:41 -0700
Message-ID: <3AFA2960.F62AFDF7@exesolutions.com>

andy wrote:

> I have an Oracle 8.1.6 database installed on Windows NT4.
> I have a tablespace 'example' and a user with the same name.
> I've created a role called 'rexample'.
> The user own some object, in particular some tables.
> I've assigned 'connect' and 'rexample' roles to the users. At this
> point i would like to grant object privileges on role 'rexample'.
>
> One solution is:
>
> GRANT SELECT ON "EXAMPLE"."OBJECT1" TO "REXAMPLE";
> GRANT SELECT ON "EXAMPLE"."OBJECT2" TO "REXAMPLE";
> GRANT SELECT ON "EXAMPLE"."OBJECT3" TO "REXAMPLE";
>
> ...
>
> But there is a lot of objects in tablespace and the object can be
> added sometimes.
> I would like to have a command (e.g. a stored procedure) that grant
> all the obects automatically.
>
> Thanks in advance.
>
> Andy.

Then write one.

You can easily do it with something like this:

CREATE OR REPLACE PROCEDURE xyz IS

CURSOR xyz_cur IS

   SELECT table_name
   FROM user_tables;

xyz_rec xyz_cur%ROWTYPE;

BEGIN
   Open the cursor
   LOOP
      Concatenate together a string with the command you wish to execute

      Use native dynamic SQL to execute the string    END LOOP; END xyz;

Daniel A. Morgan Received on Thu May 10 2001 - 00:38:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US