add a user with sql-plus

[Former Member]

Hello I want to add a Sap-user into the database (on unix).

I created the user registry on USR01, USR02 and UST04 tables, I can log-in with the new user but the profiles are not charged.

I think thet I shuold put a new entry in the USR04 table, but on sqlplus I can´t find this table. Can somebody tell me where can I find this table?

Thanks in advance.

Angel

[Private_Member_27907]

Hi,

could you finally achieve this project?

I am very interested in this issue. Everyday, a copy of a production system is done into a isolated environment for testing purposes.

I am using several scripts to setup this mirror system but due to inherited permissions from the production SAP System I am unable to launch some reports via startrfc program from OS with my current SAP user.

I was thinking of creating of a new user with SAP_ALL via sqlplus before SAP startup.

Create a user in Production for this purpose is forbidden by audit department.

Thanks in advance.

[Former Member]

Tomas,

      I always do an export of the user information of the current system (QAS, SANDBOX, training, etc.) before refreshing.  That way I can import the user information after the refresh and have access.  If it is a copy to a non-production system why can't you just use SAP*?

[Former Member]

One or two admonitions about touching tables directly is enough. If you can't add to the discussion, don't post. You don't have to clean up after him so don't worry about it. If there are better or more approriate ways, by all means post them but be complete in your answer. All of us are leaning in one way or another. SOme of the answers have been excellent in helping me learn about the structure of SAP.

[Former Member]

Hi,

To satisfy your curiosity: the reason why you don't see USR04 in the database is that USR04 is not a transparent table (i.e. a table in the SAP dictionary that has a 1:1 relation with a table in the database), but a "pooled" table, i.e. a logical table contained along with many other tables inside one physical database table. In this case the physical table (the "pool") is UTAB. You cannot update such a table directly at DB level because the SAP data fields are contained in a single Long Raw column of the pool.

Let me also repeat that creating a SAP user directly in the database is a very bad idea and likely to fail anyway. First of all you will probably overlook some tables involved in user administration (unless you do a SQL trace). Secondly, the way authorization data is physically stored in the tables will defy all your attempts to enter such data with SQLPLUS. The profiles and authorizations are stored in raw fields with binary length indicators and the like.

If you are in a SAP sandbox system and your only aim is to learn, then a much more prodctive method is to go at it the other way round: run the SAP transaction with the SQL Trace on, then use SQLPLUS (or SE16) to query the tables and find out what gets written where and how everything fits together.

Finally, if what you want to do is to create, change, ... users programmatically rather than via SU01, then use BAPI function modules in ABAP (BAPI_USER_CREATE, etc.); use SE37 to get a list; you can try them out in Test Mode inside SE37.

[Former Member]

Hi,

Sorry not related. What will be happened if we loose table primary index UTAB~0?

This is actually happened and no body can not logon to the system now. How to solve it?

Thanks a lot,

Yan

[former_member204746]

IT is not a good idea to try to do this, but if you really want to play with this, activate an ST05 trace, create a new user with SU01, you will get a list of SQL requests done to DB... You will find dozens of affected tables...

you have now everything you need to play around in the DB... but your buffers won't get updated at SAP's level if you insert manually into DB..

definitely not a good idea.

[Former Member]

Hi,

Just cant understand why to take such a lot pain when it can be done easily via SU01 if there is no unfair intention?

Regards

[Former Member]

Hello,

I dont't think create a SAP user directly in db is a good way, that may cause the crash of db. Instead you can add a SAP user using t-code su01, and authorize the new user.

Regards

AiJiA

Message was edited by:

Jia Ai

[vince_laurent]

According to my DB02 there isn't such a beast.

Owner Object

SAPR3 USR01

SAPR3 USR01~0

SAPR3 USR02

SAPR3 USR02~0

SAPR3 USR02~001

SAPR3 USR02~002

SAPR3 USR03

SAPR3 USR03~0

SAPR3 USR06

SAPR3 USR06~0

SAPR3 USR07

SAPR3 USR07~0

and so on...

are you wanting to add another oracle user OR another sap user? Will this user log into oracle or into SAP?

http://www.adp-gmbh.ch/ora/sql/create_user.html might help.

Vince

[Former Member]

Hello

I want to create a SAP user, on SAP SE16 transaction I can see the USR04 table, but I don´t see this table in sqlplus. To can log on into the SAP System I only need to create the entry at USR02 table, but if I want to give permission to this user I need to add the authorizations in UST04, but it is not enought, I think that I need to edit the USR04, but I can´t see this table at oracle level, maybe this table have another name.

What do you think?

Thanks in advance.

Angel

[Former Member]

Why are you creating SAP users directly in the database and not through the proper SAP transactions?

Best Regards,

Matt

[Former Member]

Hi

It is not good practice to add to DB using sqlplus.

any way to display tables u need to give sap<sid>.<tablename>.

Ex SQL>select * from SAPSID.USR02; Etc.....

Regards

Vinod

[Former Member]

Hello

The first, thanks to everybody for the help.

About the reason because I want to create the user directly on DB and not using the sap transaction (SU01), it is only a curiosity.

As I said before, I managed to create the user using the next insert on SQLplus:

SQL> insert into sapr3.usr02 (MANDT, BNAME, BCODE, GLTGV, GLTGB, USTYP, CLASS, LOCNT, UFLAG, ACCNT, ANAME, ERDAT, TRDAT, LTIME, OCOD1, BCDA1, CODV1, OCOD2, BCDA2, CODV2, OCOD3, BCDA3, CODV3, OCOD4, BCDA4, CODV4, OCOD5, BCDA5, CODV5, VERSN, CODVN, TZONE, ZBVMASTER) VALUES ("Here i insert the values separated by "commas"(,)");

Now I have the user that can do log-in into the sap-system, but my user haven't got any authorization, rol or permission. To solve the new problem there are two tables in SAP, UST04 and USR04, I managed to insert the registry in "UST04" but I can´t do it on USR04 because this table doesn´t exist on DB-level (maybe this table has another name).

Thanks you

Angel

[Former Member]

First,

I do not agree on touching those tables. If you want to add a user and authrizations for the user then use the proper methods.

If you cannot use the table, that means that is a pool or cluster table ( please, check it on SE11, on 45B it was a pool table )

For that reason you

-> cannot find it on the DB as it does not exist there

-> it is not possible to manipulate at DB level