Home » RDBMS Server » Server Administration » Column Level Security
Column Level Security [message #370587] Mon, 03 January 2000 16:24 Go to next message
Messages: 7
Registered: January 2000
Junior Member
Hi all,
Is there any way I can imlement column level security from Oracle? Example One user can see Emp No and Emp Name columns from the Employee table and another can see Emp No, Emp Name and Emp Address columns. Will making a view for every user for every table be the best approach/ I have doubts abt it bcos if there are 1000 users and 500 tables per user we have a rapid spawning of views? Any solutions?

Re: Column Level Security [message #370643 is a reply to message #370587] Thu, 13 January 2000 18:05 Go to previous messageGo to next message
Roger D. Cornejo
Messages: 5
Registered: January 2000
Junior Member
Views will work, however, as you've pointed out this will be a problem for large numbers of tables and large numbers of users. Granting access to the views to roles and then granting roles to the users should help manage the problem.
Here's something I havn't tried, but it's an idea:
1) create a <PRIV'S TABLE> with the username and the priv, with one row per user.
2) join the priv's table to the main tables where user = <PRIV'S TABLE>.username
3) in the select list of the main table decode on the priv (e.g. DECODE(priv,'YES', - , null) I.e. don't show the column value if they don't have the priv.

Roger ---|-
Re: Column Level Security [message #370690 is a reply to message #370587] Wed, 19 January 2000 12:21 Go to previous message
Jerry Gitomer
Messages: 1
Registered: January 2000
Junior Member
In the where clause add:

and <COLUMN_NAME> = (select user from dual)

(tested in 8.0.5, may not work in 7.x)
Previous Topic: Duplicate Rows
Next Topic: EXECUTE IMMEDIATE got PLS-00103 error.
Goto Forum:

Current Time: Sun Apr 11 05:41:56 CDT 2021