Re: GRANT/PRIVILEGE PROBLEM: URGENT

From: Julio Negueruela <julio.negueruela_at_si.unirioja.es>
Date: Tue, 18 May 1999 12:27:00 +0200
Message-ID: <37414074.FC1ABB68@si.unirioja.es>

Doug Cowles escribió:
> =

> I'd be interested in the script....

The script is at the end of the post.

> Is this true of the latest and greatest Oracle versions that role privi=
leges
> are not
> used by a stored procedure or package? Even if they belong to the owne=
r of
> the package or procedure? Not making a judgement, just wondering.

Well, I'm not capable of answering, once I've read what others say. I prefer to read others' opinion.

Best regards.
-- =

Julio Negueruela
DBA Servicio Informático

Universidad de La Rioja      -      Spain
Telf: 941-299179     Fax: 941- 299180

mailto:julio.negueruela_at_si.unirioja.es

///////////////////// THE SCRIPT ////////////////////

/// Once created the procedure you can execute the it with: //// grantexecuterol (owner, package, role)

create or replace procedure grantexecuterol (propietario in varchar2,

                                        funcion in varchar2, rol in

varchar2)
as
cursor usuarios is

      select grantee from sys.dba_role_privs,sys.dba_users
       where granted_role = rol
         and grantee=username
      union
       select grantee from sys.dba_role_privs,sys.dba_roles
       where granted_role = rol
         and grantee=role
      order by 1;

usuario varchar2(45);
contador number(9);
identificador number;
begin
DBMS_OUTPUT.ENABLE(50000);
open usuarios;

DBMS_OUTPUT.PUT_LINE('...');
DBMS_OUTPUT.PUT_LINE('Dando privilegios de ejecucion al Role
'||rol||'...');
DBMS_OUTPUT.PUT_LINE('...');

loop
fetch usuarios into usuario;
exit when usuarios%notfound;
select count(*) into contador
  from sys.dba_roles
 where role=usuario;
if contador > 0 then

   grantexecuterol (propietario, funcion, usuario); else
 if usuario != propietario then

    DBMS_OUTPUT.PUT_LINE('grant execute on '||propietario||'.'||

                  funcion||' to '||usuario);

    identificador:=DBMS_SQL.OPEN_CURSOR;     DBMS_SQL.PARSE (identificador,'grant execute on '||propietario||'.'||

                  funcion||' to '||usuario,1);

    DBMS_SQL.CLOSE_CURSOR(identificador);  end if;
end if;
end loop;

DBMS_OUTPUT.PUT_LINE('...');
DBMS_OUTPUT.PUT_LINE('Fin de Role '||rol);
DBMS_OUTPUT.PUT_LINE('...');

end;
/ Received on Tue May 18 1999 - 05:27:00 CDT