Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Odd behavior of User_Role_Privs

Posted on 2003-03-18
1
Medium Priority
?
1,152 Views
Last Modified: 2007-12-19
hi everyone,

something i can't explain:

A client had a problem with his menu-security. He had one big power-user FSDDEV who owned all the objects in the application and through public synonyms it were granted to the rest of the company.

He had build in a function that looked at user_role_privs through a cursor like:

   CURSOR get_role_cur
   IS
     SELECT username, granted_role
      FROM user_role_privs
      WHERE granted_role = 'FSD_USERS';

Finally i found out that who ever accessed the function through the public synonyms got to select from this cursor as being the owner FSDDEV and not as being the selecting person (i.e. user avotar). i solved pretty easy (and dirty) by using dba_role_privs.. so no problem there...

the thing i am wondering about is why user_roles_privs behaves this way...


Rick
0
Comment
Question by:Avotar
1 Comment
 
LVL 6

Accepted Solution

by:
M-Ali earned 375 total points
ID: 8164800
By default stored procedures/functions use "definer rights" -- this means that when the code is executed by *any* user, the privileges of the code-creator are in effect.

So above, when AVOTAR executed the code developed by FSDEV, the actual "user" in effect was FSDEV and not AVOTAR.

You can avoid this by using "invoker rights" ie adding
AUTHID CURRENT_USER to your code.


So your code would be something like:

CREATE OR REPLACE FUNCTION function_name (..) RETURN .. AUTHID CURRENT_USER AS
CURSOR get_role_cur
  IS
.........



HTH

Ali
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question