Administering User Security
Creating and controlling accounts
CREATE USER ... IDENTIFIED BY password (or EXTERNALLY, or GLOBALLY AS directory name) with a default tablespace, temporary tablespace, profile and quota. A user can log in with CREATE SESSION, but cannot own a segment in a tablespace without a quota or UNLIMITED TABLESPACE. Manage state with ACCOUNT LOCK/UNLOCK and PASSWORD EXPIRE; a profile can enforce FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_MAX and PASSWORD_VERIFY_FUNCTION (installed by utlpwdmg.sql).
Privileges, roles and authentication
System privileges (CREATE TABLE, SELECT ANY TABLE) carry WITH ADMIN OPTION and survive revocation of the grantor's own privilege. Object privileges (SELECT, INSERT, EXECUTE on a named object) carry WITH GRANT OPTION and disappear when the grantor's privilege is revoked or the object is dropped.
Roles simplify administration but are not enabled inside definer's-rights PL/SQL - grant the privilege directly or use invoker's rights. Grant to roles, not to PUBLIC, unless every user genuinely needs the privilege.
Administrators authenticate through the password file (built with orapwd, governed by REMOTE_LOGIN_PASSWORDFILE) for remote SYSDBA/SYSOPER logins, or through OS group membership (OSDBA/OSOPER) for local sqlplus / as sysdba.
Traps: DROP USER fails while the schema owns objects unless you use CASCADE; the CONNECT role now contains only CREATE SESSION; the owner of a view needs the underlying object privileges, not the querying user.
Rule of thumb: apply least privilege - object privileges to roles, quotas instead of UNLIMITED TABLESPACE, and consult DBA_SYS_PRIVS, DBA_TAB_PRIVS and DBA_ROLE_PRIVS whenever a user "suddenly has access."