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;
drop user vallep;
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;
alter user vallep identified by password;
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;|
|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;|
|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.