Chapter 8 Administering User Security

Create and Manage Database User Accounts
User Account Attributes
UserName

Default Tablespace and Quotas
ALTER DATABASE DEFAULT TABLESPACE tbs_name;
ALTER USER user_name quota n[MGT]/unlimited on tbs_name;

Temporary Tablespace
ALTER USER user_name TEMPORARY TABLESPACE tbs_name;

PROFILE: control password and resource usage limits

Account Status
OPEN, LOCKED, EXPIRED, LOCKED(TIMED), EXPIRED(GRACE)
ALTER USER user_name ACCOUNT LOCK/UNLOCK;
ALTER USER user_name PASSWORD EXPIRE;

Authentication Methods (For starting/shutdown instance, administrative tasks)
Operating System and Password File Authentication
GRANT [sysdbasysoper] TO user_name;

CONNECT / AS [SYSOPERSYSDBA]; (Local Machine Connection)
CONNECT user_name/password [@db_alias] AS [SYSOPERSYSDBA]; (Remote Connection)

Password Authentication (Normal, stored in data dictionary)
CONNECT user_name/password [@db_alias];
ALTER USER user_name IDENTIFIED BY newpassword;

External Authentication (using for normal user in additonal to the SYSDBA, SYSOPER]
OS_AUTHENT_PREFIX=ops$

Note the following user_name is the same as Operating System user_name:
create user ops$user_name identified externally; (normal user, see OS and Password File)
grant create session to ops$user_name;
sqlplus /

Global Authentication: LDAP directory server, the Oracle Internet Directory

SYS is different from SYSTEM because SYS can only log on as SYSDBA or SYSOPER, however, SYSTEM can log on as SYSDBA or SYSOPER or as Normal User.

Create User Accounts
create user scott identified by tiger
default tablespace users temporary tablespace temp
quota 100m on users, quota unlimited on example
profile developer_file
password expire
account unlock;

alter user scott identified by lion;
alter user scott default tablespace hr_data temporary tablespace hr_temp;
alter user scott quota unlimited on hr_data, quota on on users;
alter user scott profile prod_profile;
alter user scott password expire;
alter user scott account lock;
drop user scott [cascade];

Grant and Revoke Privileges
System Privileges: actions affect data dictionary (with admin option)
GRANT privilege [, privilege...] TO user_name [WITH ADMIN OPTION];
REVOKE privilege [, privilege...] FROM user_name; (No Cascade)

Object Privileges: atcions affect user data (with grant option, cascade ok)
SELECT, INSERT UPDATE, DELETE, ALTER, EXECUTE, ALL

GRANT privilege ON schema.object TO user_name [WITH GRANT OPTION];
REVOKE privilege FROM user_name; (Auto Cascade)

Create and Manage Roles (to group system privileges and/or object privileges under one Role, can be activated/deactivated)
CREATE ROLE role_name;
GRANT role_name1 TO user_name/role_name2 [WITH ADMIN OPTION];
REVOKE role_name1 FROM user_name/role_name2; (No Cascade)

ALTER USER user_name DEFAULT ROLE role_name;
SET ROLE role_name;
CREATE ROLE role_name IDENTIFIED USING procedure_name;

DROP ROLE role_name;

Create and Manage Profiles (to enforce a password policy and to restrict the resources usage for a session)
Password Management (Enforced by default)

CREATE/ALTER PROFILE DEFAULT/profile_name LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION verify_function_11G;


Resource Limits (Enforced only if RESOURCE_LIMIT=TRUE, default is FALSE)
ALTER PROFILE DEFAULT LIMIT
SESSION_PER_USER 1;

ALTER SYSTEM SET resource_limit=true;

ALTER USER user_name PROFILE profile_name;

DROP PROFILE profile_name; (A profile cannot be dropped if it has been assigned to users)
DROP PROFILE profile_name CASCADE; (Auto reassign all users with profile_name back to the DEFAULT profile)