Why doesn’t PL/SQL respect privileges granted by Roles?

by

I think you may be fighting over Invokers rights vs Definers rights.

From Oracle docs:

During a server call, when a DR unit is pushed onto the call stack,
the database stores the currently enabled roles and the current values
of CURRENT_USER and CURRENT_SCHEMA. It then changes both CURRENT_USER
and CURRENT_SCHEMA to the owner of the DR unit, and enables only the
role PUBLIC. (The stored and new roles and values are not necessarily
different.) When the DR unit is popped from the call stack, the
database restores the stored roles and values. In contrast, when an IR
unit is pushed onto, or popped from, the call stack, the values of
CURRENT_USER and CURRENT_SCHEMA, and the currently enabled roles do
not change

So if you want Oracle to “respect the privileges granted by roles”, then perhaps you want to use Invokers rights ( AUTHID CURRENT_USER clause)

More Related Contents:

  1. Place to store PL/SQL block [closed]
  2. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  3. Get counts of all tables in a schema
  4. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  5. PL/SQL – Use “List” Variable in Where In Clause
  6. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  7. Get resultset from oracle stored procedure
  8. Oracle PL/SQL – How to create a simple array variable?
  9. ways to avoid global temp tables in oracle
  10. Number of rows affected by an UPDATE in PL/SQL
  11. PL/SQL print out ref cursor returned by a stored procedure
  12. Create Pivot view in SQL from a SQL table
  13. How to send email using Oracle 10 g Forms
  14. SQL Fiddle Output Error
  15. oracle PL/SQL how to calculate range ip for IPv6 cidr
  16. if (select count(column) from table) > 0 then
  17. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  18. Oracle: how to INSERT if a row doesn’t exist
  19. Functions vs procedures in Oracle
  20. How to see refcursor result/output in Oracle SQL Developer? [duplicate]
  21. How to output result of SELECT statement which is executed using native dynamic SQL?
  22. Reading clob line by line with pl\sql
  23. Cursor For Loop with dynamic SQL-Statement
  24. How to use parameters in a ‘where value in…’ clause?
  25. BEGIN – END block atomic transactions in PL/SQL
  26. Why cannot I create triggers on objects owned by SYS?
  27. No more data to read from socket
  28. automating rollback script oracle
  29. How do I get column datatype in Oracle with PL-SQL with low privileges?
  30. Escaping single quote in PLSQL

Leave a Comment