Database security has several aspects. First, there is authentication: Who can connect? How does one identify them? Second, there is authorization: What are users allowed to do? How does one restrict their actions? Finally, there is auditing: Given that users can connect and perform certain actions, how do you track what they are doing? These aspects are covered in this short note.
When a user logs on to the database, following some means of identification, they connect to a user account, which defines their initial access permissions and the attributes of the session. Associated with a user account is a schema. The terms user, user account, and schema can often be used interchangeably in the Oracle environment, but they are not always the same thing. A user is a person who connects to a user account by establishing a session against the instance and logging on with the user account name. A schema is a set of objects owned by a user account.
A user account must be granted privileges before a session (or sessions) connected to the account can do anything. Many different privileges can be granted for many different objects and actions, and managing privileges individually is not practical for any but the simplest systems. Privileges are usually grouped into roles, which make privilege administration much easier.
You can use profiles to manage passwords and (to a limited extent) control the resources a user is allowed to consume within the instance and the Oracle database.
In many environments, users will have permission to do certain things, but that doesn’t mean they should be able to do them without a record being kept. For example, a database administrator (DBA) will (usually) be able to read and write any row in any table in the database. This is part of their job. For example, if a business data corruption occurs (perhaps through user error or through application software issues), the DBA will need to use SQL*Plus or some other tool that can bypass all the application security and rules and edit the data to fix it. But that does not mean the DBA should be diving into tables of sensitive data without good reason. Actions such as this cannot be prevented, but they can, and must, be tracked. This is the purpose of auditing: to record actions that are permitted but potentially harmful.
