How can i prevent a user, with ALTER USER system privilege, to change SYS
password?

You can't! Revoke ALTER USER from them if you can't trust them, that
is all you can do.

I would give this privilege only to DBAs, and I would have to trust
them that they know what they are doing, otherwise they wouldn't be
given the DBA privileges in the first place.

HTH,

Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer

Hi,

If your users are to use a Forms 4.5 / 5.0 application, and have to have the
ability to change their own password there is a way to dot this without
having to grant them ALTER USER directly:
1) Make a role with the ALTER USER privilege. Make the role
password-protected.
2) Grant the role to the users. Be sure it won't be their default role
3) Set the role from within your apllication using forms_ddl

I have done this with a Forms application, but I don't know if you could use
this method using other application platforms.

Why do you want to grant ALTER USER privilege to the end user in the
first place? Users don't need to have this privilege to be able to
change *their own* password! Users don't need to have been granted any
system privilege or role at all to be able to change their own
password. (Well, sure, they need at least CREATE SESSION privilege so
that they are able to connect to the database, but that is all that is
needed!)

Regards,

Certified Oracle7 DBA (OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer

Threat to kick his behind if he does it.

A user that is able to change the password for ANY user in the database
has to be trustworthy.

Remco
--
rd31-144: 10:35pm  up 21:24,  5 users,  load average: 1.00, 1.04, 1.10

Hi,

Well, you have a point there Jurij. I was too quickly!

In fact the situation in the application is that there are common users who have
rights to use the application, but only  have rights to query information about
other users. Beside the common users there are a group of superusers (not DBA's)
who have the rights to add new common users, and these are on the same time added
as oracle-users to the database. These superusers are the ones who I set the role
with alter user privileges for. Does this make it clearer?

My point is that the runtime, applicationbased set of the privileged role is one
of the ways you can protect the database from uncontrolled use: Even though the
superusers have access to the database with for example SQL*PLUS, they are not
able to apply their application-rights. That is of course if they can't guess the
password of the privileged role.

Regards,

Michael Ringbo

We had the need for certain end-users ("application owners") to be able
to setup users for their own apps and reset passwords etc...We overcame
the problem above by using queues...

end-user submits a request to the queue (to create an account or reset a
password etc)..The queue listener (which runs with under SYS or
something similar) then processes the request and does things like
validation checking etc etc and then proceeds with the request (or
emails them an error)...

Took a while to setup but seems to work ok now...

HTH
--
==============================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"Never wrestle a pig - you both get dirty and the pig likes it..."