Thanks for the question, Karthikeyan.

Asked: July 31, 2003 - 2:22 pm UTC

Last updated: August 02, 2010 - 2:48 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom

Thanks very much for your valuable time to answer this question.

Owner of TABLE_A table is USER_A.

USER_A granted PUBLIC SYNONYM with SELECT only on TABLE_A to USER_B.

USER_A created procedure PROC_A to insert record to TABLE_A.

USER_A granted PUBLIC SYNONYM with EXECUTE only on PROC_A to USER_B.

Now, USER_B has privilege SELECT ONLY on TABLE_A and EXECUTE only on PROC_A.

When USER_B executes the PROC_A procedure, it inserts record to TABLE_A.

But the USER_B has only SELECT privilege on table TABLE_A?

Please correct me, if I am missing something here.

I would appreicate, if you give your valuable time and point.

Thanks
Karthik

and Tom said...

that is the sheer positive BEAUTY of stored procedures....

the execute with the base privileges (minus roles) of the DEFINER (owner) of the procedure.

the procedure becomes an executable process that others can execute (when permitted) and executes with the privs of the definer.

This allows you to build a seriously secure system. Look at what you just did -- in order for B to insert into TABLE_A, they must execute your specific code that can do whatever security validation, auditing, logging, translations, whatever it needs

So, now user B can insert into TABLE_A in an extremely secure manner.

Suppose as a DBA you wanted to let a help desk person reset the passwords for some set of application users? Well, Oracle doesn't have a "grant alter user usera, userb, userc to HELP_DESK" command -- but the DBA can code:

create procedure reset_password( p_username in varchar2, p_pw in varchar2 )
as
begin

-- could be a table lookup of course
if ( p_username in ( 'A', 'B', 'C', .... ) )
then
execute immediate 'alter user ' || p_username || ' identified by ' ||
p_pw;
else
-- maybe log that they tried to alter some user
raise_application_error( -20001, 'hey, you cannot do that!' );
end if;
end;

Now, the owner of that procedure has the ALTER USER privilege and by granting execute on it to HELP_DESK, they allow that person/role to alter any of the users in the list.

You use procedures to secure your database...

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Toms

A reader, February 23, 2004 - 2:00 pm UTC

Privileges

Vaij, July 31, 2010 - 11:41 pm UTC

By default, if you grant execute a proc. to any user, the internal statements get executed by default regardless of the privileges executor has.

But this can be controlled by using keyword authid in the procedure which does the following,

definer - Inherit the grants of the creator.(definer)
current_user - Use the grants of the executor.

August 02, 2010 - 9:02 am UTC

it is a lot lot LOT more complex than that. I hesitate to even bring it up because the use of invokers rights (current_user) are so very rare as to be a feature that MOST developers will never ever even consider using.

They (invokers rights) are useful for one thing:

writing utility code. Like a utility routine that accepts a query as input and writes the output of the query to a file. The invoker is the one that needs to be able to run the query, the routine just fetches and prints it. So, that would make sense as an invokers rights routine.

or a routine to gather statistics on a table ( like dbms_stats, oh wait, we already wrote that :) )

It is NOT useful in most application code - especially since people do not log in as themselves anymore (3 tier broke that very nice feature of the database, 3 tier that doesn't use n-tier proxy authentication anyway...)

SQL Injection

radino, August 02, 2010 - 1:14 pm UTC

Code:
execute immediate 'alter user ' || p_username || ' identified by ' || p_pw;

is injectable through the p_pw parameter.

p_pw: password quota 100T on users.

it's not critical, but it could be abused.

August 02, 2010 - 1:34 pm UTC

it is critical, it was a mistake to not have it right in the first place.

this should validate the inputs using dbms_assert or some other package - definitely. The username is OK since we sanitize that, but the password is subject to sql injection attacks definitely.

Thanks for pointing it out. Use

dbms_assert.simple_sql_name(p_pw)

instead of just p_pw in the code

SQL injection

Michel Cadot, August 02, 2010 - 2:23 pm UTC

To Radino,

Do you think that "quota unlimited on system" is not critical?

To Tom,

You say "The username is OK since we sanitize that" but it can be sql injected too:

SQL> declare 
  2    p_usr varchar2(100);
  3  begin
  4    p_usr := 'toto identified by toto quota unlimited on system --';
  5    execute immediate 'create user '||p_usr||' identified by something';
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select max_bytes from dba_ts_quotas
  2  where username='TOTO' and tablespace_name='SYSTEM';
 MAX_BYTES
----------
        -1

Regards
Michel

August 02, 2010 - 2:48 pm UTC

my code sanitized username already:

   -- could be a table lookup of course
  <b> if ( p_username in ( 'A', 'B', 'C', .... ) ) 
  </b> then
       execute immediate 'alter user ' || p_username ||

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks Toms

Privileges

SQL Injection

SQL injection

More to Explore

PL/SQL demos

PL/SQL docs

Object Privilege

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks Toms

Privileges

SQL Injection

SQL injection

More to Explore

PL/SQL demos

PL/SQL docs