Re: grant and role

On Wed, 09 May 2001 22:38:41 -0700, "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:

>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

What does it mean 'a string with the command...' ?
>
> Use native dynamic SQL to execute the string
> END LOOP;
>
>END xyz;
>
>Daniel A. Morgan
>
Received on Tue May 15 2001 - 05:37:52 CDT