5 Replies Latest reply: Feb 25, 2013 11:36 PM by 951027 RSS

    query help

    951027
      Hello all im trying to grant select privilege of sys tables to admin user

      my os windows 7 pro
      db ver 11.2.0.1.0
      im using command prompt : sqlplus / as sysdba
      while running this.

      i saved file a.sql

      BEGIN
      FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='SYS') LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to admin';
      END LOOP;
      END;
      /


      while exectuting this

      @a.sql

      im getting

      SQL> @a.sql;
      BEGIN
      *
      ERROR at line 1:
      ORA-00911: invalid character
      ORA-06512: at line 3
        • 1. Re: query help
          Purvesh K
          Does it work when you execute it just as a Script from SQL Plus/SQL Developer?
          • 2. Re: query help
            sb92075
            948024 wrote:
            Hello all im trying to grant select privilege of sys tables to admin user

            my os windows 7 pro
            db ver 11.2.0.1.0
            im using command prompt : sqlplus / as sysdba
            while running this.

            i saved file a.sql

            BEGIN
            FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='SYS') LOOP
            EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to admin';
            END LOOP;
            END;
            /


            while exectuting this

            @a.sql

            im getting

            SQL> @a.sql;
            BEGIN
            *
            ERROR at line 1:
            ORA-00911: invalid character
            ORA-06512: at line 3
            The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
            Then print the variable before passing it to EXECUTE IMMEDIATE.
            COPY the statement & PASTE into sqlplus to validate its correctness.
            • 3. Re: query help
              951027
              nopes.......:( its still giving error
              • 4. Re: query help
                Purvesh K
                948024 wrote:
                nopes.......:( its still giving error
                set serveroutput on;
                BEGIN
                for r in (select owner, table_name from all_tables where owner='SYS') loop
                  dbms_output.put_line('grant select on '||R.owner||'.'||R.table_name||' to admin');
                END LOOP;
                END;
                /
                
                
                anonymous block completed
                grant select on SYS.DUAL to admin
                grant select on SYS.SYSTEM_PRIVILEGE_MAP to admin
                grant select on SYS.TABLE_PRIVILEGE_MAP to admin
                Try similar block at your end and Copy and Paste the Output here. And also try the Grant statements one by one at your terminal to ascertain their working before trying them with Execute Immediate.
                Since, I do not have Grant privileges at my database, I cannot try the output generated.

                My Oracle version is 10.0.2.4. Though, I very much doubt if its related to version.
                • 5. Re: query help
                  951027
                  thanks a ton.........:)

                  it worked.

                  grant select on SYS.AQ$_SYS$SERVICE_METRICS_TAB_H to admin
                  grant select on SYS.AQ$_SYS$SERVICE_METRICS_TAB_G to admin
                  grant select on SYS.AQ$_SYS$SERVICE_METRICS_TAB_I to admin
                  grant select on SYS.OLAPTABLEVELS to admin
                  grant select on SYS.OLAPTABLEVELTUPLES to admin
                  grant select on SYS.SAM_SPARSITY_ADVICE to admin
                  grant select on SYS.WRI$_ADV_OBJSPACE_TREND_DATA to admin
                  grant select on SYS.WRI$_ADV_OBJSPACE_CHROW_DATA to admin
                  grant select on SYS.UTL_RECOMP_COMPILED to admin
                  grant select on SYS.UTL_RECOMP_SORTED to admin

                  PL/SQL procedure successfully completed.