MS Access oracle roles

Using roles and security with Oracle

Joining Access and SQL Server when it comes to security it really simple. Your user’s AD accounts are used by the SQL instance to set the appropriate permissions and job done. How does one achieve the same group (or role) based security when using Oracle.

Oracle doesn’t fit in with AD very well. There may be strategies that could allow Oracle to recognise these, or a synchronisation mechanism. But this isn’t a OID blog so I won’t get into that. I’ll assume that you have Oracle as a separate entity on your network.

Oracle has Roles which can be assigned to schemas and used in permissions. So if you have a schema which is used by hundreds of users you would assign each user into a role in their user profile. When that user signs in they only see what that role has been given permissions to. Users can have access to multiple roles but in general it will be only one.

As this is not an Oracle blog, I won’t discuss how to set the roles up and assign them to the objects. I’ll leave that as a lesson for the reader. But lets assume we have two roles, aptly named ROLE1 and ROLE2. No idea which has more privileges but it hardly matters.

The Access ODBC connection will be using an Oracle user to login to the database and how can you tie the Windows names and assign the proper roles when logging in. Oracle has the USERENV context information and the SESSION_USER attribute. This attribute identifies the user name on the Windows machine which has connected.

Then you’ll need a table to map those user names to the role they are to be assigned along with a little bit of code.

|sam | 1 |
|todd | 2 |
|harvey |-1 |
|gill | 1 |

The -1 would indicate a user that previously had permissions but those have been revoked.

To do the mapping you need this bit of PL/SQL code

create or replace function authRole() return number(1)
s_user varchar2(20);
i_role_id number(1);
select upper(sys_context('USERENV', 'SESSION_USER')) into s_user from dual;
select role_id into i_role_id from Users where userid=s_user and role_id>0;

case i_role_id
when 1 then execute immediate 'set role ROLE1';
when 2 then execute immediate 'set role ROLE2';
end case;
return i_role_id;
when NO_DATA_FOUND then raise_application_error(-20011,'User unauthorized');
when others then raise_application_error(-20011,'Unknown Exception in authRole Function');

you could put this into a login trigger on the schema but you’ll want to remove the raise_application_error as they don’t come through when ODBC is connecting. (in my experience, anyway)

That’s the Oracle side, whew, now for the Access part.

I create a function which assigns the role – by calling the code above and then checking if there is access.

Function OraAssignRole()
dim rs as DAO.Recordset
dim oQry as querydef

'Initiate the login by running a passthrough query to connect to oracle
set oQry = CurrentDb.QueryDef!qryPassthrough
oQry.sql = "{call SetTheRole}"
oQry.ReturnsRecords = True
oQry.Execute dbFailOnError

'Made it here so the user is ok, get the role id for use in the app
glbl_role_id = rs(0)

Exit Function
MsgBox "Error on login, You may not have rights. Application will close now."
End Function

And that’s the end of it. Place a call to this in the autoexec macro and your application will login to Oracle, set the role, and if the user is not found – close down the application.

I hope you found this post useful.