среда, 15 августа 2012 г.

This is a copy of http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

Why do I get a "ORA-01031: insufficient privileges" or "PLS-00201: identifier 'x' must be declared" in my stored procedures?

Roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.This fact is documented application developers guide:

Privileges Required to Create Procedures and FunctionsTo create a stand-alone procedure or function, or package specification or
body, you must meet the following prerequisites:
• You must have the CREATE PROCEDURE system privilege to create a
procedure or package in your schema, or the CREATE ANY
PROCEDURE system privilege to create a procedure or package in
another user’s schema.
Attention: To create without errors, that is, to compile the procedure
or package successfully, requires the following additional privileges:
The owner of the procedure or package must have been explicitly
granted the necessary object privileges for all objects referenced within
the body of the code; the owner cannot have obtained required
privileges through roles.
If the privileges of a procedure’s or package’s owner change, the procedure
must be reauthenticated before it is executed. If a necessary privilege to a
referenced object is revoked from the owner of the procedure (or package), the
procedure cannot be executed.
</quote>Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure.  If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i.  See the PLSQL documentation for more information on this feature and make sure you understand the ramifications).  To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you. 

Почему PL/SQL в коде (представлении/процедуре/триггере/пакете) невозможно использовать чужие объекты. Roles and ORA-00942 table or view does not exist

  Этот вопрос находится в топе вопросов на SQL.RU по СУБД Oracle.
  Краткий ответ такой, потому что грант на эти объекты выдан через роль.
  Ответ в документации по Oracle: "Потмучто в именованном PL/SQL блоке все роли отключаются"
  Чуть более развёрнутый ответ приведён на самом SQL.RU: Почему в своем pl/sql-коде (представлении/процедуре/триггере/пакете) не удается использовать чужой объект - выдается ошибка ORA-00942 table or view does not exist - хотя в sql-запросе или в анонимном pl/sql/блоке все нормально, обращение к объекту другого пользователя проходит без проблем?
Ответ. Скорее всего на чужой объект есть привилегия, выданная только через роль. Для использования объектов другого пользователя или системных привелегий в своих процедурах/триггерах и т.д., а также в job-ах необходимо дать на них привилегию напрямую, не через роль.
Так же это правило следует учесть для динамического SQL: FAQ: ORA-01031: insufficient privileges при выполнении динамического SQL в процедурах (по этой ссылке приводится ещё более развёрнутый ответ)

Ну и самый подробный ответ на этот вопрос нам предлагает ASKTOM.ORACLE.COM.
Привожу перепечатку этого ответа, мало ли он исчезнет:

You Asked

Hi Tom,

We are using 2 schemas, user1, user2.
User1 has user1_table, and created role role1. The following was issued:
grant select, insert, update, delete on user1_table to role1;
grant role1 to user2
User2 created private synonym user1_table for user1.user1_table
and tried to create :

create or replace procedure aa as
aa user1_table.column%TYPE;
begin
null;
end;
/
Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE AA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4      PLS-00201: identifier 'USER1.USER1_TABLE' must be declared
2/4      PL/SQL: Item ignored

Why user2 cannot access user1_table in his procedure, if he has rights on the this table 
via role1?
We do not want to grant  user1_table to user2 directly.

Thanks,

Dusan

 

and we said...

See
http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html
You have no choice but to grant select on user1_table to user2 directly.  It is the only 
way to make it work.

Why is it that roles are not used during the compilation of a procedure?  It has to do 
with the dependency model.  Oracle is not storing exactly WHY you are allowed to access T 
¡V only that you are (directly able to -- not via a role).  Any change to your privileges 
that might cause access to T to go away will cause the procedure to become invalid and 
necessitate its recompilation.  Without roles ¡V that means only ¡§REVOKE SELECT ANY 
TABLE¡¨ or ¡§REVOKE SELECT ON T¡¨ from the definers account or from PUBLIC.  With roles 
¡V it greatly expands the number of times we would invalidate this procedure.  If some 
role that was granted to some role that was granted to this user was modified, this 
procedure might go invalid, even if we did not rely on that privilege from that role.  
ROLES are designed to be very fluid when compared to GRANTS given to users as far as 
privilege sets go. For a minute, let¡¦s say that roles did give us privileges in stored 
objects.  Now, most any time anything was revoked from ANY ROLE we had, or any role any 
role we have has (and so on -- roles can and are granted to roles) -- many of our objects 
would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly 
your entire database must be recompiled! 

Consider the impact of revoking some system privilege from a ROLE, it would be like doing 
that to PUBLIC now -- don't do it, just think about it (if you do revoke some powerful 
system privilege from PUBLIC, do it on a test database).  Revoking SELECT ANY TABLE from 
PUBLIC for example would cause virtually every procedure in the database to go invalid.  
If procedures relied on roles ¡V virtually every procedure in the database would 
constantly become invalid due to small changes in permissions.  Since one of the major 
benefits of procedures is the ¡§compile once, run many¡¨ model ¡V this would be 
disastrous for performance.

Also consider that roles may be

ć Non-default: If I have a non-default role and I enable it and I compile a procedure 
that relies on those privileges, when I log out I no longer have that role -- should my 
procedure become invalid -- why? Why not? I could easily argue both sides.

ć Password Protected: if someone changes the password on a ROLE, should everything that 
might need that role be recompiled?  I might be granted that role but not knowing the new 
password ¡V I can no longer enable it. Should the privileges still be available?  Why or 
Why not?  Again, arguing either side of this is easy.  There are cases for and against 
each.  

The bottom line with respect to roles in procedures with definers rights are:

ć You have thousands or tens of thousands of end users. They don't create stored objects 
(they should not). We need roles to manage these people.  Roles are designed for these 
people (end users).

ć You have far fewer application schema's (things that hold stored objects). For these 
we want to be explicit as to exactly what privileges we need and why.  In security terms 
this is called the concept of 'least privileges' -- you want to specifically say what 
privilege you need and why you need it. If you inherit lots of privileges from roles you 
cannot do that effectively. We can manage to be explicit since the number of development 
schemas is SMALL (but the number of end users is large)...

ć Having the direct relationship between the definer and the procedure makes for a much 
more efficient database.  We recompile objects only when we need to, not when we might 
need to.  It is a large efficiency enhancement.

четверг, 9 августа 2012 г.

Чудесная функция COALESCE

  Как всем известно в Oracle SQL есть замечательная функция NVL, которая позволяет вывести результат "по умолчанию", в случае если значение колонки равно NULL.
  На протяжении всей своей оракловой жизни я успешно пользовался этой функцией, жил не тужил, хотя нет, иногда мне становилось грустно, когда мне надо было вывести в колонку значение которой надо было взять из других колонок, при этом оно не должно было быть NULL, если все колонки равны NULL, то надо было вывести значение по умолчанию. Обычно эту задачу я решал при помощи CASE, но это требовало много сил и писанины. А вчера произошло чудо, оказывается есть замечательная функция COALESCE, которая замечательно заменяет этот CASE, ниже привожу её синтаксис и пример из документации по Oracle.


COALESCE (expr1, expr2, ..., exprn), for n>=3
Пример:
SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050
   ORDER BY product_id, list_price, min_price, "Sale";

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769         48                  43.2
      1770                    73         73
      2378        305        247      274.5
      2382        850        731        765
      3355                                5

Синтаксис и пример взяты документации по SQL СУБД Oracle