Cannot execute a query with pyhdb

Question

I'm trying to execute a simple query by using pyhdb.

Here is my code:

HDB_TRACE=1

import pyhdb as db

db.tracing = True

connection = db.connect('HOST', 31047, 'USER', 'PASSWORD')

cursor = connection.cursor()

cursor.execute("SELECT TOP 1 GLOBAL_BRAND_OWNER FROM _SYS_BIC.LAV_DWH_SELLOUT.CV_NIELSEN")

print(cursor.fetchone())

The error I get is

pyhdb.exceptions.DatabaseError: insufficient privilege: Not authorized

I asked to the DB Admin and he told me that the user I'm logging in with has every possible privilege.

If I try SELECT 'Hello Python World' FROM DUMMY it works. I don't know if there is an error in my code or not.

If I execute the query through HANA Studio with the same user, it works.

This is the trace of the process:

C:\Users\grellaa\AppData\Local\Programs\Python\Python35\python.exe D:/ProgettoTableau/Importazione_Variabile_Obiettivo.py
RequestMessage = {
    header = [
        session_id = -1,
        packet_count = 0,
        payload_length = 128,
        varpartsize = 131040,
        num_segments = 1,
        packet_options = 0
    ],
    segments = [
        RequestSegment = {
            header = [
                segment_length = 128,
                segment_offset = 0,
                num_parts = 1,
                segment_number = 1,
                segment_kind = 1,
                message_type = 65,
                commit = 0,
                command_options = 0
            ],
            parts = [
                Authentication = {
                    header = [
                        part_kind = 33,
                        part_attributes = 0,
                        argument_count = 1,
                        bigargument_count = 0,
                        payload_size = 88,
                        remaining_buffer_size = 131016
                    ],
                    trace_header = b'21 00 01 00 00 00 00 00 58 00 00 00 c8 ff 01 00',
                    trace_payload = b'03 00 08 31 30 30 30 43 30 32 39 0b 53 43 52 41 4d 53 48 41 32 35 36 40 77 a3 f6 c2 cb a6 ...',
                    user = '1000C029',
                    methods = {b'SCRAMSHA256': b'w\xa3\xf6\xc2\xcb\xa6v\xb0\x9a\xf0\x7f\xf4g\x07\x0ep]B{\xef\x99L-vc\x94\xeb2=\xe4\x13\xa7\xdb:\x87\xb10\x0c\xb2*\x0esYY\xed7\x19\x81OU\xd2\x95R\x95\x16W\xbb\xdfS\xe0\x04\xac\xb1\xe6'}
                }
            ]
        }
    ]
}
ReplyMessage = {
    header = [
        session_id = 0,
        packet_count = 0,
        payload_length = 128,
        varpartsize = 29968,
        num_segments = 1,
        packet_options = 0
    ],
    segments = [
        ReplySegment = {
            header = [
                segment_length = 128,
                segment_offset = 0,
                num_parts = 1,
                segment_number = 1,
                segment_kind = 2,
                function_code = 0
            ],
            parts = [
                Authentication = {
                    header = [
                        part_kind = 33,
                        part_attributes = 0,
                        argument_count = 1,
                        bigargument_count = 0,
                        payload_size = 83,
                        remaining_buffer_size = 29928
                    ],
                    trace_header = b'21 00 01 00 00 00 00 00 53 00 00 00 e8 74 00 00',
                    trace_payload = b'02 00 0b 53 43 52 41 4d 53 48 41 32 35 36 44 02 00 10 c4 0a c3 2b 27 be cc b3 c4 65 ab 1b ...',
                    user = None,
                    methods = {b'SCRAMSHA256': b"\x02\x00\x10\xc4\n\xc3+'\xbe\xcc\xb3\xc4e\xab\x1bb\x16_80!$Y\xe2*5\xfe\x14#2+\x10\x06@\x05\x0b2l\xc3\xe2\x92\x94\x80\xbc\x80\x96[y\x04&\xe8\x1fR?z\xbe\x1b\xd6\xcbp\x0b\xdf&\x9a+s\x8c\x85"}
                }
            ]
        }
    ]
}
RequestMessage = {
    header = [
        session_id = 0,
        packet_count = 0,
        payload_length = 208,
        varpartsize = 131040,
        num_segments = 1,
        packet_options = 0
    ],
    segments = [
        RequestSegment = {
            header = [
                segment_length = 208,
                segment_offset = 0,
                num_parts = 3,
                segment_number = 1,
                segment_kind = 1,
                message_type = 66,
                commit = 0,
                command_options = 0
            ],
            parts = [
                Authentication = {
                    header = [
                        part_kind = 33,
                        part_attributes = 0,
                        argument_count = 1,
                        bigargument_count = 0,
                        payload_size = 59,
                        remaining_buffer_size = 131016
                    ],
                    trace_header = b'21 00 01 00 00 00 00 00 3b 00 00 00 c8 ff 01 00',
                    trace_payload = b'03 00 08 31 30 30 30 43 30 32 39 0b 53 43 52 41 4d 53 48 41 32 35 36 23 00 01 20 7d 35 0f ...',
                    user = '1000C029',
                    methods = {'SCRAMSHA256': b'\x00\x01 }5\x0f\x02\x15\xf4|`\x985\xc9s\xc9\xd5\xd5J\xbd!\xdb\xc9\x9bL\x91\x8f\x99\xfd\xed:e\x89 \xee'}
                },
                ClientId = {
                    header = [
                        part_kind = 35,
                        part_attributes = 0,
                        argument_count = 1,
                        bigargument_count = 0,
                        payload_size = 23,
                        remaining_buffer_size = 130936
                    ],
                    trace_header = b'23 00 01 00 00 00 00 00 17 00 00 00 78 ff 01 00',
                    trace_payload = b'70 79 68 64 62 2d 36 30 37 32 40 73 64 67 6e 62 32 38 35 2e 53 44 47 00',
                    client_id = 'pyhdb-6072@sdgnb285.SDG'
                },
                ConnectOptions = {
                    header = [
                        part_kind = 42,
                        part_attributes = 0,
                        argument_count = 8,
                        bigargument_count = 0,
                        payload_size = 42,
                        remaining_buffer_size = 130896
                    ],
                    trace_header = b'2a 00 08 00 00 00 00 00 2a 00 00 00 50 ff 01 00',
                    trace_payload = b'03 1d 05 00 65 6e 5f 55 53 02 1c 01 17 03 01 00 00 00 0c 03 01 00 00 00 0f 03 00 00 00 00 ...'
                }
            ]
        }
    ]
}
ReplyMessage = {
    header = [
        session_id = 1718090968109219,
        packet_count = 0,
        payload_length = 376,
        varpartsize = 29968,
        num_segments = 1,
        packet_options = 0
    ],
    segments = [
        ReplySegment = {
            header = [
                segment_length = 376,
                segment_offset = 0,
                num_parts = 3,
                segment_number = 1,
                segment_kind = 2,
                function_code = 0
            ],
            parts = [
                Authentication = {
                    header = [
                        part_kind = 33,
                        part_attributes = 0,
                        argument_count = 1,
                        bigargument_count = 0,
                        payload_size = 15,
                        remaining_buffer_size = 29928
                    ],
                    trace_header = b'21 00 01 00 00 00 00 00 0f 00 00 00 e8 74 00',
                    trace_payload = b'02 00 0b 53 43 52 41 4d 53 48 41 32 35 36 00 00',
                    user = None,
                    methods = {b'SCRAMSHA256': b''}
                },
                ConnectOptions = {
                    header = [
                        part_kind = 42,
                        part_attributes = 0,
                        argument_count = 21,
                        bigargument_count = 0,
                        payload_size = 118,
                        remaining_buffer_size = 29896
                    ],
                    trace_header = b'2a 00 15 00 00 00 00 00 76 00 00 00 c8 74 00 00',
                    trace_payload = b'01 03 98 1a 06 00 0b 1d 03 00 48 4c 51 0c 03 01 00 00 00 17 03 01 00 00 00 10 03 06 00 00 ...'
                },
                TopologyInformation = {
                    header = [
                        part_kind = 15,
                        part_attributes = 0,
                        argument_count = 3,
                        bigargument_count = 0,
                        payload_size = 165,
                        remaining_buffer_size = 29760
                    ],
                    trace_header = b'0f 00 03 00 00 00 00 00 a5 00 00 00 40 74 00 00',
                    trace_payload = b'08 00 05 03 04 00 00 00 01 1d 0e 00 31 36 31 2e 32 37 2e 31 36 36 2e 31 30 32 02 03 47 79 ...'
                }
            ]
        }
    ]
}
RequestMessage = {
    header = [
        session_id = 1718090968109219,
        packet_count = 0,
        payload_length = 112,
        varpartsize = 131040,
        num_segments = 1,
        packet_options = 0
    ],
    segments = [
        RequestSegment = {
            header = [
                segment_length = 112,
                segment_offset = 0,
                num_parts = 1,
                segment_number = 1,
                segment_kind = 1,
                message_type = 2,
                commit = 0,
                command_options = 0
            ],
            parts = [
                Command = {
                    header = [
                        part_kind = 3,
                        part_attributes = 0,
                        argument_count = 1,
                        bigargument_count = 0,
                        payload_size = 72,
                        remaining_buffer_size = 131016
                    ],
                    trace_header = b'03 00 01 00 00 00 00 00 48 00 00 00 c8 ff 01 00',
                    trace_payload = b'53 45 4c 45 43 54 20 54 4f 50 20 31 20 47 4c 4f 42 41 4c 5f 42 52 41 4e 44 5f 4f 57 4e 45 ...',
                    sql_statement = 'SELECT TOP 1 GLOBAL_BRAND_OWNER FROM _SYS_BIC.LAV_DWH_SELLOUT.CV_NIELSEN'
                }
            ]
        }
    ]
}
Traceback (most recent call last):
  File "D:/ProgettoTableau/Importazione_Variabile_Obiettivo.py", line 9, in <module>
    cursor.execute("SELECT TOP 1 GLOBAL_BRAND_OWNER FROM _SYS_BIC.LAV_DWH_SELLOUT.CV_NIELSEN")
  File "C:\Users\grellaa\AppData\Roaming\Python\Python35\site-packages\pyhdb\cursor.py", line 258, in execute
    self._execute_direct(statement)
  File "C:\Users\grellaa\AppData\Roaming\Python\Python35\site-packages\pyhdb\cursor.py", line 217, in _execute_direct
    reply = self.connection.send_request(request)
  File "C:\Users\grellaa\AppData\Roaming\Python\Python35\site-packages\pyhdb\connection.py", line 84, in send_request
    return self.__send_message_recv_reply(payload.getvalue())
  File "C:\Users\grellaa\AppData\Roaming\Python\Python35\site-packages\pyhdb\connection.py", line 124, in __send_message_recv_reply
    return ReplyMessage.unpack_reply(header, payload)
  File "C:\Users\grellaa\AppData\Roaming\Python\Python35\site-packages\pyhdb\protocol\message.py", line 92, in unpack_reply
    segments=tuple(ReplySegment.unpack_from(payload, expected_segments=header.num_segments)),
  File "C:\Users\grellaa\AppData\Roaming\Python\Python35\site-packages\pyhdb\protocol\segments.py", line 152, in unpack_from
    raise error.parts[0].errors[0]
pyhdb.exceptions.DatabaseError: insufficient privilege: Not authorized

Answer 1

You will have to set the parameters for the cursor object that is logging into the HANA database and ensure that user has permission to the schema holding the data you are querying.

Answer 2

I'd say it is pretty clear that your user does in fact not have "every possible privilege". Seeing that you try to access a calculation view, my first guess would be that your user has not authorisation on that calculation view.

For a more thorough analysis of the problem, you may logon via SAP HANA Studio, right-click on the view and select "Show Authorisation". This will give you a graphical overview of required and provided privileges.

This blog post gives a pretty good coverage on how to use the tool.