جد مفيد/ بخصوص عمل Sequence من خلال Packages

[amjad ghanim]

السلام عليكم ورحمة الله وبركاته
تحية طيبة وبعد

الموضوع : مايلي :-

عندي يوزر له صلاحيات connect,resource,dba

من خلاله اذا نفذت الامر التالي من خلال function او procedure او package فانه يتم التنفيذ ويعطيني قيمة
exec immediate|| 'select'||sequence_name.nextval ||'from dual'l

ولكن يا احبائي يا اعزائي انظروا للآتي :-

اذا اردت ان انشاء sequence معين من خلال package او function كالتالي :-

execute immediate ||'create sequence sequence_name1' start with 100 minval 100 nocache';l على سبيل المثال

ماذا يكون الناتج من تنفيذ هذا الامر

الناتج لا شيء حيث لا يعمل ولا يقوم بانشاء هذا sequence في حالت انشاء create sequence

ولكن اذا استخدمت select في sequence فانه يعطيني قيمة

وهذا هو سؤالي : ما هي الصلاحية التي يجب ان اعطيها لليوزر من اجل ان يقوم بانشاء sequence من خلال فنكشن او بوسيجر او باكيج

يمكن السؤال اكثر للمختصين DBA فارجو المساعدة
واتمنى ان يكون السؤال مفهوم

ودمتم في حفظ الله ورعايته.

[TROYMAN]

السلام عليكم...
سؤالك كتير حلو ..
كما نعلم أنه بالنسبة للتنفيذ أي تعليمة تحوي DDL or DCL يجب تضمينها في عبارة Execute Immediate
عند عمل بروسيدجر يحوي هذه العبارة فأن عملية الكومبايلينك تتم عن التنفيذ .
يجب على المستخدم الذي يقوم بإنشاء أي عنصر في الداتا بيس (ضمن السكيما الخاصة به) عن طريق بروسيدجر أو فانكشن أن يملك سماحية خاصة تدعى (Any)
فمثلاً (create any table) أو (Create any Sequence)
ربما نسأل أنفسنا عن سبب وجود هذه السماحية في هذه الحالة و الجواب كالتالي:
لنفرض أن Scott لا يملك سماحية Create any Sequence وأنه قام بتنفيذ العبارة التالية:

SQL> connect scott
Enter password: *****
Connected

 1  create or replace procedure pro_seq
 2  is
 3  begin 4  execute immediate 'create sequence seq6';
 5* end;
SQL> /

Procedure created.

ثم قام بمنح سماحية تنفيذ هذا البروسيدجر إلى يوزر آخر

SQL> grant execute on pro_seq to user1;

Grant succeeded.

SQL> connect user1/user1;
Connected.
SQL> execute scott.pro_seq;
BEGIN scott.pro_seq; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.PRO_SEQ", line 4
ORA-06512: at line 1

عند تنفيذ أي بروسيدجر أو فانكشن من قبل المستخدم الممنوح لسماحية التنفيذ
ينفذ هذا التابع حسب سماحيات المستخدم المانح...
فإذا كان هذا التابع يحوي عبارة إنشاء لعنصر في الداتا بيس فإنه يقوم بإنشائه بشكل افتراضي في السكيما الخاصة بالمستخدم المالك لهذا التابع
هناك حالتين لحل هذه المشكلة:
الأولى تكون بمنح المستخدم الأصلي(المالك) سماحية Create any Sequence
بحيث عند استخدام التابع من قبل مستخدم آخر يقوم بأخذ هذه السماحية عند التنفيذ فقط و ينشأ هذا السيكونس في السكيما الخاصة بـScott كالتالي:

SQL> connect system/orcl;
Connected.
SQL> grant create any sequence to scott;

Grant succeeded.

SQL> connect user1/user1;
Connected.
SQL> execute scott.pro_seq;

PL/SQL procedure successfully completed.
SQL> connect scott/tiger
Connected.
SQL> select seq6.nextval from dual; NEXTVAL
----------
        1

وهكذا نلاحظ أنه تم الإنشاء بنجاح.


الحل الثاني باستخدام Invoker’s rights
يقوم Scott بتعديل جسم البروسيدجر فيصبح:

1  create or replace procedure pro_seq8
 2  Authid Current_user
 3  is
 4  begin 5  execute immediate 'create sequence seq8';
 6* end;
SQL> /

Procedure created.

نلاحظ إضافة عبارة Authid Current_user , تفيد هذه العبارة بأن تنفيذ البروسيدجر سيتم حسب سماحيات المستخدم المنفذ للتابع و ليس المالك الأصلي كما في الحالة السابقة. و يقوم بإنشاء السيكونس في السكيما الخاصة بالمستخدم المنفذ للتابع.

SQL> grant execute on pro_seq8 to user1;

Grant succeeded.

SQL> connect user1/user1;
Connected.
SQL> execute scott.pro_seq8;
BEGIN scott.pro_seq8; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.PRO_SEQ8", line 5
ORA-06512: at line 1

نلاحظ هذا الخطأ بسبب أن المستخدم المنفذ لا يملك سماحية إنشاء سيكونس

SQL> connect system/orcl;
Connected.
SQL> grant create sequence to user1;

Grant succeeded.

SQL> connect user1/user1;
Connected.
SQL> execute scott.pro_seq8;

PL/SQL procedure successfully completed.

SQL> select seq8.nextval from dual; NEXTVAL
----------
        1

