Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: grant and role
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