Categories
Access begging hire me inbound marketing MS Access unemployed work

A begging letter

The web is a massive thing. You’ll find all sorts on here, good and bad, in relative measure of course. There are those what would consider blog on Access bad.

I’ve heard about inbound marketing. What it is exactly I am not sure. It sure does fit for a introverted person like myself, I can even speak with my parents for all that long!

But does it not all sound like inbound marketing is a new way of writing a work begging letter. It is a way to attract people who are looking for someone to find you. I thought if I blog on Access topics people will find me. So far it hasn’t worked out that way. Maybe they are coming here – though from stats they aren’t – and taking the knowledge.

Any inbound marketing tips and sites are welcme. I’ve used up my book budget this year so won’t be able to buy any of those.

Categories
hotfix MS Access performance windows 7

New hotfix solves Access 2010 performance issue

Thanks to Tony Toews and his blog for bringing this to our attention.

Access 2010 hotfix package (Ace-x-none.msp): August 30, 2011  which can download from here by request.

This solves the Access 2010 nd Windows 7 performance issue.

As with all hotfixes, only apply if you really has having an issue described and you can’t live with it until the next Service Pack is released.

Categories
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.



+------------+---------+
|USERID | ROLE_ID |
+------------+---------+
|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)
as
s_user varchar2(20);
i_role_id number(1);
begin
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;
exception
when NO_DATA_FOUND then raise_application_error(-20011,'User unauthorized');
when others then raise_application_error(-20011,'Unknown Exception in authRole Function');
end;

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()
On Error GoTo ERR_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
oQry.Close

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

Exit Function
ERR_ORAASSIGNROLE:
MsgBox "Error on login, You may not have rights. Application will close now."
Application.Quit
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.