Re: Granting the privileges of existing objects within a schema to a new role

"Ted" <steadman_at_ceva.net> wrote in message news:39d8ada9.0409151012.7da37d1e_at_posting.google.com...
| How do I grant all privileges for a schema that has a large number of
| existing tables, procedures, functions, etc to a newly created role,
| without having to issue a grant statement for each object and each
| privilege? I want the role to have all of the rights of the schema
| owner.
|
| Is there any kind of blanket granting of all privileges to a role?

Like 'GRANT SELECT TO xxx ON SCHEMA SCOTT'? Good idea, but doesn't exist.

You can, however, right PL/SQL to loop through all objects in a schema and grant appropriate privileges to the target role

Something like:

procedure grant_all_objects(ip$role in varchar2)

    for r1 in (select object_type, object_name from user_objects where object_type in .... )

    loop

        case
        when r1.object_type = 'TABLE'
        then execute immediate 'grant select on ' || r1.object_name || ' to
' || ip$role;
        when r1.object_type in ('PROCEDURE','FUNCTION'...

++ mcs Received on Wed Sep 15 2004 - 23:01:22 CEST