Error in while accessing MAXDB

[Former Member]

HI all,

I'm using EP 7.0 and SP 15.

I am trying to customize portal activity report.For that I decided to access portal database MAXDB (for access portal activity report tables).So, I created BI JDBC System for portal database, system connection test is successfull.

In Visual Composer I used that same system in the SQL Editor(BI) and display all the tables in the MAXDB. If i enter any sql query like (select * from wcr_userpageusage) and while validate that query its showing Error: [-4004] (at 15) display the unknown table name.while RUNning that query Error1 unable to execute query or XML returned result contain query execute failed

But i use that same System in Bi Integartion wizard display all the tables and retrieve the information correctly.

But this application working fine in EP 7.0 and SP 13 but i imported same VC application in SP 15 i'm facing the above problem.

what is the problem.. anyone tell me to achieve this.

Thanks and regards

Thillai J

[lbreddemann]

This is very likely not a MaxDB problem, but a question of setting up the connection in visual composer.

Do you get the same error, when running the statement in SQL Studio / DB Studio / sqlcli ?

regards,

Lars

[Former Member]

yes.I tried to execute the same query in MAXDB manager using Command Line and getting same error.

I used with SUPERDBA user to checked in MAXDB manager.

Thanks for your reply

Regards

Thillai

[former_member229109]

Hello Thillai,

1) Are you SAP customer?

2) What user is the owner of the application tables in database?

Please update the thread with the output of the two SQL statements,

connect to the database as SUPERDBA user::

select owner from tables where tablename = 'wcr_userpageusage'

select * from users

3)"But this application working fine in EP 7.0 and SP 13 but i imported

same VC application in SP 15 i'm facing the above problem."

What note og guide did you follow "to import VC application in SP 15"?

Thank you and best regards, Natalia Khlopina

[Former Member]

Hi Natalia Khlopina,

Thanks for your reply...

In MAXDB manager I entered as the SUPERDBA user and execute the two query which you given

1) sql_execute select * from users

'CONTROL';'';'CONTROL';'ADMIN';'MULTIPLE';(null);(null);(null);'DEFAULT';'20080115';'00153020';'20080115';'00153020';'20080115';'00153020';'SERVERDB';'SERVERNODE';USERID;'NO';(null)

'SUPERDBA';'';'SUPERDBA';'SYSDBA';'MULTIPLE';(null);(null);(null);'DEFAULT';'20080115';'00153020';'20080115';'00153025';'20080115';'00153020';'SERVERDB';'SERVERNODE';USERID;'NO';(null)

'SUPERDBA';'';'SAPW11DB';'DBA';'MULTIPLE';(null);(null);(null);'UNICODE';'20080115';'00153101';'20080115';'00153101';'20080115';'00153101';'SERVERDB';'SERVERNODE';28;'USERID';(null)

'SUPERDBA';'';'ARTERIA';'STANDARD';'MULTIPLE';(null);(null);(null);'DEFAULT';'20081112';'00190905';'20081112';'00190905';'20081112';'00190905';'SERVERDB';'SERVERNODE';USERID;'NO';(null)

2) sql_execute select owner from tables where tablename = 'wcr_userpageusage'

OUTPUT: ERR-24988,ERR_SQL: SQL error

100,Row not found

3) sql_execute select tablename,owner from tables

IN this query Output display all the tables and owner

but these are tables are not showing in that list

WCR_USERPAGEUSAGE , WCR_WEBCONTENTSTAT , WCR_USERSTAT , WCR_USERFIRSTLOGON

Thanks& Regards

Thillai J

[Former Member]

Hi Natalia Khlopina,

Thanks for your reply... sorry, I found all these tables in the 'tables' table.

(WCR_USERPAGEUSAGE, WCR_WEBCONTENTSTAT, WCR_USERSTAT,WCR_USERFIRSTLOGON)

By using this query

sql_execute select tablename,owner from tables from WCR_USERFIRSTLOGON

I got the Ownername(SAP<ID>DB) of the table WCR_USERFIRSTLOGON. BY mapping the owner in portal, it is retrieving the information from the table.

But My requirment is, I should not use owner. So that I created a new user and

granted 'select' permission for all this tables in the MAXDB Manager by using SUPERDBA user.

But it is not working, it shows unknown table name error. Please help me to achieve this.

Thanks & Regards

Thillai J

[lbreddemann]

> Thanks for your reply... sorry, I found all these tables in the 'tables' table.

> (WCR_USERPAGEUSAGE, WCR_WEBCONTENTSTAT, WCR_USERSTAT,WCR_USERFIRSTLOGON)

