*1.What's is the logic applying here ? ops$ using oracle env*
*2.why cant create more than one user along with prefix ops$ ?*

what i know ,
by default it is using oracle user env .. am i right ? If it is wrong , correct me ...

one more final expectation :
Database user have to supply a password, that is a big hole in the security policy.
but You mentioned in previous replies *"never used OS authentication for any account except 'oracle"*What's your final conclusion ? => please provide good solution.

When the shell processor encounters a "$", it takes that as a meta-character, indicating that what follows is the name of an environment variable, and it will replace that with the value of said variable. So when you enter 'useradd ops$rose' the shell process replaces that with 'useradd ops<value of environment variable rose>' Since you don't have an environment variable 'rose' (there is no reason you would) the statement becomes 'useradd ops'.

useradd: user ops exists

Note: but existing ops$user name is ops$sam only.

Do not confuse oracle usernames with OS usernames. When you tell the OS to create a user (useradd) it has no bearing whatsoever on any user of any type that you have created in your database. When you issue 'useradd', the OS is going to try to create an OS user. It doesn't know, it doesn't give a flying fig, it doesn't check with the database for any potential relationship. If you issued 'useradd ops$rose' and got "useradd: user ops exists", that is a clear AND DEFINITIVE indication that you issued that command once before.

You can have all of the OS authenticated users you want. But you have to create them correctly. Did you read what the initialization parameter OS_AUTHENT_PREFIX actually does? Did you read it in the actual, official Oracle® Database Reference? That should be enough of a clue right there, but I'm going to take it a bit further. You can find the relevent portion of the Reference at http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams174.htm#sthref504.. The specific sentence there begins with "Oracle concatenates the value of this parameter ..." Read that several times, and then re-read what I said above about your 4 OS authenticated accounts.

Personally I only want one or two OS authenticated users, and I want them to have sysdba authority. I still do not understand why you are trying to create all these non-sysdba os authenticated users.

As I explained before creating a user with the OPS$ prefix is NOT what makes it an OS authenticated user. So you just created a database authenticated account named 'OPS$ROSE' and successfully connected to the account by supplying the username 'OPS$ROSE' and the correct password.

And why would you expect to see os account 'oracle' as an OS authenticated account listed in the database? . OS user is 'oracle' is a member of the OS group 'dba'. Any user that is a member of the 'dba' group can connect via OS authentication with sysdba authority. This is very different than an 'os authenticated database account' It's the membership in the dba group that gives them this ability. And these accounts cannot connect without sysdba authority.

You can, but you have to do it correctly. Again, read the Reference manual on what OS_AUTHENT_PREFIX means. Think about how the shell processor handles the "$" character.
>

what i know ,
by default it is using oracle user env .. am i right ? If it is wrong , correct me ...

What "it" do you think is "using oracle user env"?
When an os user creates a session on the OS (connects to the os), several files, including /home/<osusername>/.bash_profile are executed to establish a starting point for that session's environment. This is purely an OS issue and has NOTHING to do with Oracle. Of course, there are certain environment variables that sqlplus and other oracle software expects to be correctly set. Things like $ORACLE_BASE, $ORACLE_HOME, $ORACLE_SID. But the OS doesn't inherently know about them. After all, as far as the OS is concerned, all those oracle processes and utilities are "just another application process".

one more final expectation :
Database user have to supply a password, that is a big hole in the security policy.

Why do you think supplying a password is "a big hole in the security policy."? Are you under the misguided perception that os authentication is somehow more secure? Please do explain.

but You mentioned in previous replies *"never used OS authentication for any account except 'oracle"*
*What's your final conclusion ?

My final conclusion remains. I do not create OS authenticated users in my databases. I grant membership in the OS 'dba' group to a VERY limited set of OS accounts. In fact, in my current shop, where I am the only Oracle DBA, the ONLY OS account that is a member of the DBA group is the owner of the oracle software: oracle.

=> please provide good solution.*

