Question What is the purpose of
the authid and definer rights clauses? Are definer
rights the same as the rights when you grant execute on a
procedure of function?
Answer.
The authid definer rights is the opposite of the
authid current_user clause. Essentially the same
as the "grant execute:" clause" the authid definer rights
allows the specified users' grants to be used for privileges
within Oracle.
The use of definer rights are available in stored
procedure, functions and type definitions. Here we
definer a procedure with definer rights. Note that
definer is the default argument when using the authid
clause:
create or replace
procedure
myproc
AUTHID DEFINER
as . . . .
The above definer rights clause would be equivalent to
this grant execute statement:
grant execute on myproc to public;
In this example, we set the type to have definer
rights:
CREATE TYPE address_t
AUTHID DEFINER
AS OBJECT (
address_line1 varchar2(80),
street_name
varchar2(30),
street_number
number,
city varchar2(30),
state_or_province varchar2(2),
country_code varchar2(20));
WARNING: Writing
PL/SQL code with the default authid definer, can
facilitate SQL injection attacks, because an intruder would get
privileges that they would not get if they used authid
current_user.
Below is a a query that shows all
procedures that have authid = 'DEFINER' (definer rights) or
authid = 'CURRENT_USER' (invoker rights).
select
object_name,
procedure_name,
authid
from
dba_procedures;
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|