PostgreSQL and/or SQL Server alternatives to Oracle's SYS_CONTEXT

Question

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?

blubb · Accepted Answer · 2018-01-04 22:05:31Z

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

Alex Brasetvik · Accepted Answer · 2009-12-19 21:15:47Z

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.

edited Dec 19, 2009 at 21:15

answered Dec 19, 2009 at 20:38

Alex Brasetvik

11.8k2 gold badges40 silver badges38 bronze badges

2 Comments

Sevas Over a year ago

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!

Alex Brasetvik Over a year ago

Ah, so what you really want is session variables? stackoverflow.com/questions/414541/…

user80168 · Accepted Answer · 2009-12-20 11:20:31Z

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.

answered Dec 20, 2009 at 11:20

user80168

Collectives™ on Stack Overflow

PostgreSQL and/or SQL Server alternatives to Oracle's SYS_CONTEXT

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related