RE: Grant all question

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 19 Jul 2006 16:11:40 -0400

All user only or database wide privileges such as truncate table can be
handled by creating a procedure under the schema owner that issues the
DDL via dynamic SQL.  Only an execute grant on the procedure is then
required. Again I would grant the execute procedure to a ROLE and grant
the ROLE to the target users.  This limits the damage the end users can
do to the target schema.  With a little thought logic to limit the
procedure to truncating only specific tables can be added limiting the
damage to specific tables.

HTH -- Mark D Powell --


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Alex Gorbachev
Sent: Wednesday, July 19, 2006 2:49 PM
To: oracle.rdbms@xxxxxxxxx
Cc: Oracle-L Freelists
Subject: Re: Grant all question

Just a bit sideways from the Q:
I would seriously question that approach and rather propose to follow
more minimalistic approach giving only grants that are needed.
In addition, there is no way to grant certain operations. For example,
in order to truncate the table, user must either own this table or have
DROP ANY TABLE privilege. Are you ready to grant DROP ANY TABLE?
;-) Of course, there is a workaround to write a PL/SQL procedure.


2006/7/19, Sinardy Xing <oracle.rdbms@xxxxxxxxx>:
>  Something like this
>  GRANT ALL OF a TO b;


--
Best regards,
Alex Gorbachev

http://blog.oracloid.com
--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: