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

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.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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 your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

578 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