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)
Grants of Select Privilege Thru Stored Procedure [message #689527] Mon, 05 February 2024 10:05 Go to next message
wtolentino
Messages: 388
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.
Re: Grants of Select Privilege Thru Stored Procedure [message #689528 is a reply to message #689527] Mon, 05 February 2024 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Execute
set role none
grant SELECT on TEST1.TEST_SEQUENCE_10 to DMMCODEOWNER;
Conclusion?
Re: Grants of Select Privilege Thru Stored Procedure [message #689529 is a reply to message #689528] Mon, 05 February 2024 10:23 Go to previous messageGo to next message
wtolentino
Messages: 388
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
Re: Grants of Select Privilege Thru Stored Procedure [message #689530 is a reply to message #689529] Mon, 05 February 2024 10:38 Go to previous messageGo to next message
wtolentino
Messages: 388
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>
Re: Grants of Select Privilege Thru Stored Procedure [message #689531 is a reply to message #689530] Mon, 05 February 2024 10:52 Go to previous messageGo to next message
wtolentino
Messages: 388
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.
Re: Grants of Select Privilege Thru Stored Procedure [message #689532 is a reply to message #689531] Mon, 05 February 2024 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68595
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Grants of Select Privilege Thru Stored Procedure [message #689536 is a reply to message #689532] Tue, 06 February 2024 11:22 Go to previous message
wtolentino
Messages: 388
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.
Previous Topic: Other way to get the Schema Size other than dba_segments view
Goto Forum:
  


Current Time: Wed Feb 21 02:35:10 CST 2024