Forum Stats

  • 3,826,885 Users
  • 2,260,721 Discussions
  • 7,897,114 Comments

Discussions

Import data wizard requires also select privileges on target table

jmarton
jmarton Member Posts: 92
edited Mar 30, 2016 7:30PM in SQL Developer

When trying to do csv import to a table, if the importing user has only insert privilege, but no select privilege, SQL Developer fails with "Validation Failed: Table Name is not a valid Oracle Table Name"

steps to reproduce:

  1. connect to a database as the built-in user scott and execute the following 2 SQL statements:
create table import_test (col1 number);


grant insert on import_test to hr;

  1. create a file /tmp/import_test.csv, with the following content (3 numbers, line-by-line)
1
2
3

  1. connect to the database as the built-in user hr, and initiate the import wizard by doing
    1. in the connections tab, expand the connectios/your connection/other users/scott/tables
    2. right click on table import_test, and select "import data" in the pop-up.
    3. select the /tmp/import_test.csv for import, uncheck header, and click next
    4. SQL Developer should display the step to choose import method, instead that, after a while, a blank step is displayed, and "Validation Failed: Table Name is not a valid Oracle Table Name" pops up (screenshot attached).

Workaround: If I also grant select to the importig user, everything works fine.

My environment:

  • sqldeveloper-4.1.3.20.78
  • java: java version "1.8.0_72"
  • client OS: linux (the same holds on Windows XP)
  • Oracle Database 11gR2 XE, on linux

Edit: sdcli gives other error message (myimport.xml is saved using SQL Developer import wizard button "Save state", that connect with the buil-in user hr), workaround is the same:

~/sqldeveloper/sqldeveloper/bin$ bash sdcli utility import -config /tmp/myimport.xml
General validation completed with errors.

Configuration file contains connection local_xe_hr.
Configuration file contains table SCOTT.IMPORT_TEST.
Unable to access table SCOTT.IMPORT_TEST using connection local_xe_hr.

