Synonym USER_OBJECTS in MAXDB 7.7.06.09

[Former Member]

I've installed MAXDB on 7.7.06.09 on a Windows2003-server.

Then i create a dba-user.

In MaxDB 7.6 i use the synonym USER_OBJECTS in my sql-scripts for generating my database.

In MAXDB 7.7.06.09 is that synonym not available.

Does somebody know why?

Does the database-user doesn't have enough authorisation ?

Did i install MAXDB on 7.7.06.09 on a wrong way ?

Is the synonym USER_OBJECTS not available anymore in MAXDB 7.7 ?

[lbreddemann]

> I've installed MAXDB on 7.7.06.09 on a Windows2003-server.

> Then i create a dba-user.

> In MaxDB 7.6 i use the synonym USER_OBJECTS in my sql-scripts for generating my database.

> In MAXDB 7.7.06.09 is that synonym not available.

>

> Does somebody know why?

The former USER_OBJECTS view was used to provide a kind of compatibility for users changing their ORACLE database applications to MaxDB.

In fact, this table never had been a primary MaxDB table.

[Definitions of the System Tables - SAP Documentation|http://maxdb.sap.com/doc/7_7/45/2c5756ed062b30e10000000a1553f7/content.htm]

> Does the database-user doesn't have enough authorisation ?

No, you're database is fine.

> Did i install MAXDB on 7.7.06.09 on a wrong way ?

Doesn't look like that - it's correct that this table isn't there anymore.

> Is the synonym USER_OBJECTS not available anymore in MAXDB 7.7 ?

No, it has been removed.

In fact, if you check the 7.6 documentation, you'll find that this table had always been empty as the required data wasn't availble or applicable to MaxDB: [http://maxdb.sap.com/doc/7_6/d5/686c3c8cac924ae10000000a11405a/content.htm]

So, what do you use the table for?

regards,

Lars

[Former Member]

We use an application with JDBC to SAPMAXDB with a JDBC-driver parameters

com.sap.dbtech.jdbc.DriverSapDB:jdbc:sapdb://SERVERNAME/DBNAME?sqlmode=ORACLE&unicode=true"

on a MAXDB 7.6 database.

And with this configuration the synonym USER_OBJECTS is filled with data.

We use USER_OBJECTS to determine what objects are in the schema. With that information we create "DROP"-scripts to delete all the objects before we create a new initial table-set.

We use this functionality in a developer-environment on a central MAXDB-server.

The reason why we don't recreate the user\schema is that we don't want to give all developers dba-rights on the central MAXDB-server.

Is there a alternative view where we can determine what objects are owned by a db-user ?

[lbreddemann]

Hmm... even if there is data in USER_OBJECTS, you cannot rely on it, since the documentation clearly states that this is not the table to look into.

> We use USER_OBJECTS to determine what objects are in the schema. With that information we create "DROP"-scripts to delete all the objects before we create a new initial table-set.

> We use this functionality in a developer-environment on a central MAXDB-server.

> The reason why we don't recreate the user\schema is that we don't want to give all developers dba-rights on the central MAXDB-server.

> Is there a alternative view where we can determine what objects are owned by a db-user ?

Sure, simply use the MaxDB system views (I already send you the documentation links...).

So a simple,

select * from tables where schemaname=user and tabletype !='RESULT'

and you'll get all tables and views for the currently logged on user.

Concerning your general approach here:

Don't mistake the DBA-userclass with the actual DBA who uses the DBMGUI.

For SQL Users the following permissions are connected to the classes:

STANDARD: Access to data and database procedures that were defined by other database users and for which the user has been given privileges Definition of view tables, synonyms and temporary tables

RESOURCE: Definition of data and database procedures

Granting of privileges for these database objects to other database users

DBA (Database Administrator):

Creation of RESOURCE and STANDARD database users

Definition of database objects

Granting of all or part of privileges for these database objects to other database users

Since DBA users can only be created by the SYSDBA, you could do it like this:

Create a DBA user by your SYSDBA, let's say you name it 'DEVMASTER'

Logon as DEVMASTER and create a template user 'DEVTEMPLATE'

Now do your changes to the DEVTEMPLATE user, like assigning roles, etc.

Afterwards you can create a copy of the DEVMASTER logged on as the DEVMASTER by running

create user dev1 password dev1 like devtemplate

And there you have it.

One problem by using DROP scripts is, that the actual release of the pages of the dropped objects happens asynchronously in the background.

If you drop large tables and shutdown the database shortly after that it may happen that there will stay some pages marked as allocated although they are not.

regards,

Lars