SQL> connect scott/tiger;
Connected.
SQL> select pro_seq8.nextval from dual; select pro_seq8.nextval from dual
      *
ERROR at line 1:
ORA-02289: sequence does not exist

When use " Authid Current_user " with stand-alone functions, procedures, or packages:

• Names used in queries, DML, Native Dynamic SQL, and DBMS_SQL package are resolved in the invoker’s schema
• Calls to other packages, functions, and procedures are resolved in the definer’s schema

أرجو أن أكون استطعت تبسيط الأمور...و لأي استفسار أو توضيح أنا جاهز...

تم تعديل 13 مارس 2009 بواسطة TROYMAN

[hrs]

اخ تروي مان .. بصراحة لم افهم كلامك بالضبط

اولا : معروف ان الddl مثل create tableمانقدر نعملها في plsql لذلك نستخدم execute immediate

هل execute immediate لكل الفيرجن لأني استخدمها في 6اي وال10 جي
فهل فهمي صح... لأني جربتها في الفورم بس مايقبلها

 execute immediate ('craete table t 
										a number ,
										b varchar ');

ومرة قرأت اني ممكن استخدم FORMS_DDL('CREATE TABLE T1 .........')
فهل هذا صحيح ام لا





ثانيا:
Authid Current_user
لم افهم فائدة استخدمها .. فمن الطبيعي ان اليوزر سوف يعمل اي كويري حسب سماحياته واضافتها لم تغير شيء .. فقد اضررنا لاعطاء اليوزر السماحية اولا .. فما الفائدة منها اذا..

[TROYMAN]

ملخص عن كلامي السابق:
لحل المشكلة يوجد طريقتين:
1- امتلاك المستخدم لسماحية Create any sequence
2- إضافة عبارة Authid Current_user لجسم البروسيدجر
أما عن التفاصيل فيرجى مراجعة الأمثلة.

اولا : معروف ان الddl مثل create tableمانقدر نعملها في plsql لذلك نستخدم execute immediate

هل execute immediate لكل الفيرجن لأني استخدمها في 6اي وال10 جي
فهل فهمي صح... لأني جربتها في الفورم بس مايقبلها


ومرة قرأت اني ممكن استخدم FORMS_DDL('CREATE TABLE T1 .........')
فهل هذا صحيح ام لا

للأسف يا أخي أنا ما بعرف أبداً بالفورم أنا بشتغل دوت نت , بالنسبة لفهمك حول Execute Immediate فهو صحيح ضمنياً
و بالنسبة للموضوع فهو ينطبق فقط على PL/SQL

ثانيا:
Authid Current_user
لم افهم فائدة استخدمها .. فمن الطبيعي ان اليوزر سوف يعمل اي كويري حسب سماحياته واضافتها لم تغير شيء .. فقد اضررنا لاعطاء اليوزر السماحية اولا .. فما الفائدة منها اذا..

أنا لم أقصد سماحيات اليوز عند تطبيق الاستعلامات...
مثال:
يوزر1 أنشأ بروسيدجر و قام بمنح سماحية التنفيذ ليوزر2 .
عند تنفيذ البروسيدجر من قبل اليوزر2 , ينفذ البروسيدجر حسب سماحيات اليروز1 .
إذا كان البروسيدجر يحوي تعليمات DDL و خاصة إنشاء العناصر (Create Table,Create Serqunce)
عند التنفيذ من قبل يوزر2 يقوم بإنشاء هذا العنصر في سكيمت اليوزر1 (المالك)
ولكي يكون اليوزر2 قادر على إنشاء جدول في سكيما أخرى يجب عليه امتلاك سماحية Create any table
لذلك يجب على اليوزر1 امتلاكها بحيث أنه عند التنفيذ فقط يأخذ اليوزر2 هذه السماحية من اليوزر1 .
أما بالنسبة لعبارة Authid Current_user :
إذا قام اليوزر1 (المالك) بإضافة هذه العبارة إلى البروسيدجر لاحظ الذي يحدث:
1- إذا قام اليوزر2 بتنفيذ هذا البروسيدجر , ينفذ حسب سماحياته و ليس حسب سماحيات المالك كما في الحالة السابقة .
2- ينشأ العنصر النتاتج عن البروسيدجر في السكيما الخاصة بالمنفذ و ليس بالمالك كما في الحالة السابقة
و لذلك يجب على اليوزر2 المنفذ أن يمتلك سماحية Create (لأنه سوف ينشأه في سكيمتهي الخاصة)
لا داعي أن يمتلك اليوزر1 سماحية Create any table


و أفضل مثال لتوضيح الفائدة من Authid Current_user هي :

create or replace procedure pro_Insert_Row(TableName varchar2,ColValue number) is
Authid Current_user
begin execute immediate 'insert into '||TableName||' values '||'('||ColValue||')' ;
end;
/

إذا فرضنا أن مدير قاعدة المعطيات أنشأ هذا البروسيدجر و أراد منحه للمستخدمين بحيث كل منفذ لهذا البروسيجدر يستطيع إدخال سجلات جديدة على الجداول التي له السماحية عليها فقط مثلاً SCOTT يستطيع استخدام البروسيجدر لإدخال سجلات جديدة على الجداول الخاصة به فقط مثل EMP وهكذا........

لأي استفسار آخر أنا جاهز ...

تم تعديل 17 مارس 2009 بواسطة TROYMAN

[hrs]

تسلم اخ تروي مان .. وصلت وجاري التجربة