Home » SQL & PL/SQL » SQL & PL/SQL » Procedure owner rights works with DML but not with DDL
Procedure owner rights works with DML but not with DDL [message #259471] Wed, 15 August 2007 13:00 Go to next message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
I created a procedure to execute commands from a file.

I'm successful at running DML commands - such as inserts and deletes on tables in which the invoker has no rights. But when it comes to DDL it doesn't work.

Does DDL commands only works with invoker rights? Example, only a user with 'CREATE' grant can execute the procedure to create a table, even when the owner of the procedure has this right, and the invoker has execute rights on the procedure?

Thanks,
Christian
Re: Procedure owner rights works with DML but not with DDL [message #259482 is a reply to message #259471] Wed, 15 August 2007 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DDL work when they are used in the correct environment.
Fix your environment.

Regards
Michel
Re: Procedure owner rights works with DML but not with DDL [message #259486 is a reply to message #259471] Wed, 15 August 2007 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>But when it comes to DDL it doesn't work.
My car doesn't work. Tell me how to fix my car.

Please use CUT & PASTE to show whole session; your inputs & Oracle's output.
Re: Procedure owner rights works with DML but not with DDL [message #259487 is a reply to message #259482] Wed, 15 August 2007 13:23 Go to previous messageGo to next message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
Michel Cadot wrote on Wed, 15 August 2007 15:16
DDL work when they are used in the correct environment.
Fix your environment.


Can u please be more specific. I'm not a DBA, I'm just a developer. I can talk to the DBA to help me, but can u give me some hints as to what is causing this?

Thanks,
Christian
Re: Procedure owner rights works with DML but not with DDL [message #259489 is a reply to message #259487] Wed, 15 August 2007 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have no clue of what you've done so I give the answer that I can with what you give.

Regards
Michel
Re: Procedure owner rights works with DML but not with DDL [message #259490 is a reply to message #259471] Wed, 15 August 2007 13:28 Go to previous messageGo to next message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
This is the procedure. It receives a file as a paramater. Then it reads each command (separated by a "\") and executes them one by one.

CREATE OR REPLACE PROCEDURE exec_from_file (
   dir_in    IN   VARCHAR2
 , file_in   IN   VARCHAR2
 , ars_in    IN   INTEGER
 , err_abort IN   BOOLEAN
)
IS
   l_path_spool varchar2(200);
   l_file_in UTL_FILE.file_type;
   l_filename_out varchar2(200);
   l_file_out UTL_FILE.file_type;

   l_resultado varchar2(1000);
   l_operacao PLS_INTEGER;
   
   l_newline char(2) := CHR(13)||CHR(10);
   l_lines DBMS_SQL.varchar2s;
   
   l_sql_exception EXCEPTION;
   l_hostname varchar2(200);

   procedure sql_execute( cmd IN DBMS_SQL.varchar2s, 
                          spool UTL_FILE.file_type, 
                          result OUT varchar2 )

   IS
   l_cur PLS_INTEGER := DBMS_SQL.open_cursor;
   
      BEGIN
         DBMS_SQL.parse ( l_cur
                        , cmd
                        , cmd.FIRST
                        , cmd.LAST
                        , TRUE
                        , DBMS_SQL.native
                        );
         
         result := DBMS_SQL.EXECUTE ( l_cur );
         l_operacao := DBMS_SQL.last_sql_function_code;
         
         IF l_operacao = '3' OR l_operacao = '5' OR l_operacao = '9'
         THEN
             result := '-- ' || result || ' linha(s) afetada(s).';
         ELSE
             result := '-- Realizado com sucesso!';
         END IF;
         
         UTL_FILE.put_line( spool, result || l_newline );
      
         DBMS_SQL.close_cursor ( l_cur );

      EXCEPTION
         WHEN OTHERS
         THEN
            result := '-- ERRO: '|| DBMS_UTILITY.format_error_stack;
            UTL_FILE.put_line( spool, result || l_newline );
      
            IF DBMS_SQL.is_open ( l_cur )
            THEN
               DBMS_SQL.close_cursor ( l_cur );
            END IF;
            
         RAISE l_sql_exception;
   END sql_execute;  
 
   BEGIN
--      IF ars_in IS NULL OR ars_in = 0
--      THEN
--          DBMS_OUTPUT.put_line('O número do ARS informado é inválido!');
--      END IF;
   
      l_file_in := UTL_FILE.fopen ( dir_in, file_in, 'R' );
      
      SELECT vl_parametro INTO l_path_spool
      FROM parametro 
      WHERE cd_parametro = 'PATH_SPOOL_PP_EXEC';
      
      l_filename_out := LOWER(replace(UPPER(file_in), 'SQL', 'LOG'));
      l_file_out := UTL_FILE.fopen ( l_path_spool, l_filename_out, 'W' );
      
      LOOP
         <<read_one_line>>
         BEGIN
            UTL_FILE.get_line ( l_file_in, l_lines ( l_lines.COUNT + 1 ));
            IF RTRIM ( l_lines ( l_lines.LAST )) = '/'
            THEN
              l_lines.DELETE ( l_lines.LAST );
              FOR i IN l_lines.FIRST..l_lines.LAST
              LOOP
                  UTL_FILE.put_line( l_file_out, '* ' || l_lines(i) );
              END LOOP;

              sql_execute( l_lines, l_file_out, l_resultado  );

              l_lines.DELETE;
            END IF;
                
         EXCEPTION
            WHEN l_sql_exception
            THEN
                IF err_abort
                THEN
                    UTL_FILE.put_line( l_file_out, '--> Abortando restante do script [err_abort = TRUE]!');
                    EXIT;
                END IF;
            WHEN NO_DATA_FOUND
            THEN
               EXIT;
         END read_one_line;
      END LOOP;
      
      SELECT SYS_CONTEXT('USERENV', 'HOST') INTO l_hostname FROM DUAL;
      
      INSERT INTO L_ATUALIZACOES_DB (dt_atualizacao, id_login, 
                                    ds_hostname, nm_arquivo, 
                                    nr_ars, nm_arquivo_result)
             VALUES (SYSDATE, user, l_hostname, file_in, 
                    ars_in, l_filename_out);
            
      UTL_FILE.fclose( l_file_in );
      UTL_FILE.fclose( l_file_out );

END exec_from_file;


And this is the result file.
* INSERT INTO CHRISTIANMSISDN
* VALUES ('123445', '123', '123', '123')
-- 1 row(s) affected.

* INSERT INTO CHRISTIANMSISDN
* VALUES ('123446', '123', '123', '123')
-- 1 row(s) affected.

* DELETE FROM CHRISTIANMSISDN
* WHERE MSISDN = '123445'
-- 1 row(s) affected.

* UPDATE CHRISTIANMSISDN
* SET MSISDN = '123447'
* WHERE MSISDN = '123446'
-- 1 row(s) affected.

* CREATE TABLE a_test ( campo1 integer not null )
-- ERRO: ORA-01031: insufficient privileges


User A is the owner of the procedure and has ALL privileges in the database, it can create, drop, do anything. I'm using user B to execute the procedure. It has only SELECT right on the tables.
As u can see, the DML commands work fine, inserts and all. But the create table fails.

[Updated on: Wed, 15 August 2007 13:35]

Report message to a moderator

Re: Procedure owner rights works with DML but not with DDL [message #259494 is a reply to message #259490] Wed, 15 August 2007 13:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:227413938857
Check these two links. It explains in detail why you are getting this problem ORA-1031: Insufficient Priveleges.

Hope that helps

cheers

Raj

[Updated on: Wed, 15 August 2007 13:57]

Report message to a moderator

Re: Procedure owner rights works with DML but not with DDL [message #259514 is a reply to message #259494] Wed, 15 August 2007 15:31 Go to previous messageGo to next message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
S.Rajaram wrote on Wed, 15 August 2007 15:50
http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:227413938857
Check these two links. It explains in detail why you are getting this problem ORA-1031: Insufficient Priveleges.

Hope that helps

cheers

Raj

Thanks, that helped a lot. It didn't solve the problem (what I want cannot be done), but explained exactly what is going on. =)
Re: Procedure owner rights works with DML but not with DDL [message #259593 is a reply to message #259494] Thu, 16 August 2007 01:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure? Read the links again, definitely what you want is there. If you still cannot find it, come back to us, will let you know what needs to be done. I am doing it deliberately so that you will learn more.

Good Luck

Cheers

Raj
Re: Procedure owner rights works with DML but not with DDL [message #259770 is a reply to message #259471] Thu, 16 August 2007 07:33 Go to previous message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
Raj, first of all, thanks for attention,

from what I learned in this link (http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html), roles are not applied when a procedure is executed to give the owner its privileges. In other words, if I do this and get this result:
> -- I'm user A (owner of the procedure)
> set role none;
> create table x (field 1 number);
> ORA-01031 insufficient privileges.


If user A is associated with a role that gives hime the 'create table privilege' (which is the case), user B will not be able to execute a procedure (with definer rights) to create a table, since the privilege is associated with the role and not the user/owner. It would only work if the 'create table' privlege was associated DIRECTLY to user A.

Every other command will work, since I'm working on a database where every object is owned by A, so I won't have problems manipulating data on those objects even if B (which has no privs) is calling it.

Right?
Previous Topic: row level
Next Topic: Help on rank() function
Goto Forum:
  


Current Time: Fri Dec 09 05:57:15 CST 2016

Total time taken to generate the page: 0.06105 seconds