CODEWITHSUNDEEP
vb.netoracle-database
Thomas Carlton
Thomas Carlton

Reputation: 5968

Select only one role for user when logging to Oracle

I have an Oracle database with two roles assigned to the same user.

The user will connect from .Net application.

Since each role has its own privileges I would like, when using my application, only one role to be considered. So the user will choose the role to be considered when logging to the application. And the privileges related to the other role won't be "active".

For example

Role 1 can access Table A
Role 2 can access Table B

So theoretically the user can access A & B

When using my application :

Choose either 1 or 2

if 1, show only A table
if 2, show only B table

Is that possible?

Upvotes: 0

Views: 129

Answers (1)

DCookie
DCookie

Reputation: 43533

Use of ROLES is one way:

CREATE ROLE roleA;
CREATE ROLE roleB;
GRANT SELECT ON tableA to roleA;
GRANT SELECT ON tableB to roleB;
GRANT roleA to TheUser;
GRANT roleB to TheUser;

When the user chooses a role in your application, issue the appropriate SET ROLE command:

User chooses 1:

SET ROLE roleA;

Otherwise, 

SET ROLE roleB;

Since the roles are not defaults, they will only be active via the SET ROLE command. You can password protect the roles, too.

Upvotes: 1

Related Questions