«1

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,605 Employee
    edited Mar 29, 2016 8:50AM

    yeah, we need to look at a table to see what's what to do the import

    we assume if you can INSERT, you can also SELECT

  • jmarton
    jmarton Member Posts: 92
    edited Mar 29, 2016 11:53AM

    Do you need privilege to read my mailbox in order to send me az email? No, you only need to to the address, and the way to format your thoughts.

    Likewise, you need to to look at the table structure (columns, data types etc.) to do the import, but not its content.

    You know the table structure through e.g. all_tables, all_tab_columns, if you have INSERT privilege. What else do you need to know? Also SQL Developer can show the columns tab of the table panel, but not the data tab, when one have INSERT, but no SELECT privilege. This is the expected behaiour.

    If the database engine itself required SELECT to do INSERT, then INSERT would implicitly add SELECT object privilege. But it does not do so. Also the traditional SQL*Loader can do the insert into a table where only INSERT privilege is given, no SELECT privilege. I just wanted to do the same without a full OCI client install, with only SQL Developer, and maybe using sdcli...

    Unfortunately, "Statements log" does not reveal what query do SQL Developer run against the target table that leads to the error.

    Please review your above-mentioned assumption.

  • Unknown
    edited Mar 29, 2016 5:42PM
    Do you need privilege to read my mailbox in order to send me az email? No, you only need to to the address, and the way to format your thoughts.
    

    Totally irrelevant - mixing apples and oranges.

    Likewise, you need to to look at the table structure (columns, data types etc.) to do the import, but not its content.
    

    Correct - but Sql dev doesn't query the table so look at its content.

    You know the table structure through e.g. all_tables, all_tab_columns, if you have INSERT privilege. What else do you need to know?
    

    And that is where your misunderstanding is. The data dictionary does NOT show the Java metadata for the table. It shows the Oracle-based structural data.

    Java apps (e.g. Sql Developer) need to know what Java datatypes to map the Oracle datatypes to. When you use Java to query a table the JDBC driver extracts the Oracle metadata from the data dictionary and then constructs the Java metadata from it. It then actually queries the data itself (Sql Dev doesn't need that last step).

    The difference is most apparent for objects columns. The 'datatype' in Oracle is the name of an object type (a nested table or array or a plain object with attributes). The java 'datatype' is ARRAY or STRUCT.

    Also SQL Developer can show the columns tab of the table panel, but not the data tab, when one have INSERT, but no SELECT privilege. This is the expected behaiour.
    

    Yes - but, again, that does NOT contain the Java metadata that Java apps need.

     If the database engine itself required SELECT to do INSERT, then INSERT would implicitly add SELECT object privilege. But it does not do so.
    

    True but irrelevant - the engine is NOT written in Java and does NOT need Java metadata.

    Also the traditional SQL*Loader can do the insert into a table where only INSERT privilege is given, no SELECT privilege.
    

    True but irrelevant - and sql*Loader is NOT written in Java and does NOT need Java metadata.

    Unfortunately, "Statements log" does not reveal what query do SQL Developer run against the target table that leads to the error.
    

    It queries the table (but not any data) to force the JDBC driver to construct the needed Java metadata for the table.

    jmarton
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,605 Employee
    edited Mar 29, 2016 5:56PM

    if you dont want to grant select privs on the table directly to the people batch loading data to it (weird, but OK), then create a view on the table and load via that view

    we're not going to re-engineer the wizard for the use case of user can insert but not select

    jmarton
  • Unknown
    edited Mar 29, 2016 7:43PM

    Yep - that wouldn't be ANYWHERE on my priority list.

    I can't recall ever doing ETL work that didn't involve verifying that the data got loaded properly. That isn't possible if you can't select from the table.

  • jmarton
    jmarton Member Posts: 92
    edited Mar 30, 2016 12:03PM
    rp0428 wrote:
    
    You know the table structure through e.g. all_tables, all_tab_columns, if you have INSERT privilege. What else do you need to know?
    
    And that is where your misunderstanding is. The data dictionary does NOT show the Java metadata for the table. It shows the Oracle-based structural data.
    
    Java apps (e.g. Sql Developer) need to know what Java datatypes to map the Oracle datatypes to. When you use Java to query a table the JDBC driver extracts the Oracle metadata from the data dictionary and then constructs the Java metadata from it. It then actually queries the data itself (Sql Dev doesn't need that last step).
    
    The difference is most apparent for objects columns. The 'datatype' in Oracle is the name of an object type (a nested table or array or a plain object with attributes). The java 'datatype' is ARRAY or STRUCT.
    
    
    

    This is the key point: "the JDBC driver extracts the Oracle metadata from the data dictionary and then constructs the Java metadata from it". If JDBC driver can construct the Java metadata when querying, then it should be able to construct it also some other way. Actually, the driver indeed can construct, e.g. DatabaseMetaData.getColumns() call returns the Java types.

    Oracle JDBC Thin Driver 12.1.0.2 seems slightly inaccurate in this area:

    • DatabaseMetaData.getColumns()/TYPE_NAME gives unqualified type name, whereas it should return "Data source dependent type name, for a UDT the type name is fully qualified"[1], and as ResultSet.getMetaData().getColumnTypeName() does that correctly.

    I have created a JDBC Demo code (use it at own risk, NOT designed for production use!) to illustrate the above-mentioned differences, which is attached (as .txt, to be renamed to .java). Argument list is:

    1. DB username
    2. DB password
    3. target table schema (uppercase)
    4. target table name (uppercase)
    5. OCI/Thin for the driver to used, respectively

    Using the Oracle JDBC 12.1.0.2 Thin driver:

    $ java -version
    java version "1.8.0_74"
    Java(TM) SE Runtime Environment (build 1.8.0_74-b02)
    Java HotSpot(TM) 64-Bit Server VM (build 25.74-b02, mixed mode)
    $ sha1sum ojdbc7.jar
    7c9b5984b2c1e32e7c8cf3331df77f31e89e24c2  ojdbc7.jar
    $ javac -cp ojdbc7.jar GetJavaType.java
    $ java -cp ojdbc7.jar:. GetJavaType scott TIGER HR DEPT_PHONE_LIST Thin
    Connected.
    Table column types from DatabaseMetaData.getColumns() for HR.DEPT_PHONE_LIST
    1: DEPT_NO (1): DECIMAL / NUMBER
    2: PHONE_LIST (2): ARRAY / PHONE_VARRAY_TYP
    3: ADDED_PHONE_FIELD (3): STRUCT / PHONE_TYP
    Table column types from ResultSetMetaData.getColumn*() for HR.DEPT_PHONE_LIST
    1: DEPT_NO (1): NUMERIC / NUMBER
    2: PHONE_LIST (2): ARRAY / HR.PHONE_VARRAY_TYP
    3: ADDED_PHONE_FIELD (3): STRUCT / HR.PHONE_TYP
    Connection closed.
    
    
    

    Demo table setup is Example 5-4 Creating and Populating a VARRAY Data Type from [3], with a plain object column (added_phone_field), commit and grant statements added. Note that SELECT privilege is added only for being able to compare the two mentioned methods. INSERT privilege by itself is enough for the DatabaseMetaData.getColumns() way.

    conn hr/hr
    CREATE TYPE phone_typ AS OBJECT (
        country_code   VARCHAR2(2),
        area_code      VARCHAR2(3),
        ph_number      VARCHAR2(7));
    /
    CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
    /
    CREATE TABLE dept_phone_list (
      dept_no NUMBER(5),
      phone_list phone_varray_typ,
      added_phone_field phone_typ
      );
    
    INSERT INTO dept_phone_list VALUES (
       100,
       phone_varray_typ( phone_typ ('01', '650', '5550123'),
                          phone_typ ('01', '650', '5550148'),
                          phone_typ ('01', '650', '5550192')));
    commit;
    grant insert,select on dept_phone_list to scott;
    grant execute on phone_typ to scott;
    grant execute on phone_varray_typ to scott;
    
    
    

    [1]: DatabaseMetaData (Java Platform SE 8 )

    [2]: https://docs.oracle.com/database/121/JJDBC/datacc.htm#g1028145

    [3]: https://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjcol.htm#i468598

    Thank you, @rp0428, for pointing me toward this type resolution problem. Without knowing if the described technique (i.e. getting the column types by running a query against the table) is used in SQL Developer's data import implementation or not, it seems for me that there exists an other solution that requires no SELECT privilege. So it is clear that querying is no must in order to get the columns' Java types according to the JDBC specification, and also in practice. And I wouldn't be surprised if there would be some technique to get the correct Java type in more detail from the Oracle Thin JDBC driver 12.1.0.2 DatabaseMetaData implementation.

    Edit (2016-03-30 16:02 UTC): add the mentioned Java JDBC Demo code: GetJavaType.txt

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,605 Employee
    edited Mar 30, 2016 11:45AM

    it doesn't matter in the end of the day

    it would be easier for you to get SELECT privs

  • jmarton
    jmarton Member Posts: 92
    edited Mar 30, 2016 11:47AM

    Thank you, @Jeff Smith Sqldev Pm-Oracle . I wouldn't say it is weird to give only the minimal set of permissions that is required for the operation, i.e. INSERT only to do data loading (think of a scheduled import from sdcli, when it will go beyond early adopters' release), but I also understand your opinion. Is such, please document it correctly, or point me to the right documentation that I possibly missed. So for now, SQL Developer Data import requires SELECT and INSERT object privileges on the target table (if the table already exists).

  • jmarton
    jmarton Member Posts: 92
    edited Mar 30, 2016 11:53AM

    Thank you, @Jeff Smith Sqldev Pm-Oracle . That's exactly what I have done to solve quickly the problem. I just wanted to point to this issue in the hope that it will be useful, and bring attention to that there exists a working method without SELECT privilege from a Java app using Oracle JDBC Thin driver.

  • Unknown
    edited Mar 30, 2016 1:07PM

    Just to be clear - no one is trying to 'argue' with you. We are trying to explain how, and why, things work the way they do.

    This is the key point: "the JDBC driver extracts the Oracle metadata from the data dictionary and then constructs the Java metadata from it". If JDBC driver can construct the Java metadata when querying, then it should be able to construct it also some other way.
    

    I agree - and you CAN construct it 'some other way'.

    Actually, the driver indeed can construct, e.g. DatabaseMetaData.getColumns() call returns the Java types.
    

    And doing it that way:

    1. is MUCH MORE complex

    2. requires a lot more detailed understanding of how the pieces fit together

    3. uses 'integer based' (number based) 'codes' that you then have to manually lookup/convert the the string equivalent.

    The example you posted is just ONE of many.

     Oracle JDBC Thin Driver 12.1.0.2 seems slightly inaccurate in this area:
    
    DatabaseMetaData.getColumns()/TYPE_NAME
    gives unqualified type name, whereas it should return "Data source
    dependent type name, for a UDT the type name is fully qualified"[1], and
    as ResultSet.getMetaData().getColumnTypeName() does that correctly.

    Well - I submit that your 'slightly inaccurate' is just an observation/opinion on your part. That 'getColumnTypeName' is actually needed and is just ONE of the differences I alluded to.

    But saying 'it should return'? If you review the API (and try an example) you will see that the relative schema is provided as a separate attribute. Which means the user wanting the fully qualified name needs to put the pieces together properly.

    That global metadata view does that. It gives you a much finer, more complex, granularity of the metadata. That level is rarely needed in my experience

    So I don't consider the metadata version to be 'inaccurate' - it is just accurate at a different level of granularity. After all if you NEED the component parts of a fully qualified name Oracle gives you that way to get them without having to (poorly) write a parser to split things apart.

    Look at those attributes and you will also see that many of them use numeric code values (for example type-code) rather than a string description. You then need to do a lookup to get the string description.

    There are a LOT of 'gotchas' if you try to put those pieces together yourself. It can be done but can also be VERY complex and especially error-prone. Oracle makes extensive use of hidden columns (functional indexes, extended statistics, virtual column support) that are normally NOT presented/reflected in the user view of things.

    But if you work with the 'under the covers' view of things they are there and you need to understand them and know which ones are relevant and which you can safely ignore.

    Taking views, synonyms, privileges and the like into account can make things even harder to dissect.

     I have created a JDBC Demo code (use it at own risk, NOT designed for production use!) to illustrate the above-mentioned differences, which is attached (as .txt, to be renamed to .java).
    

    I must have missed it because I don't see any attachment.

    But I don't see any need to continue.

    Sql Developer is based on the JDeveloper framework. It enhances it in areas specific to Sql Dev requirements/needs but otherwise the code-base is controlled by the JDev team.

    Again - not arguing - but in 25+ years I, personally, have NEVER had a use case for ETL work where the load processes needed to be restricted from having select access. Generally the accounts that do external loading are loading into staging tables or the loading is being done by a DB controlled process.

    So even if such a requirement did exist there are alternative ways (views, DEFINER RIGHTS procedures, others) that the loading process can have the needed privileges.

    I can think of a LOT of other functionality that would be much higher on my personal 'wish list' for the Sql Dev team to work on that this issue.!

This discussion has been closed.