jump to navigation

Procedure: Blocks data access through Roles…..be aware February 13, 2010

Posted by sendtoshailesh in Uncategorized.
Tags:
trackback

Sometime life itself hijack you. Its being very long time I have blogged anything here. I wish this way of schedule should not continue……..

Last week I was developing certain stored procedures and I recalled one thing which learnt long back. but at the time of compiling I forgotten those things.

After a full-time troubleshoot I did recalled those things and that is – Any privilege granted through the roles are always blocked at PL/SQL compile and as well as runtime. I went through lot of tech article and metalink doc and concluded that Oracle blocks role routed privs because of security and performance reason. While compilation PL/SQL engine does checks the privs which are granted through roles. Let me explain with Hands-on

Let us create a role and grant some HR schema privs to a role and in-turn grant that role to SCOTT.

SQL> conn sys as sysdba
Connected.
SQL> drop role hr_role;

Role dropped.

SQL> create role hr_role;

Role created.

SQL> grant select on hr.regions to hr_role;

Grant succeeded.

SQL> grant hr_role to scott;

Grant succeeded.

Now, Lets connect with scott and try to create a procedure to test this grant.

SQL> conn scott/tiger
Connected.
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure hr_test
2 is
3 v varchar2(4000);
4 begin
5 select region_name
6 into v
7 from hr.regions
8 where region_id=1;
9 dbms_output.put_line(v);
10* end;
SQL> /

Warning: Procedure created with compilation errors.

OOPS………There is some error. Let us debug the error.

SQL> show error
Errors for PROCEDURE HR_TEST:

LINE/COL ERROR
——– —————————————————————–
5/1 PL/SQL: SQL Statement ignored
7/9 PL/SQL: ORA-00942: table or view does not exist

So….above error shows either HR REGIONS table does not exist or Scott do not have privs on it.
OK. Let do one things. We can try from scott to run same SQL which procedure have.

SQL> select region_name from hr.regions where region_id=1;

REGION_NAME
————————-
Europe

WOW…but surprising. HR Data access which is restricted through procedure is not restricted through direct on the prompt query. Actually while compiling PL-SQL engine does not consider Roles. so this procedure could be successful only when same privs is granted directly to scott irrespective of role. Lets do that and test.

SQL> conn sys as sysdba
Connected.
SQL> drop role hr_role;

Role dropped.

SQL> grant select on hr.regions to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> ed
Wrote file afiedt.buf

1 create or replace procedure hr_test
2 is
3 v varchar2(4000);
4 begin
5 select region_name
6 into v
7 from hr.regions
8 where region_id=1;
9 dbms_output.put_line(v);
10* end;
SQL> /

Procedure created.

Great….. as per our expectation. Compiler has permitted this access or we can say direct object grant are considered by PL-SQL engine. Let us test the procedure.

SQL> set serveroutput on

SQL> exec hr_test
Europe

PL/SQL procedure successfully completed.

SO…never forget any privs granted through roles are never accessible through procedures.

EnjoooooooooooY!!

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: