Re: users / password file clarification

On 3 May 2004 10:37:55 -0700, pmurphy_at_scsinet.com (Paul Murphy) wrote:

>I just want to be sure I'm clear about recovering user accounts from a
>backup, since it's not covered in the OCP books. Hopefully, each of
>the following statements is true, if not, please let me know.
>
>If you have only DBA accounts in the password file, the regular users
>are stored in the system tablespace.

You can't store ordinary users in the password file. All users in the password file will have either SYSDBA or SYSOPER priv or both.

Ordinary users are stored in sys.user$

All users, other than SYS, can log in as an ordinary user, so they are in both user$ and the passwordfile.

>
>If you add regular users after the system tablespace is backed up and
>have to restore the system tablespace data file and roll forward using
>the redo logs, the user accounts will be recreated when the user table
>in the system tablespace is rolled forward.

That should be true. But how often do you create user accounts, that you worry?

>
>What I'm wondering is:
>
>Is a create user statement just a DML insert into a table and will be
>rolled forward like any other insert statement?

create user is Data Definition Language. Consequently it is protected by the SYSTEM roll back segment and committed automatically.

>
>Thanks for clearing this up for me.
>Paul Murphy

--
Sybrand Bakker, Senior Oracle DBA