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.

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.

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.

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

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.

Roles

See data access for more information on roles.