Re: Use of GRANT,CREATE within a PL/SQL Block

From: Bob King <rking_at_dfw.net>
Date: 1995/11/12
Message-ID: <484mlg$7md_at_fnord.dfw.net>#1/1


monisha_at_pt8404.livonia ((R) Monisha Tope) wrote:

>Hello oracle experts,
 

>I'm sorry if this is a FAQ.
>I'm using Oracle7 Server Release 7.1.6.2.0 and PL/SQL Release 2.1.6.2.0
 

>Does anyone know why I cannot use a GRANT statement within a PL/SQL block??
 

>example

>create trigger grnt_ppn_app_roles_trig
>before insert on tapme.ppn_app_roles for each row
>begin
> if inserting
> and (:new.username is not null)
> and (:new.role is not null)
> then
> grant connect,resource,:new.role to :new.username identified by :new.username;
> end if;
>end;
 

>I get the following error message:
>*
>ERROR at line 1:
>ORA-06550: line 8, column 3:
>PLS-00103: Encountered the symbol "GRANT" which is an unsupported reserved
>word.
>Resuming parse at line 8, column 80.
>ORA-06550: line 9, column 6:
>PLS-00103: Encountered the symbol "IF" when expecting one of the following:
>a PL/SQL variable or double-quoted string
>a simple name
>an optional simple name
>Resuming parse at line 9, column 8.
 

>Any info would be appreciated.
>Thanx in advance,

>Monisha Tope.
>monisha_at_tc1225.pto.ford.com

Look into the 7.1 addendum for the syntax and examples of dbms_sql. It allows you to build sql dynamically, including DDL statements like Grant, Create Table, etc. We've built several DBA packages like this for administration and they are fast.

One caveat - if you write stored objects with dbms_sql, it operates out of the security domain of the person executing the job, not the stored object's owner, just the opposite of DML work.

If you're new to stored procedures:

        User A can have access to delete from employee_table, create a procedure FIRE_EMP(emp_num) and grant user B, who has no privilege on employee_table, execute on FIRE_EMP. User B could then delere from the employee_table when executing the procedure, without having that privilege directly.

        If User A, a DBA, want to allow User B, an Operations Clerk, to set up new users, User A can now set the entire process up in PL/SQL., but User B must still have been granted the appropriate DDL (system) privileges in order to use the procedure. Bob King
Business Phone - (817) 551-8223
** all comments are personal and do not necessarily

   the views of my employer, wife, daughter or dog (a pug) :-) Received on Sun Nov 12 1995 - 00:00:00 CET

Original text of this message