Blocking An Oracle Table from Users Even for Select

Toad for Oracle & DevOps Toolkit
1

Oracle 11g.
Linux

I am trying to block a set of tables from some internal users. Mostly PHI data.
Select, Update everything.

The way I did in MS SQL was create a Role with Deny security to these tables. Then assign users to the role.

Any simple way to do it in Oracle?

Appreciate the help.

2

Evening Prakash ,

By default, Oracle offers no access to tables etc in other user accounts. In order to see those tables, you must be granted select, at the very least, to see them, and you need to know which user owns them, and their name too, at least until you create a synonym for them. Or look in ALL_TABLES.

If the users you want to block login to the same user account that owns the tables, then you can’t do much I’m afraid. That user owns them.

If the users you want to block have their own accounts, as they should - for auditing purposes amongst others - then you have nothing to do as they cannot see the tables in the other user accounts. By default.

In oracle, you have to give permissions to let other users see your tables, not to prevent them.

HTH

Cheers,

Norm. [TeamT]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

3

JUst to be more simple.

Oracle does not have “deny” schema".

All tables in any schema are visible to owner of that table and some privileged users (dba, sysdba roles or sys, system users).

This means that after creation you should add “grant all” to users who can access those tables.

But again privileged users you cannot stop EASY (there is a walt option in Oracle) to see the content.

Chers,

Damir

4

You might be able to use Oracle's Virtual Private Database. (fine grain access control)

https://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG007

Once you set that up, I am thinking you could do one of two things:

  1. Add a column to your table, make it Null. Then set the policy to only select rows where this column is not null.
  2. or add a few dummy rows to the table with values no other record in the table has. Then set the security policy to select that criteria. Personally I like #1 better.
    I have not done this, but I am theorizing based on the Documentation for Oracles VPD. I do not know if this would affect power users such as dba, sysdba, sys etc.

Hope this helps.

On Tue, Jul 25, 2017 at 2:50 PM, prakash.chada bounce-prakashchada@toadworld.com wrote:

Blocking An Oracle Table from Users Even for Select

Thread created by prakash.chada
Oracle 11g.
Linux

I am trying to block a set of tables from some internal users. Mostly PHI data.
Select, Update everything.

The way I did in MS SQL was create a Role with Deny security to these tables. Then assign users to the role.

Any simple way to do it in Oracle?

Appreciate the help.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

5

p.s.

The VPD also works with users looking at data on forms.

On Tue, Jul 25, 2017 at 2:50 PM, prakash.chada bounce-prakashchada@toadworld.com wrote:

Blocking An Oracle Table from Users Even for Select

Thread created by prakash.chada
Oracle 11g.
Linux

I am trying to block a set of tables from some internal users. Mostly PHI data.
Select, Update everything.

The way I did in MS SQL was create a Role with Deny security to these tables. Then assign users to the role.

Any simple way to do it in Oracle?

Appreciate the help.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.