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


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.


create user vallep identified by password;
create user vallep identified by password default tablespace users temporary tablespace temp quota 100m on users;


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;

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;

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
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 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

The security features that the profile can also manage are

Creating create profile user_profile limit sessions_per_user 5;

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;


See data access for more information on roles.