Grant permission on SYS function to a user

OraDeveloper
OraDeveloper used Ask the Experts™
on
Hi to dear experts, I have the following problem, I have a SYS function SYS.ud_get_files_list, I have granted permission to execute this function to user Alex:

 GRANT EXECUTE ON SYS.UD_GET_FILES_LIST TO &1;

The user logs in with his password, he can successfully execute the procedure, but he can't recompile it if he make changes.
When he recompile he get's an error: insufficient privileges

I want to give him this permission also, how can I do this ?

hank you

Here is the function:

thank you
CREATE OR REPLACE FUNCTION ud_get_files_list(i_path IN VARCHAR2,i_ext IN VARCHAR2 := NULL, i_file_separator IN VARCHAR2 := '/')
  RETURN dbms_utility.lname_array
IS
/******************************************************************************
   NAME:       ud_get_files_list
   PURPOSE:
 
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        07/02/2007  Sasha        1. Created this function.
 
******************************************************************************/
   pattern   VARCHAR2(1024) := i_path||i_file_separator;
   ns        VARCHAR2(1024);
   v_ret_tab dbms_utility.lname_array;
BEGIN
  sys.dbms_backup_restore.searchfiles(pattern, ns);
  FOR each_file IN (SELECT fname_krbmsft AS name FROM x$krbmsft) LOOP
    IF UPPER(SUBSTR(each_file.name,1,(INSTR(each_file.name,i_file_separator,-1) - 1))) = UPPER(i_path)
       AND
       (i_ext IS NULL 
        OR 
        UPPER(i_ext) = UPPER(SUBSTR(each_file.name,INSTR(each_file.name,'.',-1) + 1))) THEN
      v_ret_tab(v_ret_tab.COUNT + 1) := each_file.name;
    END IF;
  END LOOP;
  RETURN v_ret_tab;
END ud_get_files_list;
/

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
That's correct you need to grant the user the following privilege:
ALTER ANY PROCEDURE
... then that user will be able to create or compile a program unit in another schema.

Author

Commented:
Haa,
that is the point, I've already done this:
" Grant alter any procedure to alex "
But I get the same error message.

I think there is something in the function itself that prevent Alex from compiling a SYS function.

Thanks
Top Expert 2009
Commented:
SYS is treated differently. For example, SELECT ANY TABLE or EXECUTE ANY  PROCEDURE gives access to all non-SYS objects only, due to the security issue. Oracle has builtin protection for SYS objects and catalogs. Prior to version 9 (I think) there were security holes in grant select any table, but when it was fixed, and the new roles (SELECT_CATALOG_ROLE) and sys priv (SELECT ANY DICTIONARY) were added, a backwards compatibility option 07_DICTIONARY_ACCESSIBILITY was provided, but it defaults to false. You could try setting that but that is dangerous too.

ALTER ANY PROCEDURE will not give you access to alter a SYS object.

You can even try connecting as a DBA normally (without SYSDBA or SYSOPER role) and SYSTEM user cannot recompile a sys owned procedure.

Why are you creating a SYS owned procedure and expecting a regular user to recompile it?

I do not think there is a way to allow a user to modify a SYS object without connecting as SYSDBA

I would move that procedure out of SYS schema, or only maintain it as SYS user. What you are attempting seems to be unusual and dangerous practice.

Author

Commented:
Thank you !
Another, quite dangerous, is granting sysdba to alex.  But just troubleshooting, you can try.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial