Ahh...thanks Tom, I didn't know they were considered the same for these purposes, and that explains it.

I have oracle 8.1.7, in the future we will migrate to 9i or 10g.
I've tried to set role with dbms_session, but oracle say that I cant make a commit inside a trigger. Sometimes I have others problems.
Thanks
PD: I'm sorry for my english.

ORA-06565: cannot execute SET ROLE from within stored procedure

This is my store procedure

procedure enableRoles is
begin
dbms_session.set_role('ALL');
end;
I want to set only a subset of non-default roles granted to user, based on some rules. I want evalute those rules on logon trigger. But for now I have this errors

I really wanted an answer to the last question in the thread, but there isn't one.

I dissagree - there ARE circumstances where you want to enable roles based on certain rules. I am designing a security framework where the users are all enterprise users with certain info held on the LDAP and after logon I would like to enable one of a number of roles each allowing the user access to a different set of views on the same tables. Name resolution is via private synonyns and shema changes at logon. It works brilliantly, but the last piece of the jigsaw is to enable the appropriate role for the duration of the session - How to do it?

Thanks
Jay

“where it would be impossible to enforce some "rules" since you have no idea where you are yet”
Not true…

Here’s an example:
SQL> create table info(text varchar2(4000));

Table created.

SQL> grant all on info to public;

Grant succeeded.

SQL> create or replace trigger trig1
  2     after logon on database
  3  declare
  4  begin
  5    insert into a_user.info values ('Hi, I''m just sussing out some stuff..');
  6    insert into a_user.info values ('Enterprise user: '||substr(sys_context('userenv','external_name'),4,(instr(sys_context('userenv','external_name'),',')-5)));
  7    insert into a_user.info values ('Schema user: '||user);
  8    insert into a_user.info values ('User role: '||sys_context('SYS_LDAP_USER_DEFAULT','EMPLOYEETYPE'));
  8    insert into a_user.info values ('User location: '||sys_context('SYS_LDAP_USER_DEFAULT','REGISTEREDADDRESS'));
  9    insert into a_user.info values ('Now I know LOTS and want to enable an appropriate role for this session!...');
 10    -- enable role here perhaps
 11  end;
 12  /

Trigger created.

SQL> conn user01/user01@mydb
Connected.
SQL> select * from a_user.info;

TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hi, I'm just sussing out some stuff..
Enterprise user: user0
Schema user: DATA_ACCESS
User role: MANAGER
User location: HQ
Now I know LOTS and want to enable an appropriate role for this session!...

5 rows selected.

SQL>

However, I can enable the secure application role, because I get:
SQL> conn user01/user01@mydb
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot SET ROLE in a trigger
ORA-06512: at "SYS.DBMS_SESSION", line 124
ORA-06512: at "REPUBLICAN_GUARD.SENTRY", line 14
ORA-06512: at line 6

REPUBLICAN_GUARD.SENTRY is a package that has been created with invokers rights.

I’ve read all the results returned from a search of “secure application roles”, but no helpful stuff there, although I did happen upon this:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1934646094043

(the "and we said..." bit)

which saves me explaining in too much detail broadly speaking what I’m doing – it that approach, but using enterprise users, so we have a single schema to connect to (create user globalUser identified globablly as ‘’). I don’t want to use enterprise roles for maintenance reasons (and because my system regenerates everything automatically if things change – enterprise users are held outside the database and therefore not helpful here)

So, how can I do it?

Thanks in advance for your neat solution!
Jay
 

But I'm not sure why you can't just say that Tom, nor do I understand why Oracle won't let you, but I'm sure there's a good reason. The original question was regarding setting the role inside a logon trigger.

Also - you CAN use logon triggers with connection pooling - proxy authentication and OracleOCIConnectionPool (multiplexing sessions over physical connections). David Knox has a good chapter on this in his book "Effective Oracle Database 10g Security by Design"

I've solved my issue by adding a predicate to the view definitions that either equates to 1=1 or 1=0 depending on session context values, so you still have access to the view, you just can't see any data.

Thanks for your efforts on this Tom.
Cheers
Jay

Tom

You say: "You CAN use login triggers, but they are useless in MOST ALL CASES."

What worries me is that a logon trigger is the only solution that seems to satisfy my needs (I need to perform some session init tasks regardless of what application connects to my database, so the database is the obvious place to put this functionality), but you think login triggers are a bad idea, why? Is there a reason I should not be using them? How else can I change the schema on login so that I am using the appropriate set of synonyms?

If you've lost interest in this, I'd understand! I'll carry on using the login trigger which is now working quite happily. Incidentally - it's quite a neat solution to a fairly complex security problem, and might make quite a good article for the Oracle magazine... if you're interested.

Cheers
Jay

Dear Tom,

In my case the application is not supposed to be changed, it has to be the way it is written. Moreover we cannot create any further views in the database because for that we will also have to change the application to access the new views.

The only way left is to put some logic on the database. That might be when the application comes with the user x, database should perform some tests and then enable or disable the defined roles. This can be only done in LOGON trigger.

Please tell me how can we do that without using LOGON triggers and without touching the code of the application.

Thank you in advance for your time.

Dear Tom,

Thanks for a quick reply but, the problem is still there. I need to enable or disable a role when the user logs on.
The application has been built in Java and i will say it again that we are not ought to made any changes in application code.

Now the question is weather we can do it or not? I wonder if there is another solution instead of logon triggers, any trick, any workaround?

Let me tell you my problem:

I have a table "A".

Three users: X,Y,Z.

Another table which has a proprietry flag T or F for every user.

Now i want that when ever a user connects with the Database a check should be performed on the flags table if the user has a flag T he should have select access over table A. and if he has a flag F then he should not have an access over Table A.

Currently what i am doing is i made an application role identified using an invoker's rights procedure.
Then grant select on table A to that role.
Then Grant that role to all users and made it a non-default role for all of them.
Then i put a logon trigger which checks their proprietry flag when they log on to the DB. if the flag is T then set the role through the invoker's rights procedures. and if the flag is F for the user then leave it disabled.

It is not working, because we can not set roles in a logon trigger, but there must be any other way to do it. Now that you know all the scenerio, you are in a better position to help me finding a way.

Your help in this regard is highly appriciated!

Thank you.

Tom/Zahid,

you can make this happen without making any modification(s) to the application. i.e if you write a AFTER UPDATE trigger on the table which has the proprietry flag T or F for every user. And in this trigger call a stored procedure which does the required work i.e GRANT & REVOKE roles etc. But this stored procedure need to be defined as autonomous transaction.

Thanks
Omar

couple of problems with that.

the first obvious one - there is no update.  

... I need to enable or 
disable a role when the user logs on. ....


Not "I need to enable or disable a role when an update is done".  The T/F value is read, not updated.

the second one is, well, it wouldn't work even if there was an update:

ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t_trigger
  2  after update on t
  3  declare
  4          pragma autonomous_transaction;
  5  begin
  6          dbms_session.set_role('all');
  7          commit;
  8  end;
  9  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> set role connect;
 
Role set.
 
ops$tkyte@ORA9IR2> select * from session_roles;
 
ROLE
------------------------------
CONNECT
 
ops$tkyte@ORA9IR2> update t set x = 2;
 
1 row updated.
 
ops$tkyte@ORA9IR2> select * from session_roles;
 
ROLE
------------------------------
CONNECT
 
<b>I have not set role all..., if I had:</b>

ops$tkyte@ORA9IR2> exec dbms_session.set_role('all');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from session_roles;
 
ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
OLAP_DBA
 
14 rows selected.
 

Thank you very much Omar for such a great idea, and thanks to you too Tom for a brief explanation. But i took Omar's idea in another way, and took my head out of this enabling and disabling the role stuff.
What i do is i made another role, and grant select privilige on Table A to that role. Now i put the AFTER UPDATE trigger on the Proprietary Table and grant that role to the user if flag set to TRUE and revoke the role if flag set to F.
I on't know if i am gonna face any problem in it, but its working like anything. TWO THUMBS UP!!

Thank you every Body.
Zahid

Thank you very much Omar for such a great idea, and thanks to you too Tom for a brief explanation. But i took Omar's idea in another way, and took my head out of this enabling and disabling the role stuff.
What i do is i made another role, and grant select privilige on Table A to that role. Now i put the AFTER UPDATE trigger on the Proprietary Table and grant that role to the user if flag set to TRUE and revoke the role if flag set to F.
I do not know if i am going to face any problem in it, but its working like anything. TWO THUMBS UP!!

Thank you every Body.
Zahid

I am not sure, but if the idea was to protect the contents of some tables from some users, and to do that outside of the application, wouldn't this be a good use of vpd? Seems even more straightforward than any other idea here.

Hi Tom,

We are currently working on designing a security model for a university systems. The university has the following systems.
- HR
- Students Registration
- Inventory
- Library


Currently, they are using these systems which are developed using Oracle Forms and Reports. The end user logon to one form which displays a tree for the systems the end user has access to them. However, the security model is enforced on the application only, while at the database level the user has a full access to all tables on all systems. This is accomplished using one Role which (Select Any.....) system privileges.

Our task now is to design these legacy systems using Oracle JDeveloper to replace Oracle Forms and the reporting tool is BI Publisher as a replacement for Oracle Reports. Therefore, we decided - as first step - to redesign the security model using Oracle Best Practices. However, the following needed to be in mind when we need to design the security model for the systems mentioned above:
Users belong to groups, for example: deans, managers, secretary... etc
A group of users, say secretary, will have the same privileges on all systems mentioned above.
Some users within the same group may have more or less privileges. For example, a secretary might be granted a privilege for Order Entry system. This privilege is ONLY for this user, not the whole group. Also, the reverse is true, a privilege might be revoked from specific user, not from the whole group. For example, a secretary might not be allowed to use specific report while it is still allowed to the rest of the group.

Also, some users will have insert, update and delete on tables but with specific rules. For example, consider a table for leave permissions. The user in the secretary is allowed to insert a record as a leave permission as follows:

Emp_no leave_date_time return_date_time status
1111 01/05/2012 10:00 a.m. pending

Then, when he/she return back, the user is allowd to update the record to enter the return information

Emp_no leave_date_time return_date_time status
1111 01/05/2012 10:00 a.m. 01/05/2012 12:30 p.m. completed


Now, the manager of this employee, can only *update* this record after its status is completed to approve it. The manager will have an update privilege on this table for the records with *completed* status


Therefore, technically, we thought of the following:
- The system will have a schema called "sec_man", this schema will have tables for users and their groups and the privileges to these systems.
- Users are all database users, they all connect to the database through a proxy user. The proxy user establishes the connection pooling between the application server and the database.
- Business Logic is implemented in PLSQL Packages with invoker rights
- Security is enforced using Secured Application Roles
- Data Access is enforced using Oracle Virtual Private Database (VPD)

Roles here represents groups of users. Also, Roles are enforced from trusted package, they are secured.

VPD will handle the specific update, delete, insert and select privileges for users. The policy function will determine the right *WHERE* clause for each user.
However, I still do not know to how to handle the exceptions inside each group, I mean, how to grant a user an additional privilege that does not exist for the rest of the group as I have explained before.

Also, we are little worry that using Oracle Roles to implement the security model will not helpful due to the exception happened inside each group as explained above. Therefore, some of the team are thinking to grant the users the EXECUTE privilege for the PLSQL packages using owner rights, and inside each package, the developer will restrict what the user can do inside the PLSQL code within the package.

I am still fan of using Oracle Roles, Secured Application Roles along with VPD. On the other hand, some of my team worry that this model will be very costing and complicated in implementation.

I have reviewd the Security Guide Book
http://docs.oracle.com/cd/E11882_01/network.112/e16543/authorization.htm
especially chapter 4 and 5, but I still feel they did not give me the complete picture.

If you were working on such a project, how would you implement the security model?

Regards,
Emad

Hi Tom,
I have a state like the following, it is not the only one but this is to clarify the issue:
I have a table of the holiday that the regular employee would insert a record in it of his holiday

