Re: Have you ever run grants from plsql?

Patxi wrote:

> I'm trying to do just this with dinamic SQL.
>
> Someone gave my user the DBA role & now I have to create users & run its
> grants using PL/SQL.
>
> I'm trying with SQL Navigator & when I debug my procedure SQL Navigator's
> debugger tells me the following
>
> Error -1932: ORA-01932: ADMIN option not granted for role 'CONNECT'
> ORA-01932: ADMIN option not granted for role 'CONNECT'
> ORA-06512: at line 9
>
> It happens just after parse.
>
> The code I've used is:
>
> --Grant connect
> v_run := 'grant connect to ' || UPPER (pi);--v_run varchar2 (200)
> v_run_id := DBMS_SQL.open_cursor;--v_run_id binary_integer
> DBMS_SQL.parse (v_run_id, v_run, DBMS_SQL.v7);
> w_run := DBMS_SQL.execute (v_run_id);--w_run binary_integer
> DBMS_SQL.close_cursor (v_run_id);
>
> I've been looking for some documentation & I've found this procedure
>
> PROCEDURE DBMS_AQADM.GRANT_TYPE_ACCESS (user_name IN VARCHAR2);
>
> I don't understand what is this procedure useful for. Could it be useful for
> me?
>
> Thank you in advance,
>
> Patxi.

Having the DBA role does not give you permission to grant anything to anybody.

What you are trying to do can be done if you are given the correct privileges with the option to grant them. It appears that this is not the case.

But in any case you should never grant the CONNECT role to anyone. Instead grant CREATE SESSION and such other individual privileges such as CREATE TABLE as may be required. Granting CONNECT is just plain dangerous and I can not understand why Oracle insists on sending it out as a database default.

Daniel A. Morgan Received on Mon May 14 2001 - 23:53:31 CDT