OraFAQ Forum: Server Administration » Grants of Select Privilege Thru Stored Procedure

Home » RDBMS Server » Server Administration » Grants of Select Privilege Thru Stored Procedure (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.21.0.0.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Grants of Select Privilege Thru Stored Procedure [message #689527]

Mon, 05 February 2024 10:05

wtolentino
Messages: 429
Registered: March 2005

Senior Member

I have this procedure

SQL> create or replace procedure sqldba.proc_test_grant as
  2  begin
  3   execute immediate 'grant SELECT on TEST1.TEST_SEQUENCE_10 to DMMCODEOWNER';
  4  end;
  5  /

Procedure created.

When run the procedure is run by other schema

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
DMMCODEOWNER

SQL> exec sqldba.proc_test_grant;
BEGIN sqldba.proc_test_grant; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SQLDBA.PROC_TEST_GRANT", line 3
ORA-06512: at line 1
SQL

But when run direclty by the schema who owned the procedure it is working fine

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
SQLDBA

SQL> grant SELECT on TEST1.TEST_SEQUENCE_10 to DMMCODEOWNER;

Grant succeeded.

SQL>

I thinkI am missing something. Please advise. Thank you.

Report message to a moderator

Re: Grants of Select Privilege Thru Stored Procedure [message #689528 is a reply to message #689527]

Mon, 05 February 2024 10:09

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Execute

set role none
grant SELECT on TEST1.TEST_SEQUENCE_10 to DMMCODEOWNER;

Conclusion?

Report message to a moderator

Re: Grants of Select Privilege Thru Stored Procedure [message #689529 is a reply to message #689528]

Mon, 05 February 2024 10:23

wtolentino
Messages: 429
Registered: March 2005

Senior Member

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
SQLDBA

SQL> set role none;

Role set.

SQL> grant SELECT on TEST1.TEST_SEQUENCE_10 to DMMCODEOWNER;
grant SELECT on TEST1.TEST_SEQUENCE_10 to DMMCODEOWNER
                      *
ERROR at line 1:
ORA-01031: insufficient privileges

Report message to a moderator

Re: Grants of Select Privilege Thru Stored Procedure [message #689530 is a reply to message #689529]

Mon, 05 February 2024 10:38

wtolentino
Messages: 429
Registered: March 2005

Senior Member

using the same owner of the procedure to execute the procedure

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
SQLDBA

USER
--------------------------------------------------------------------------------
SQLDBA

SQL> exec sqldba.proc_test_grant;
BEGIN sqldba.proc_test_grant; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SQLDBA.PROC_TEST_GRANT", line 3
ORA-06512: at line 1

but works when

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
SQLDBA

SQL> grant SELECT on TEST1.TEST_SEQUENCE_10 to DMMCODEOWNER;

Grant succeeded.

SQL>

Report message to a moderator

Re: Grants of Select Privilege Thru Stored Procedure [message #689531 is a reply to message #689530]

Mon, 05 February 2024 10:52

wtolentino
Messages: 429
Registered: March 2005

Senior Member

I figure it out, had to grant the schema owner of the procedure the privileges with grant option.

grant SELECT on TEST1.TEST_SEQUENCE_10 to sqldba admin option;

when the procedure is executed it work.

SQL> select user from dual;

USER
--------------------------------------------------------------------------------
DMMCODEOWNER

SQL> exec sqldba.proc_test_grant;

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Grants of Select Privilege Thru Stored Procedure [message #689532 is a reply to message #689531]

Mon, 05 February 2024 12:34

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The issue is that, by default, roles are disabled in a procedure: you have 2 ways to declare a procedure with owner direct privileges (AUTHID DEFINER which is the default) and with the privileges of the caller including those included in the current roles (AUTHID CURRENT_USER).

The drawback of your solution is that you'll have to grant to the owner any privilege object privilege you would like to grant in your procedure.
A more efficient but less secure solution, is to directly grant "grant any object privilege" privilege to the procedure owner.

Report message to a moderator

Re: Grants of Select Privilege Thru Stored Procedure [message #689536 is a reply to message #689532]

Tue, 06 February 2024 11:22

wtolentino
Messages: 429
Registered: March 2005

Senior Member

Right, instead of the "admin option" I use a procedure owned by the schema that owns the same sequence. To grant the privilege. Thanks.

Report message to a moderator

Previous Topic:	Other way to get the Schema Size other than dba_segments view
Next Topic:	ALLOW_LEGACY_RECO_PROTOCOL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 09 21:57:35 CDT 2026