|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Insufficient privilegs [message #333277 is a reply to message #333205] |
Fri, 11 July 2008 02:14   |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
| Quote: | All of my stored procedure, function, or trigger are in the same -objects-owner. So, the reason is something else.
|
I'm not sure I understood what you are saying, but let me try: you have created certain objects (tables, procedures, ...) logged as user Scott (that would be "are in the same -objects-owner").
There's also user Mike who would like to use objects owned by Scott.
Therefore, according to your policy, you don't want to grant privileges directly from Scott -> Mike, but have created a role so grant goes as Scott -> Role -> Mike.
That works fine in SQL, but not in PL/SQL named procedures. Here's an example: connected as Scott, I'll create a function, grant privileges directly to Mike and test how it works:SQL> connect scott/tiger@ora10
Connected.
SQL> create function fun_now return date as
2 begin
3 return sysdate;
4 end;
5 /
Function created.
SQL> grant execute on fun_now to mike;
Grant succeeded.
SQL> connect mike/lion@ora10
Connected.
SQL> -- SQL test:
SQL> select scott.fun_now from dual;
FUN_NOW
--------
11.07.08
SQL> -- PL/SQL ANONYMOUS BLOCK test:
SQL> declare
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_line(retval);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> /
11.07.08
PL/SQL procedure successfully completed.
SQL> -- PL/SQL NAMED PROCEDURE test:
SQL> create procedure prc_test as
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_Line(retval);
6 end;
7 /
Procedure created.
SQL> exec prc_test;
11.07.08
PL/SQL procedure successfully completed.
SQL>
So far, so good.
Now let's create a role and grant privileges via role:SQL> connect scott/tiger@ora10
Connected.
SQL> revoke all on fun_now from mike;
Revoke succeeded.
SQL> create role my_role;
Role created.
SQL> grant execute on fun_now to my_role;
Grant succeeded.
SQL> grant my_role to mike;
Grant succeeded.
SQL> connect mike/lion@ora10
Connected.
SQL> -- SQL test:
SQL> select scott.fun_now from dual;
FUN_NOW
--------
11.07.08
SQL> -- PL/SQL ANONYMOUS BLOCK test:
SQL> declare
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_line(retval);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
11.07.08
PL/SQL procedure successfully completed.
SQL> -- PL/SQL NAMED PROCEDURE test:
SQL> create or replace procedure prc_test as
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_Line(retval);
6 end;
7 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE PRC_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
4/16 PL/SQL: ORA-00904: : invalid identifier
SQL> SQL> connect scott/tiger@ora10
Connected.
SQL> grant execute on fun_now to mike;
Grant succeeded.
SQL> connect mike/lion@ora10
Connected.
SQL> alter procedure prc_test compile;
Procedure altered.
SQL> set serveroutput on
SQL> exec prc_test;
11.07.08
PL/SQL procedure successfully completed.
SQL>
Ta-daaaa! It works again.
I hope you noticed the difference between granting privileges directly to user or via role, as well as influence of privileges acquired via roles to anonymous and named PL/SQL procedures.
|
|
|
|
|
|
|
|
|
|
|
|
 Re: Insufficient privilegs [message #339221 is a reply to message #333277] |
Thu, 07 August 2008 02:05 |
grr.rajareddy
Messages: 1
Registered: August 2008
|
Junior Member |
|
|
| Littlefoot wrote on Fri, 11 July 2008 12:44 | | Quote: | All of my stored procedure, function, or trigger are in the same -objects-owner. So, the reason is something else.
|
I'm not sure I understood what you are saying, but let me try: you have created certain objects (tables, procedures, ...) logged as user Scott (that would be "are in the same -objects-owner").
There's also user Mike who would like to use objects owned by Scott.
Therefore, according to your policy, you don't want to grant privileges directly from Scott -> Mike, but have created a role so grant goes as Scott -> Role -> Mike.
That works fine in SQL, but not in PL/SQL named procedures. Here's an example: connected as Scott, I'll create a function, grant privileges directly to Mike and test how it works:SQL> connect scott/tiger@ora10
Connected.
SQL> create function fun_now return date as
2 begin
3 return sysdate;
4 end;
5 /
Function created.
SQL> grant execute on fun_now to mike;
Grant succeeded.
SQL> connect mike/lion@ora10
Connected.
SQL> -- SQL test:
SQL> select scott.fun_now from dual;
FUN_NOW
--------
11.07.08
SQL> -- PL/SQL ANONYMOUS BLOCK test:
SQL> declare
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_line(retval);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> /
11.07.08
PL/SQL procedure successfully completed.
SQL> -- PL/SQL NAMED PROCEDURE test:
SQL> create procedure prc_test as
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_Line(retval);
6 end;
7 /
Procedure created.
SQL> exec prc_test;
11.07.08
PL/SQL procedure successfully completed.
SQL>
So far, so good.
Now let's create a role and grant privileges via role:SQL> connect scott/tiger@ora10
Connected.
SQL> revoke all on fun_now from mike;
Revoke succeeded.
SQL> create role my_role;
Role created.
SQL> grant execute on fun_now to my_role;
Grant succeeded.
SQL> grant my_role to mike;
Grant succeeded.
SQL> connect mike/lion@ora10
Connected.
SQL> -- SQL test:
SQL> select scott.fun_now from dual;
FUN_NOW
--------
11.07.08
SQL> -- PL/SQL ANONYMOUS BLOCK test:
SQL> declare
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_line(retval);
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> /
11.07.08
PL/SQL procedure successfully completed.
SQL> -- PL/SQL NAMED PROCEDURE test:
SQL> create or replace procedure prc_test as
2 retval date;
3 begin
4 select scott.fun_now into retval from dual;
5 dbms_output.put_Line(retval);
6 end;
7 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE PRC_TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
4/16 PL/SQL: ORA-00904: : invalid identifier
SQL> SQL> connect scott/tiger@ora10
Connected.
SQL> grant execute on fun_now to mike;
Grant succeeded.
SQL> connect mike/lion@ora10
Connected.
SQL> alter procedure prc_test compile;
Procedure altered.
SQL> set serveroutput on
SQL> exec prc_test;
11.07.08
PL/SQL procedure successfully completed.
SQL>
Ta-daaaa! It works again.
I hope you noticed the difference between granting privileges directly to user or via role, as well as influence of privileges acquired via roles to anonymous and named PL/SQL procedures.
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
