Grant/Revoke Oracle SQL

Question

I want to set grant/revoke and create roles in oracle apex. However I do not want to do this for users but for data in a table e.g based on the Id in the table I want to revoke their permissions to update any data. Is this possible and what is the best way to do this.

score 0 · Answer 1 · answered Mar 19 '14 at 15:13

0

You can't do this for a specific row in your table. You can only issue the GRANT/REVOKE for a table as a whole.

http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

You would have to deal with updates to a specific data in your table at the application (PL/SQL) level.

answered Mar 19 '14 at 15:13

Drumbeg

1,914
1
15
22

My issue isn't that I want to grant/revoke on a specific row. It's that I want to take the data from a column to revoke their access. eg If I had a column full of Employee_Ids I only want Employees to see the data their Id is assigned to without making them a public user – user1643333 Mar 19 '14 at 15:26

score 0 · Answer 2 · answered Apr 17 '14 at 13:32

0

A couple of options:

You could either use Oracle Label Security.
You can create a view on top of the table, which would have a query based on the current user's EMPLOYEE_ID.

answered Apr 17 '14 at 13:32

Joseph B

5,519
1
15
19

score 0 · Answer 3 · answered Dec 16 '16 at 08:56

A few notes, having done things like this in the past on PostgreSQL.

It is possible to create views on top of data for this sort of thing.
Row level security is what you usually want to do. See http://www.dba-oracle.com/concepts/restricting_access.htm for a tutorial.

The first approach has all the issues with the second approach and then some. The reason is that you have to write a policy engine into your view. In the second approach you get a policy engine you can just apply policies to.

A few notes from experience:

You really want to avoid looking up permission data from other rows in a table if you are ever going to review things many rows at a time.
Think through the performance implications of permission checking carefully before you implement it.

This sort of thing is usually a performance headache that takes some time to resolve. Be prepared for a long period of optimizations as use cases you had not thought of become important.

Grant/Revoke Oracle SQL

3 Answers3