5

Having used an Oracle DB for a while, I got used to using the Oracle DB Application Context to implement access restrictions. To simply put it, upon login a procedure is called that puts the user details into the Oracle Application context like so:

DBMS_SESSION.SET_CONTEXT('context_name', 'user_id', user_id);

Then access can be enforced by creating views that would look in the context to determine which rows can be seen by a user like so:

CREATE VIEW users_vw AS 
SELECT *
  FROM users
 WHERE user_id = SYS_CONTEXT('context_name', 'user_id');

I have now moved away from Oracle and am using PostgreSQL for personal projects and SQL Server 2000 and 2008 at work. Can anyone tell me if there are corresponding capabilities that PostgreSQL or SQL Server offer?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Sevas
  • 4,215
  • 3
  • 27
  • 26

3 Answers3

3

You can use Postgres' SET or SET LOCAL command to emulate Oracle's SET_CONTEXT(), e.g. as follows:

SET LOCAL audit.AUDIT_USER = 'whatever-you-like';
-- alternative:
SELECT set_config('audit.AUDIT_USER', 'whatever-you-like', true);

You can then retrieve this value using current_setting():

SELECT current_setting('audit.AUDIT_USER', true);

(The second argument, true, will avoid an exception if the parameter was not SET before querying and return NULL instead. The argument was introduced in Postgres 9.6, see this question for how to do it in Postgres 9.1+.)

I have also created a demo gist of an auditing mechanism leveraging SET LOCAL and current_setting() to implement DB-layer auditing with application-provided data.

Finally, some important details mentioned in the documentation, emphasis mine:

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not.

(In contrast, values set with SET are session-scoped.)

blubb
  • 9,510
  • 3
  • 40
  • 82
1

In PostgreSQL, you could perhaps get by with SECURITY DEFINER-functions that reason on the current user? Documented here: http://www.postgresql.org/docs/8.4/static/sql-createfunction.html

Edit:

plperl can be used for session variables. There are other alternatives (see link in comment), but plperl is the simplest of them.

Alex Brasetvik
  • 11,218
  • 2
  • 35
  • 36
  • Without knowing a lot of details about this, I assume it uses PostgreSQL users? In the method I described application users were separate from database users and connected to the DB using one common DB user. So the context was a way for the database to know which application user was connected. Thanks for the answer any way! – Sevas Dec 19 '09 at 21:05
  • Ah, so what you really want is session variables? http://stackoverflow.com/questions/414541/session-based-global-variable-in-postgresql-stored-procedure – Alex Brasetvik Dec 19 '09 at 21:12
0

If you'll use pl/perl (or pl/python if I recall correctly) you will get session variables for free.

Otherwise - when using plpgsql - you can use the GUCs to store your own settings, and use it in practically the same way.