RE: Security for Table/Procedure

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 05 Feb 2002 15:30:39 -0800
Message-ID: <F001.00406BC9.20020205150019@fatcity.com>

-----Original Message-----
From: Burton, Laura L. [mailto:BurtonL_at_prismplus.com]

I know that you can grant access on a table whether it be select, update, delete, etc, and I know that to keep from granting access to a table you can use procedures and grant execute rights to the procedure. 

Our situation is that we want to use procedures for security, but the procedures select data to populate a form and then update if applicable.  We had talked about granting select to the tables by using a role since anyone can view the data, and then using procedures to update the tables.  The only problem is that if we grant select to the tables and the application executes the procedure within it, the procedure will not be found and even the select will not happen.

Could some of you share how you handle security?  Am I missing something with tables/procedures?  Any ideas would be appreciated.  We are just beginning development and need to have a handle on how we are going to do this.

---

As other posters have mentioned - the "procedure not being found" might be explained by the fact that the application must refer to the procedure by user_name.procedure_name unless the Oracle user running the application has access to a public/private synonym pointing to that procedure.

For security on tables/procedures - I have used the scheme mentioned by Michele (marmstrong_at_fairpoint.com) in the past - the application sets a password-enabled role upon startup, so the users can perform inserts/updates on tables via privileges granted by the role. However if the same user signed on in SQL*Plus, they would not be able to set the same role (because they wouldn't know the password) and so would be restricted to SELECT.

Another option might be to write a database trigger after logon to grant a role depending on which application is being run (check v$session.program to see which application is being run by the user)

I'm sure you know about the use of views to restrict access "vertically" (excluding certain columns) or "horizontally" (excluding certain rows.)

Finally, instead of using roles, you could also implement "security policies". Simple example: Users that are not SYSTEM cannot select mugs with an ID >= 100 Users that are not SYSTEM cannot select, insert, update, delete any china pattern EXCEPT "Kent Gardens"

create table &&tbl_owner..my_mug_collection
  (mug_id number,
   mug_shape varchar2 (10),
   mug_capacity number
  )
  tablespace &&tbl_tbsp ;

create table &&tbl_owner..my_china_collection
  (plate_id number,
   pattern varchar2 (10),
   plate_diameter number
  )
  tablespace &&tbl_tbsp ;

create function &&func_owner..collectible_policy (owner varchar2, object_name varchar2)
 return varchar2

is
   v_policy varchar2 (2000) ;

begin
   if owner = '&&tbl_owner' and object_name = 'MY_MUG_COLLECTION'
   then
      if sys_context ('userenv', 'session_user') != 'SYSTEM'
      then
         v_policy := 'mug_id < 100' ;
      end if ;
   end if ;
   if owner = '&&tbl_owner' and object_name = 'MY_CHINA_COLLECTION'
   then
      if sys_context ('userenv', 'session_user') != 'SYSTEM'
      then
         v_policy := 'pattern = ''Kent Gardens''' ;
      end if ;
   end if ;
   return v_policy ;

end collectible_policy ;
/

exec dbms_rls.add_policy (object_schema => '&&tbl_owner', -


                          object_name => 'MY_MUG_COLLECTION', -

                          policy_name => 'MUG_POLICY', -

                          function_schema => '&&func_owner', -

                          policy_function => 'COLLECTIBLE_POLICY', -

                          statement_types => 'SELECT')



exec dbms_rls.add_policy (object_schema => '&&tbl_owner', -


                          object_name => 'MY_CHINA_COLLECTION', -

                          policy_name => 'CHINA_POLICY', -

                          function_schema => '&&func_owner', -

                          policy_function => 'COLLECTIBLE_POLICY', -

                          statement_types => 'SELECT, INSERT, UPDATE, DELETE', -

                          update_check => true, -

                          enable => false)

Received on Tue Feb 05 2002 - 17:30:39 CST