1

Taking a course on Oracle database security, I have learned profiles are being created to restrict access of some users to some resources.On the other hand, we have functions and policies, which can restrict users too, but I cannot clear the difference between these two. Why we use profiles while we can create policies for users?

shirin
  • 152
  • 1
  • 14

1 Answers1

2

A Profile restricts how much system resource a user is allowed: how many concurrent sessions they can have, how much CPU they can use, etc. We can also use Profiles for password management: how many failed logins they're allowed, how long a password lasts, etc.

So a Profile defines the broad bounds of system usage. It governs hardware and database access.

By "policy" I assume you mean Fine-Grained Access Control. These policies set limits to what a user can see of an individual table: what rows they can see and what columns. So we could.use FGAC policy to allow a manager to see all the details for their direct reports but only certain information for all other employees.

So a Policy applies further restrictions to a table which a user already has access to. Which highlights the missing P from your question.

A Privilege grants a user permission to do a specific thing. By default a user cannot do anything in a database. They can't even connect to it without the CREATE SESSION privilege. There are two levels of Privilege. A System Privilege allows a user to do general activities like create a table, execute any procedure, query the data dictionary, etc. A Table Privilege allows a user to do something with a specific object owned by a different user, such as select from my.employee_table, execute her.string_function, etc.

So you can see how Privileges and FGAC Policies interact. I might grant you select and update on my.employee_table but enforce a policy that only allows you to update records for department 40.

To summarise. There are four levels of user restrictions. In descending order of breadth:

  1. Profile
  2. System Privilege
  3. Table (i.e. Object) Privilege
  4. FGAC Policy

Note: 12c introduced some called an Audit Policy. Clearly that is relevant to the general topic of Oracle database security but I don't think it's pertinent here.

APC
  • 144,005
  • 19
  • 170
  • 281