bind variable in GRANT statment?

From: Jennifer Campbell <hexahedron6_at_yahoo.ca>
Date: 2000/05/29
Message-ID: <3932C38D.B339A564@yahoo.ca>#1/1

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