Home » RDBMS Server » Security » Insufficient privilegs (Oracle 10g)
icon9.gif  Insufficient privilegs [message #333189] Thu, 10 July 2008 15:17 Go to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
I created a new table and granted to users via a role.

It works fine in SQLPLUS and TOAD, but does not in my application (forms 10g). There is "insufficient privileges" when
i try to insert a row.

When i grant(insert on ....) to the users directly,
everything is fine.

So it works, but Our policies don't allow direct grants.

Can you give me some advice, where should I search the solution.


Thanks,

Zoltán Patalenszki
Re: Insufficient privilegs [message #333192 is a reply to message #333189] Thu, 10 July 2008 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So it works, but Our policies don't allow direct grants.
privs acquired via ROLE do NOT apply within PL/SQL (named) procedures.

>Can you give me some advice, where should I search the solution.
Change your policy!
Re: Insufficient privilegs [message #333196 is a reply to message #333192] Thu, 10 July 2008 15:58 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Thanks anacedent.

Is it true when i have trigger on the table, which call a procedure, which select a sequence?
All of the objects are in the same schema.

Then, do I have to grantee separetely the three object to the role?

Regards,
Zoltán Patalenszki
Re: Insufficient privilegs [message #333198 is a reply to message #333189] Thu, 10 July 2008 16:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> do I have to grantee separetely the three object to the role?
Three?
1) table
2) trigger
3) procedure
4) sequence

Role? What role?
Privileges acquired via ROLE do NOT apply within (named) PL/SQL procedures.
Re: Insufficient privilegs [message #333200 is a reply to message #333198] Thu, 10 July 2008 16:20 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
>Three?
four. Embarassed

I musn't grantee directly to the user.

So i thought that If it doesn't aplly I will add all object to the role.
Re: Insufficient privilegs [message #333201 is a reply to message #333189] Thu, 10 July 2008 16:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ROLES are ignored inside PL/SQL named procedures!!!!!!!!!!!!!

You're On Your Own (YOYO)!
icon8.gif  Re: Insufficient privilegs [message #333205 is a reply to message #333201] Thu, 10 July 2008 17:23 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member

Keep your hair on!!

All of my stored procedure, function, or trigger are in the same -objects-owner(as I formerly wrote).
So, the reason is something else.

Sorry, I'm a silly man.
That was my last post.

Thanks for your tolerance!

Zoltán Patalenszki

Re: Insufficient privilegs [message #333277 is a reply to message #333205] Fri, 11 July 2008 02:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Hm, it seems it won't work. So let's grant privileges back to Mike directly:
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 #333389 is a reply to message #333277] Fri, 11 July 2008 08:42 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
I noticed what You and Anacedent tell, but
my all object in the same schema (scott in your sample).

Thanks,
Zoltán Patalenszki

Re: Insufficient privilegs [message #333392 is a reply to message #333389] Fri, 11 July 2008 09:02 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
You keep telling us that all of your objects are in the same schema, so, that's ok.

Let me ask a stupid albeit obvious question: how does your application connect to the database? Which user does it use?
Re: Insufficient privilegs [message #333419 is a reply to message #333389] Fri, 11 July 2008 10:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
my all object in the same schema (scott in your sample).

OK, that's fine. Owner doesn't have any problems while working with these objects. He doesn't need grants as he owns them. Understandable, right?

I also agree with the fact that you have created additional users who access objects not directly, but through given privileges. You chose to grant these privileges not directly but rather through roles. That's OK too.

But none of these users can use objects which are used in named PL/SQL procedures (such as stored procedures or functions) because grants acquired via roles don't work in there. That's just the way it is.

Now, what makes it so difficult for you to accept it? Do you understand what's going on here? I *think* I do but I may be wrong, so - if I/we can not convince you that you are wrong - try to convince us that we are wrong.
Re: Insufficient privilegs [message #333523 is a reply to message #333419] Sat, 12 July 2008 03:11 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Thanks for all of your answer.

I accept all.

My users connect with "mike" with Oracle Forms 10g
and not make named procedure as i wrote before.

And the same roles works fine on all of the other table in the schema.

I've closed this topic, thanks for everyone.

Sorry and thanks:
Zoltán Patalenszki

icon14.gif  Re: Insufficient privilegs [message #339221 is a reply to message #333277] Thu, 07 August 2008 02:05 Go to previous message
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>
Hm, it seems it won't work. So let's grant privileges back to Mike directly:
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.

Previous Topic: How to check the privileges given to user
Next Topic: Wanna help please
Goto Forum:
  


Current Time: Thu Mar 28 11:17:16 CDT 2024