Oracle User Management

Everything in oracle requires privileges which can be granted, oracle is based on giving the least amount of privilege. The main aspects of Oracle security management are

Controlling access to data (authorization)
Restricting access to legitimate users (authentication)
Ensuring accountability on part of the users (auditing)
Safeguarding key data in the database (encryption)
Managing the security of the entire organizational information structure (enterprise security)

Users

There are 4 main accounts that are created during install sys, system, sysman and dbmsmp, you have to adjust the parameter license_max_users to allow how many licensed users can access the database.

SYS	Owns all internal tables
SYSTEM	Has additional tables and views
SYSMAN	Use by OEM to monitor and gather performance stats, which are stored in the *sysaux* tablespace
DBSNMP	Same as sys but for the OEM, owns all internal tables in the *sysaux* tablespace.

There are two privileges which many junior DBA get confused (including myself) with sysoper and sysdba, these are system privileges not users or roles, see here for more details on these two privileges.

All users need a default tablespace, this is where all objects created by the user will be stored and a temporary tablespace which is where they perform work such as sorting data during SQL execution. Make sure that you assign the tablespaces as on some systems they could end up using the system tablespace which is not a good idea.

Creating	create user vallep identified by password; create user vallep identified by password default tablespace users temporary tablespace temp quota 100m on users;
Remove	drop user vallep; drop user vallep cascade; Note: the cascade option will remove all the users objects as well.
Alter	alter user vallep idenitfied by newpassword; alter user vallep quota 200m on users;
Password options	alter user vallep identified by password; alter user vallep identified externally; alter user vallep identified globally as extname; Note: identified by - the password will be kept in the data dicitonary identified externally - authenication will be performed by the O/S idenitified globally as extname - authenication will be performed by external app i.e radius a user is only allowed to change is his/her password
Expire password	alter user vallep password expire;
Lock/unlock	alter user vallep account lock; alter user vallep account unlock;
Connecting	grant create session to vallep; Note: this allows the user to connect to the database
Revoke access	revoke create session from vallep;
Quota	alter user vallep quota 100m on users; alter user vallep quota unlimited on users; grant unlimited tablespace to vallep;
Kill a users session	select username, sid, serial# from v$session; alter system kill session '<session_id>,<session-serial>';
User connection type (decicated/shared)	select username, program, server from v$session;
Useful Views
DBA_USERS	describes all users of the database
DBA_TS_QUOTAS	describes tablespace quotas for all users
V$SESSION	lists session information for each current session

By default oracle passwords are sent in clear text across the network, set the following environment variables to encrypt the password between the client and server.

Server	dblink_encrypt_login = true
Client	ora_encrypt_login = true

Profiles

Profiles are used to limit a users resource, it can also enforce password management rules, only the DBA can change profiles. There is a global default profile which every users is assigned to if they are already not assigned to one. If a user reaches one of the limits in the profile the transaction is rolled back and a error message is displayed stating that a resource limit has been reached. There are a number of resources that can be limited

connect_time - limits session to number of minutes
cpu_per_call - limits cpu time by any single database call
cpu_per_session - limit cpu by session
idle_time - limit session to idle time, allows user to rollback or commit before logging off
logical_reads_per_call - caps the amount of work by any single database call
logical_reads_per_session - caps the amount of work by any session
private_sga - limits memory when using shared servers
sessions_per_user - limits the number of sessions a user can have
composite_limit - calculated by cpu_per_session, logical_reads_per_session, connection_time and private_sga

The security features that the profile can also manage are

failed_login_attemps - number of times a user can enter the wrong password before the account is locked
password_lock_time - if the above is breached lock password for this number of days
password_life_time - number of days a password can remain in force
password_grace_time - number of days user is notified but is used in above value
password_reuse_time - maximum # of days before a password can be reused
password_reuse_max - minimum # of different passwords before password can be reused
password_verify_function - allows the use of a function to be used to verify a password

Creating	create profile user_profile limit sessions_per_user 5;
Remove	drop profile user_profile cascade; Note: any users using the dropped profile will be automatically assigned the default profile
Setting a limit	alter profile user_profile limit idle_time 30;
Displaying current resource limits	select * from user_resource_limits;
Displaying current password limits	select * from user_password_limits;
Displaying profile	select * from dba_profiles where profile = 'USER_PROFILE';
Assign a profile	alter user vallep profile user_profile;
Useful Views
USER_RESOURCE_LIMITS	displays the resource limits for the current user.
USER_PASSWORD_LIMITS	describes the password profile parameters that are assigned to the user.
DBA_PROFILES	displays all profiles and their limits

Before profiles are used you must set the following systems parameter, you have to restart the database in order for the changes to take affect.

Enable resource limits	alter system set resource_limit = true scope = both;
Disable resource limits	alter system set resource_limit = false scope = both;

Roles

See data access for more information on roles.