Conditional sql WHERE clause based on column values

Question

I have a table that stores users. Every user has an ID, a Name and an Access Level. The three possible Access Levels are Administrator, Manager and Simple User.

What I want is to conditionally select from this table based on the Access Level value. I demonstrate the logic bellow:

1. If user is Administrator, then select all users (Administrators, Managers, Simple Users)
2. Else if user is Manager select all Managers and all Simple Users
3. Else if user is Simple User select only himself

Is that possible?

Answer 1

Providing Access Level is an integer that increases with the actual access level: Administrator = 3 Manager = 2 User = 1

Then

SELECT * FROM USERS
WHERE ACCESS_LEVEL <= (SELECT ACCESS_LEVEL FROM USERS WHERE ID = @ID)

And just switch the less than sign to greater than if it is opposite.

Applies to SQL-server

EDIT: To get what you want you can use something like this:

SELECT id,name FROM users where ID = @id
UNION DISTINCT
SELECT id, name from users where access_level <= 
    (select case when access_level = 1 then 0 else access_level end
    from users where id = @id)

With a query like this you will select all users at your current access_level or lower. Only exception if your access_level is 1 (e.g. normal user), then only your own user is selected.

Answer 2

In Oracle the concept is known as Row Level Security. See articles such as this or this to get you started.