How to create a new user which sees all tables?

[Former Member]

Hi,

how can i create a new user with access to all schemas and tables?

I'm running a MaxDB on a Linux machine for my XI Integration Server. I need to browse through the XI tables, but they are only visible for the SAP<SID>DB user. I don't see them when i'm logged in with SUPERDBA. Unfortunately i haven't a password for this user, so i want to create a new one with all access privileges.

But if create a new user within the Database Manager i can only configure username, pwd, user class and connection mode.

But where can i grant access to all existing tables?

Best regards

Manuel

[Former Member · Accepted solution]

Hi Manuel,

You can view the rights of the user SAPSIDDB using :

dbmcli -u SUPERDBA,PASSWORD -d SID user_getrights SAPSIDDB SERVERRIGHTS

And, give those rights to the user, which you'll create :

dbmcli –u SUPERDBA,PASSWORD -d SID user_create NEWUSER PASSWORD=PASSWORD SERVERRIGHTS=+DBStart,-DBStop

Or simply,

CREATE USER user_name PASSWORD password LIKE source_user

or,

dbmcli –u SUPERDBA,PASSWORD -d SID user_create NEWUSER,PASSWORD TEMPLATEUSER

I hope those help you.

Regards,

Gökhan

Message was edited by:

Gökhan Tenekecioglu

[former_member229109 · Accepted solution]

Hello Gökhan,

1)

Using the dbm command user_getrights you will display the list of server authorizations of the specified DBM operator.

The database user SAP?

• As SUPERDBA ( SYSDBA) user you could create the new user of any database user class.

You need to create the DBA user.

• If the database user SAP<SID>DB is the owner of the tables, then the user SAP<SID>DB could grant any privileges to those tables to another Database User using GRANT Statement.

< References to additional information, documents, Tutorials were given in my above replay. In "Database Users and Their Privileges" section you will see the example to run the grant statement. >.

Thank you and best regards, Natalia Khlopina

[Former Member · Accepted solution]

>If the database user SAP<SID>DB is the owner of the tables, then the user

>SAP<SID>DB could grant any privileges to those tables to another Database

>User using GRANT Statement.

That's exactly the point. Those tables are owned by SAP<SID>DB. But i can't log in with this user, because nobody has the password. So i can't create a new one which sees those tables (which i need to).

I could kill the session of this user and change the password within dbmgui. But i assume that the XI server can't connect to this tables after this change, because his password is not longer valid.

Maybe i install a new system on my local hardware.

[Former Member · Accepted solution]

After changing the password of SAPSIDDB, you need to update the JDBC password of Secure Store within Config Tool.

[former_member229109 · Accepted solution]

Hello Manuel,

-> What version of the database do you have?

< See the version of the database in the knldiag file or run 'dbmcli -d <SID> -u control,<control> show version' command, for example. >

-> Are you SAP customer? < please see the SAP Note No. 25591 to change the passwords. >

Thank you and best regards, Natalia Khlopina

[Former Member · Accepted solution]

Hi,

thanks for your answers. But to create a new user isn't longer neccesary. I found the password in the meanwhile.

Best regards

Manuel Schlestein

[former_member229109]

Hello Manuel,

-> What version of the database do you have?

< See the version of the database in the knldiag file or run 'dbmcli -d <SID> -u control,<control> show version' command, for example. >

-> Could you ask the database administrator the password of SAP<SID>DB user? The password is set at the time of installation. Did you forget it?

-> Are you SAP customer? If you are SAP customer, please see the SAP Note No. 25591.

1)

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

http://maxdb.sap.com/currentdoc/default.htm -> MAXDB library

A) Basic Information -> MaxDB Security Guide < Authorizations -> Defining Clear Authorizations for Users >

B) Basic Information -> SQL Reference Manual -> Authorization < Overview the SQL statements for authorization >

C) Tutorials -> SQL Tutorial -> Authorization < -> Database Users and Their Privileges >.

2)

Please review the Definition, list of the properties, Authorization Concept and more for the Database User & Database System Administrator (SYSDBA User) at MAXDB library -> Basic Information -> Concepts of the Database System

-> Administration -> Users, Authentication and Authorizations

< -> "Database System Administrator (SYSDBA user)" >

3)

Please see the section 'Creating/Changing/Deleting a Database User' at Tools -> Database Manager GUI -> Managing Database Users if you would like to create the database user.

4)

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.

As SUPERDBA user you could create the new user of any database user class.

If the database user SAP<SID>DB is the owner of the table TEST, then the user SAP<SID>DB could grant any privileges to the table TEST to another Database User using GRANT Statement ( see at 1) -> B) & C)).

Privileges: Overview could be found at MAXDB library -> Basic Information-> SQL Reference Manual -> Privileges: Overview

The system table TABLEPRIVILEGES describes the privileges that the current user has for tables.

Thank you and best regards, Natalia Khlopina