3 Replies Latest reply on Aug 14, 2019 10:43 AM by Jonathan Lewis

    Can't grant privs to object with lowercase named

    Marlon.ORA

      Hello team

      Have an issue. I need to grant privilege to a schema's objects but some objects are named in lower case, and at the time I try to grant even with single or double quotes it gives me an error:

       

      SYS@DB1>grant select on SCHEMA.Table_name to SCHEMA2;

      grant select on APPS.wtpParameters983_TAB to SCHEMA2

                           *

      ERROR at line 1:

      ORA-00942: table or view does not exist

       

      SYS@DB1>grant select on "SCHEMA.Table_name" to SCHEMA2;

      grant select on "SCHEMA.Table_name" to SCHEMA2

                      *

      ERROR at line 1:

      ORA-00942: table or view does not exist

       

      SYS@DB1>grant select on 'SCHEMA.Table_name' to SCHEMA2;

      grant select on 'SCHEMA.Table_name' to SCHEMA2

                      *

      ERROR at line 1:

      ORA-00903: invalid table name

       

      I validated and tables with the lower case do exist

       

      Wonder what can I do to grant the select on table to the schema2

       

      Thank you so much!!!

        • 1. Re: Can't grant privs to object with lowercase named
          L. Fernigrini

          Try this:

           

          GRANT SELECT ON SCHEMA."Table_name" to SCHEMA2;

           

          You just need to enclose between double quotes the names that are case sensitve.

           

          And, if you can, get rid of those tables!!!! Use the default case insensitive approach, it will be a nightmare to use tables names that are case sensitive.

          • 2. Re: Can't grant privs to object with lowercase named
            Gaz in Oz

            Objects that have been named with special characters and and/or include mixed case will always need to be referenced in double-quotes in DML and DDL.

            Consider not using mixed case and special characters in object names, it just makes things harder for anyone trying to use those objects.

            Check in all/dba_objects to determine the actual name of the object in question and, as L. Fernigrini shows:

            SQL> grant select on schema."Table_name" to schema2;

            If you also had a schema in mixed case then the grant would be written with each object in their own begin/end double quotes.

            For example say some not so savvy developer decided to create the schemas as "Schema", "Schema2" then the grant would be:

            SQL> grant select on "Schema"."Table_name" to "Schema2";

            Awful.

            • 3. Re: Can't grant privs to object with lowercase named
              Jonathan Lewis

              Just to add a little detail to the comment from Gaz in Oz

               

              Objects that have been named with special characters and and/or include mixed case will always need to be referenced in double-quotes in DML and DDL.

               

              In particular, your statement:

               

              grant select on "SCHEMA.Table_name" to SCHEMA2

               

              leaves Oracle looking for an object called "SCHEMA.Table_name" in the current schema.

               

              e.g.

               

              SQL> select table_name from user_tables;

               

              TABLE_NAME

              --------------------

              CHILD

              CHILD2

              ERR$_PARENT

              GENERATOR

              PARENT

              T1

              UPPER.lower

               

              7 rows selected.

               

               

              Regards

              Jonathan Lewis