A good solution for what? You've STILL not stated your business problem. You've only led us through a series of issues with a per-conceived technical solution to an unknown (unknown to us) business problem.

They are biological professional not oracle professionals.
We have different kind of projects. Recently we implemented ORACLE DB to research scholars
+[local database i.e . Database resides same server]+ That is not a (24*7) domain.

Every morning they start up database and end of the session they shut the DB.
Here Every user maintaining Some biological related formulas and statistics reports. The only thing isdiscovered DATA is important

So here problem is every one can start up DB. So we planned to restrict to make all users as
*" OS authenticated users " because external users don't have SYSDBA privilege. So we feel*DB will be safe, but the same time All users are " OS authenticated users" .

Ok, so what is your relationship to them and what is your role in all of this?

We have different kind of projects. Recently we implemented ORACLE DB to research scholars
+[local database i.e . Database resides same server]+ That is not a (24*7) domain.

Every morning they start up database and end of the session they shut the DB.

Why?

Here Every user maintaining Some biological related formulas and statistics reports. The only thing isdiscovered DATA is important

So what process do you have in place to insure there is no loss of data when (not "if", but "when") something goes wrong.

So here problem is every one can start up DB. So we planned to restrict to make all users as
*" OS authenticated users " because external users don't have SYSDBA privilege.

Well, there are really two kinds of OS authenticated users. The first kind is what you've been struggling with. "External" users are OS authenticated.
But the other kind of OS authenticated user is the os user that is a member of the os 'dba' group. Members of that group can connect 'as sysdba'
Or connect as 'sysoper', which still allows startup/shutdown but otherwise more limited than 'sysdba'.

So we feel*DB will be safe, but the same time All users are " OS authenticated users" .

I asked you before *WHY* you feel that supplying a password is a big security hole. You haven't answered. If you are depending on OS authentication, you are necessarliy depending on someone to log on to the os by supplying an OS username and password. Why is that more "secure" than supplying a username and password to the database?

Another drawback also for os authentication user ..

For ex ops$rose is os authenticated user , if OS having another user having rose means
*rose user can connect as ops$rose"

What do you mean by "another user having rose"? If you create a database account 'ops$rose' identified externally, then by definition you are saying that OS user 'rose' can connect to the database without supplying any further credentials. And on the OS, all usernames are unique. There cannot be "another" user "rose".

* The main problem is "they are maintaining DB , kindly help me !!

IF "they" are maintaining the DB, then again, what is your role and relationship to this?

Every morning they start up database and end of the session they shut the DB.Why?

No one can update any information after their sessions.
Another one members can't access the DB from outside i.e.
only from production floor. so no need to run continuously.

IF "they" are maintaining the DB, then again, what is your role and relationship to this?
Here "Data growth is not high " If any new joiners , additionally i guide some SQL concepts about oracle env regarding to that project.
My major role is providing privilege to users (create session , create table ..) and then any request from DB users , i will do that.
monitoring DB also .

So what process do you have in place to insure there is no loss of data when (not "if", but "when") something goes wrong.*

on daily basis i take export/import (backup). No data loss here ..

Here, i restrict all os users i.e my major role is providing specific privilege to every one.Some users having os authentication , For ex ops$samWhy do i restrict ?

If i provide "create session privilege" to users , they have to enter username password like this *'ops$rose/rose'*
Oracle won't prompt user name and password separately.
Even we miss 'quotes' , oracle throws "username" and password".

My role , they asked me to setup Database env for biological professions.
Once i finished my roles i will leave from that project.
On daily basis i will do "only backup"

*Please Note :* _I want to setup SYSDBA privilege only to specific users_
*Even i make all users as "DB authenticated " how can i SETUP "only specific people" can startup DB. ?*
Kindly help me to restrict SYSDBA privilege from DB users.

Every morning they start up database and end of the session they shut the DB.Why?

No one can update any information after their sessions.
Another one members can't access the DB from outside i.e.
only from production floor. so no need to run continuously.

No one without proper authority can update anyway. And one would assume that those with proper authority would not be doing any "unauthorized" updates.