> By using this query

>

> sql_execute select tablename,owner from tables from WCR_USERFIRSTLOGON

No you did not.

The query you just posted is invalid - it has two "FROM" clauses in the same query block.

This statement, as it is, will produce an error!

Anyhow, you figured out the owner of the tables.

> But My requirment is, I should not use owner. So that I created a new user and

> granted 'select' permission for all this tables in the MAXDB Manager by using SUPERDBA user.

This 'requirement' is nonsense.

Data in MaxDB (as well in other DBMS) belongs to a User/Schema and it is found by its name and its owner.

If you're not logged on as this user (or set the default schema to the schema you want), you've to provide the owner when you access the data.

Think of it: Assume you've two users/schemas in the database and both of them got a table e.g. named "HOTEL". Which one should be taken, when you logon as a third user and run a "SELECT * FROM HOTEL" ?

You need to specify, which schema should be used.

Next point:

The simple fact that you allowed another user to read the data does not mean that the table now belongs to that other user. Therefore you've to either logon as the owner or provide a synonym so that the name lookup finds a reference to the table you want to address.

Another thing: SUPERDBA is a reserved, special user.

It's for the MaxDB catalog. It is managing the database internal structures and its defitively not meant for your application data.

That's what the "SAP<SID>" users are for.

Do yourself a favour and simply use these users to access data in the database.

regards,

Lars

[Former Member]

Hi.

Thanks for your detailed answer. As per your suggestion, I want to map SAP<SID>DB user for all portal users through user mapping. Then only, my application will work.

But here I don't want to map a user with full control. Because, Here I am accessing standard tables of portal activity report. These tables should not be changed or updated by users or any other portal application(It is updated by Data Collection Service).So that I need a new db user with read permission(This user will be used in applications to access DB).

Thanks,

Thillai J

[former_member229109]

Hello Thillai J,

1) According your information, the owner of the application tables is SAP<SID>DB. As you know, the SAP Standard User for MAXDB Databases in Java EE systems is SAP<SID>DB. The user is the database administrator for the SAP system with the ID <SID> (database user of class DBA).

Please see the information & documents in the MAXDB Library at the link :

http://maxdb.sap.com/doc/7_7/default.htm

A) SAP MaxDB Security Guide

< Authorizations -> Defining Clear Authorizations for Users>

B) Tutorials -> SQL Tutorial -> Authorization

< -> Database Users and Their Privileges >.

C) Tutorials -> Reference -> SQL Reference Manual -> Authorization

< see the list of SQL statements for authorization & more details/examples >

2) Users may only implement SQL statements on database objects for which they have been granted privileges. When a user creates a database object, this user then automatically becomes the owner of the database object, and is granted all privileges for this database object. The owner of an object can grant privileges for this database object to other users; the database systems does

not grant any privileges implicitly. Other users can only grant privileges for a database object when they themselves have been granted these privileges and have permission to grant these to other users. If the database user SAPW11DB is the owner of the tables WCR*, then the user SAPW11DB could grant any privileges to the table WCR_USERFIRSTLOGON, for example,

to another database User using GRANT Statement ( see at 1) -> B) & C)).

3) Small example:

• Create the database user TEST. Connect to the database as user SAPW11DB, for example,

using SQLStudio, and run the statement:

CREATE USER TEST PASSWORD test STANDARD NOT EXCLUSIVE DEFAULTCODE UNICODE

• Grant Privileges to the user TEST. Connect to the database as user SAPW11DB, for example, using SQLStudio, and run the statement:

GRANT SELECT ON "SAPW11DB"."WCR_USERFIRSTLOGON" TO TEST

• Check tha the privileges granted. Connect to the database as user SAPW11DB, for example,

using SQLStudio, and run the statement:

SELECT * FROM DOMAIN.TABLEPRIVILEGES WHERE schemaname = 'SAPW11DB' AND tablename = 'WCR_USERFIRSTLOGON'

• More examples given in the document "Database Users and Their Privileges" < See reference in 1) -> B) >

4) Are you SAP customer? If you SAP customer, you could create the OSS message and discuss in more details our problem with MAXDB support.

5) You wrote above:

"2) sql_execute select owner from tables where tablename = 'wcr_userpageusage'

OUTPUT: ERR-24988,ERR_SQL: SQL error

100,Row not found"

Run:

sql_execute select owner from tables where tablename = 'WCR_USERPAGEUSAGE'

Thank you and best regards, Natalia Khlopina