Row Level Access with SQL

Posted on 2005-03-01
Medium Priority
Last Modified: 2008-02-01
Is there a way to do row level (essentially, column level) access with standard SQL without using vendor-specific packages?
Question by:lcor
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 13434197
You mean something like rowid in Oracle? (But then dbms independent, standard SQL)

Expert Comment

ID: 13434202
Or do you mean rowlevel privileges?
LVL 27

Expert Comment

ID: 13434354
I am confused by your question. Row level (essentially column level) access with standard SQL - do you mean ANSI-SQL? Do you mean some sort of SQL tool? Inquiring minds and all.
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

LVL 25

Accepted Solution

jrb1 earned 800 total points
ID: 13434650
I'm betting you mean row level security.

The most common form of row level security has been maintained through views. You have a view that limits a user to a subset of data in a table.  This will limit both reading and writing of data.  Features introduced in 8i gave you more options.


And new options in 10g may simplify the whole situation:

LVL 25

Expert Comment

ID: 13434661
Oops....i saw oracle in the first answer and was thinking this was an Oracle question.  What DB are you talking about, or just SQL in general?

View are the simplest way to do this in any DB.

Author Comment

ID: 13435876
jrb1 is right -- row level security.

I'm using Oracle and am finding that Oracle Label Security only goes to the table level.
LVL 25

Expert Comment

ID: 13436805
Not according to the Oracle 10g doc I posted above:

Oracle's 15th security evaluation is the first internationally recognized evaluation for Oracle Label Security, Oracle's server-enforced, row-level access control. Unique in the industry, Oracle Label Security allows many user communities to share the same database, yet only access data rows that pertain to each individual or community. It is the first and only independently evaluated security policy for controlling access to hosted-application data in a consolidated data repository.
LVL 11

Assisted Solution

sujit_kumar earned 400 total points
ID: 13436814
LVL 15

Assisted Solution

mcmonap earned 400 total points
ID: 13436976
Hi lcor,

>>without using vendor-specific packages

Like jrb1 says above I think this is best implemented with views.  You would create views offering only specific columns and rows and then assign user rights to these and remove all their rights from the underlying tables.  In this way they can only select from the subset of data within the view.
LVL 18

Assisted Solution

BigSchmuh earned 400 total points
ID: 13438766
If you don't care of user viewing data but only not to modify others, you can although use simple Triggers...
==> That way, you have full control of the access rights for write access (even on a column or row basis)


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question