>

IF "they" are maintaining the DB, then again, what is your role and relationship to this?
Here "Data growth is not high "

What does that have to do with the price of eggs in China?

If any new joiners , additionally i guide some SQL concepts about oracle env regarding to that project.
My major role is providing privilege to users (create session , create table ..) and then any request from DB users , i will do that.
monitoring DB also .

So what process do you have in place to insure there is no loss of data when (not "if", but "when") something goes wrong.*

on daily basis i take export/import (backup). No data loss here ..

Better than nothing, but an export will not protect against physical corruption of the database. You are gambling that your hardware will never fail. I'd like to invest in your hardware vendor, because they have apparently achieved something no vendor has achieved previously.

Here, i restrict all os users i.e my major role is providing specific privilege to every one.Some users having os authentication , For ex ops$sam

"Some" users? Why "some"? Why not "all" or "none"? What criteria is used to decide?

Besides, you've only described (not shown) what you think you see. You still did not answer my question of WHY you think password authentication is a "security hole". You are worried (without reason) about password authentication being a security hole, but you are trying to figure out how to give sysdba authority to every Tom, Richard, and Harry in your database.

Even we miss 'quotes' , oracle throws "username" and password".

I have no idea what you are trying to say by that statement. "miss 'quotes'"? What do you mean?

I tried to make restrictions for sysdba but *"NOT SUCCEEDED"*

Well, I have no idea what you did to "make restriction for sysdba", nor what you expected, nor what command returned "NOT SUCCEEDED".

remote_login_passwordfile = NONE means that the only place people will be able to connect with sysdba authority (necessary for a database shutdown/startup) will be from the server that is running the database. Which is considered to be a good thing.

*Even i make all users as "DB authenticated " how can i SETUP "only specific people" can startup DB. ?*
Kindly help me to restrict SYSDBA privilege from DB users.

Actually, I already told you in a previous post.

Frankly, at this point I'm very reluctant to do anything to help you dig a deeper hole. It looks to me like your organization purchased a Ferrari when all it needed was a VW Beetle, and they want to use it without a trained driver or mechanic.

I want to really restrict this thing only.Even i entered wrong password , i can connect my DB and startup my DB.

Already i mentioned data growth is not high .. So currently no more DBAs not required
for this project. Currently i have to restrict sysdba privilege from DB users;

The default Oracle security is that everything is forbidden; except that which is explicitly GRANTed.
So there is NO concept to "restrict" access.
If no GRANT is ever issued, then no access is possible.

With regard to access "as sysdba", it is controlled at the OS group level.
When OS user is member of "DBA" group, then they can login "as sysdba" without username/password verification.

They are biological professional not oracle professionals.
We have different kind of projects. Recently we implemented ORACLE DB to research scholars
+[local database i.e . Database resides same server]+ That is not a (24*7) domain.

<snip>

Something doesn't ring true here. This has been such a long running thread I decided to go back and review the whole thing. (Why am I doing this on a Saturday?)

Then later you start talking about
- They are biological professional not oracle professionals.
- We have different kind of projects. Recently we implemented ORACLE DB to research scholars

What's going on here?
Please read the forum FAQ. The link is in the upper right corner of the page you are reading at this very moment.
Please do not use one thread as a running open help line for whatever is your problem de jour. A thread should focus on one issue and one issue only.
Your first post of a thread should ALWAYS state what version of Oracle, to at least 4 decimals (that would be like 11.2.0.1. 11g is not a version, it is a marketing term) and edition (Enterprise, Standard, Standard ONE, XE). It should also include the OS, the OS version, and if applicable, the OS edition (Windows 7 Professional, Windows 7 Home, Windows 2008 Server, Oracle Linux 5.7)

Series of problems are put it here . Please don't consider given information's were not true.
Biological students actually maintained datas iin their EXCEL SHEET then we decided to arrange database
for biological professionals. My working env and this project entirely different. Series of problems are put into one place .. this is reason you felt Something doesn't ring true here.