Re: bind variable in GRANT statment?

You don't need PL/SQL for this. Just use plain old sql*plus like this

column usr new_value v_username
 select user usr
from dual;
GRANT all on &v_username..test to X&v_username; GRANT all on &v_username..test to X&v_username;

And that's all folks

Or you need to stuff the grants inside your pl/sql, either with execute immediate
'grant all on '||:v_username||' to '||:vx_username; if you are on 8.1.5 or higher
or dbms_sql (8.0 and lower) to do the job.

Capito?

Hth,

Sybrand Bakker, Oracle DBA

Jennifer Campbell <hexahedron6_at_yahoo.ca> schreef in berichtnieuws 3932C38D.B339A564_at_yahoo.ca...
> Hi,
> db: Server 7.3.4, PL/SQL 2.3.4 on Digital UNIX
> I am having a struggle with bind variables. I wish PL/SQL supported Data
> Control Language statements such as GRANT but it does not. I have a
> PL/SQL block that reads a value into a variable and want to use that
> variable in a GRANT statement. Since I can't GRANT within PL/SQL I am
> trying to use a bind variable that will be accessible after the PL/SQL
> has completed, so I can use the variable to GRANT in SQL*Plus. Do you
> follow so far? Or maybe I'm wrong already.
>
> I'm doing this so a script executed by a user doesn't have to prompt the
> user for his own name. The script grants privileges on tables in the
> user's password account to the user's auto-login account (x-account).
> "User" is available by select from dual or by doing "show user" so my
> idea is to select it into a variable (username), append the auto-login
> prefix "x" to it and make that another variable (xusername). Then use
> these two variables in the grant script. Still with me?
>
> I can get the proper values into the username and xusername variables. I
> can't seem to use them in the grant. Outside of the PL/SQL block I can
> use the PRINT command to see their values, but that's it. I try to
> reference the variables two different ways (actually I've tried many
> more), each gives an error seeming to indicate the value is not being
> substituted for the variable. Any help greatly appreciated. Thanks,
> Jennifer.
>
> Here's the script:
>
> variable v_username varchar2(15)
> variable v_xusername varchar2(16)
> begin
> select user into :v_username from dual;
> :v_xusername := 'X' || :v_username;
> end;
> /
> PRINT v_username
> PRINT v_xusername
> GRANT all on v_username.test to v_xusername;
> GRANT all on :v_username.test to :v_xusername;
>
>
> Here's what happens when I run it:
>
> SQL> @vartest
>
> PL/SQL procedure successfully completed.
>
>
> V_USERNAME
> --------------------------------
> GUMBY
>
>
> V_XUSERNAME
> --------------------------------
> XGUMBY
>
> GRANT all on v_username.test to v_xusername
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
>
> GRANT all on :v_username.test to :v_xusername
> *
> ERROR at line 1:
> ORA-00903: invalid table name
>
> ... of course I am trying for "grant all on gumby.test to xgumby"
>
Received on Mon May 29 2000 - 00:00:00 CDT