CREATE TABLE HOLIDAY
    (
      EMP_NO NUMBER(5,0),
   START_DATE DATE,
   END_DATE,
   MANAGER_NO NUMBER(5,0),
      STATE  VARCHAR2(1),
   CONSTRAINT "HOLIDAY_PK" PRIMARY KEY ("EMP_NO", "START_DATE", "END_DATE")
    )

The employee would insert only the values(emp_no, start_date and end_date).
The last two columns updated by the responsible of the employee showing if the holiday is approved or rejected.
The whole process is executed through a package named holiday_pkg as follows:

create or replace
PACKAGE HOLIDAY_PKG
AS
PROCEDURE Insert_Holiday(
    Emp_No_In Holiday.Emp_No%Type,
    Start_Date_In Holiday.Start_Date%Type,
    End_Date_In Holiday.End_Date%Type);
PROCEDURE approve_holiday(
    Emp_No_In Holiday.Emp_No%Type,
    Start_Date_In Holiday.Start_Date%Type,
    End_Date_In Holiday.End_Date%Type,
    manager_no_in holiday.manager_no%type);
END HOLIDAY_PKG;

create or replace
PACKAGE BODY HOLIDAY_PKG
AS
PROCEDURE Insert_Holiday(
    Emp_No_In Holiday.Emp_No%Type,
    Start_Date_In Holiday.Start_Date%Type,
    End_Date_In Holiday.End_Date%Type)
AS
BEGIN
  INSERT
    INTO Holiday(Emp_No, Start_Date, End_Date)
      VALUES(emp_no_in, start_date_in, end_date_in);
END Insert_Holiday;

PROCEDURE approve_holiday
  (
    Emp_No_In Holiday.Emp_No%Type,
    Start_Date_In Holiday.Start_Date%Type,
    End_Date_In Holiday.End_Date%Type,
    manager_no_in holiday.manager_no%type
  )
AS
BEGIN
  UPDATE Holiday SET Manager_No   = Manager_No_In, State = 'A'
    WHERE emp_no     = emp_no_in
      AND start_date = start_date_in
      AND end_date   = end_date_in;
END approve_holiday;
END HOLIDAY_PKG;

As I understand from Emad's review and your reply, I have to create a role X for the managers have execute on holiday_pkg, and a role Y for the employees have execute on holiday_pkg. The two roles have the same grant but I want the employees just to have the ability to execute insert_holiday procedure and not approve_holiday procedure.
I am a member of Emad's team and I suggest to add a call in the first line of every procedure within a package to call a function from the sec_man schema which returns boolean shows if the user able to execute this procedure or not, something like this:

PROCEDURE approve_holiday
  (
    Emp_No_In Holiday.Emp_No%Type,
    Start_Date_In Holiday.Start_Date%Type,
    End_Date_In Holiday.End_Date%Type,
    manager_no_in holiday.manager_no%type
  )
AS
BEGIN
  IF(!ALLOWED_TO_EXEC(package_name=>holiday_pkg, procedure_name=>approve_holiday)) then
 RAISE_APPLICATION_ERROR(-20001,'You are not allowed to executed this procedure');
  ELSE
 UPDATE Holiday SET Manager_No   = Manager_No_In, State = 'A'
  WHERE emp_no     = emp_no_in
   AND start_date = start_date_in
   AND end_date   = end_date_in;
  END IF;
END approve_holiday;
END HOLIDAY_PKG;

If there is another way to implement this, would you clarify us please?
Regards.
Mohammed Matar

Hello Chris/Connor,

Oracle Version is 19.28

Is there a way to revoke a secure application role after a pre-defined duration, without having to rely on the user calling the corresponding procedure explicitly? For e.g. consider below sequence of events
1. A user logs in to database and gets all default roles enabled except the secure application role
2. User calls the respective procedure and if the validation in the procedure is successful then the user's session gets the secure application role through a call to DBMS_SESSION.SET_ROLE
3. After a predefined time (say 10 minutes), the secure application role needs to be disabled IN THE SAME USER SESSION and WITHOUT relying on the user calling the corresponding procedure.

I am struggling about how to achieve step (3) above.
I am open to using any feature available within database.

Thanks in advance