0

Dabatase: Oracle

User: Not dba

In my oracle database, im able to do the following:

SELECT * FROM ALL_SYNONYMS

But when i do it in pl/sql, it is able to compile and execute, but the dbms.putline prints nothing:

CREATE PROCEDURE PROCEDURE_1 AS
  strName VARCHAR2(50);
  CURSOR C_NAME IS SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE TABLE_OWNER = 'USER1';

BEGIN

   FOR R_NAME in C_NAME
   LOOP
     strName := R_NAME.SYNONYM_NAME;
     dbms_output.put_line(strName);        <-----
   END LOOP;


END PROCEDURE_1

Do we need to grant any role to a non-dba user for All_Synonyms table?

|improve this question
1

You shouldn't need any special privilege to view the ALL_* views.

If the procedure is owned by and is running as USER1 then it could be that USER1 has not been granted access to any synonyms.

If the procedure is owned by a user other than USER1 it could be that that user hasn't been granted access to any synonyms.

It all comes down to who's credentials are actually being used to run the procedure - yours, USER1, procedure owner - and what synonyms or grants to synonyms that user has.

|improve this answer
0

No grant needed. You do not see any results, because output is disabled by default. Enable output before running your code:

set serveroutput on
|improve this answer
  • but i tried to run other queries, a simply select from other table and output, it works, it is not working when i try to select from all_synonyms table – hades Apr 21 '16 at 15:05
  • @hades A simple select fetches and automatically displays rows on the client side. DBMS_OUTPUT writes data to a buffer that you have to request and display manually with DBMS_OUTPUT.GET_LINES or use set serveroutput on to make it happen automatically. You do not need any privileges for ALL_SYNONYMS, and even a user with the least possible privileges gets thousands of rows from that view. – Balazs Papp Apr 21 '16 at 15:12
  • erm, sorry, maybe i didn't make my statement clear, i tried to dbms_output other select queries, it works, but not for the select * from all_synonyms. Do you mean that ALL_SYNONYMS is working for all users without any prev/role grant even in functions, store procedure? – hades Apr 21 '16 at 15:19
  • @hades Yes. SYS.ALL_SYNONYMS is a view, with a public synonym called ALL_SYNONYMS pointing to it, and by default, SELECT privilege is granted to PUBLIC on the view. There are thousands of objects like that. – Balazs Papp Apr 21 '16 at 15:36
  • 1
    @hades And that is of course not true, and listed in the errata of the book: blog.mclaughlinsoftware.com/oracle-books-errata/… – Balazs Papp Apr 21 '16 at 18:33
0

Is the DBMS Output disabled by the DBA?

Try running this in SQL*Plus

SET SERVEROUTPUT ON SIZE 30000;

EXEC PROCEDURE_1('SYN_NAME'); --replace with name of synonym you want to find

You can also add

DBMS_OUTPUT.ENABLE (buffer_size => NULL);

to your procedure like this

CREATE PROCEDURE PROCEDURE_1 AS
  strName VARCHAR2(50);
  CURSOR C_NAME IS SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE TABLE_OWNER = 'USER1';

BEGIN
   DBMS_OUTPUT.ENABLE (buffer_size => NULL);  -- add this NOT RECOMMENDED
   FOR R_NAME in C_NAME
   LOOP
     strName := R_NAME.SYNONYM_NAME;
     dbms_output.put_line(strName);
   END LOOP;

END PROCEDURE_1;
/
|improve this answer
  • I think not, because im able to DBMS output result from other queries. – hades Apr 21 '16 at 15:21
  • SET SERVEROUTPUT ON is needed to use dbms_output. You can put the command in your $ORACLE_HOME/sqlplus/admin/glogin.sql file, then you will not need to specify the command. Since 10g you don't need to specify a buffer size. There was a time where the default buffer size was 40k and the max buffer size was 1M. Now there is no practical limit to the buffer size and unlimited is the default. – Gandolf989 Apr 21 '16 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.