can an SQL function be used for a predicate when applying a policy to a table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: can an SQL function be used for a predicate when applying a policy to a table?

  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unhappy can an SQL function be used for a predicate when applying a policy to a table?

    The intention here is that the SQL function is executed based on some criteria every time a select statement is executed against the said table.

  2. #2
    Join Date
    Nov 2010
    Posts
    4
    This is an oracle label security related question.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Yes. Like when you want to determine if the current session has appropriate access rights to the targetted row/rows.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Nov 2010
    Posts
    4
    Thanks PAVB for your response.

    what I really want to happen is to pass a function for the predicate and I want the function to execute when the policy is enforced.

    my current situation is as follows

    1. if I pass the function in quotes(as illustrated below) for the predicate I get an SQL error code 28113(policy predicate has an error)

    SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
    policy_name => 'POLICY_NAME'
    ,schema_name => 'DB_USER'
    ,table_name => 'ols_test'
    ,table_options =>'READ_CONTROL,WRITE_CONTROL,CHECK_CONTROL'
    ,label_function =>NULL
    ,predicate =>'my_function(SA_SESSION.SA_USER_NAME(''POLICY_NAME''))'
    );
    end;

    if I execute the following query i get the SQL error code 28113
    SELECT * FROM ols_test;


    2. If I pass the function for the predicate without the quotes(as illustrated below). the function is executed when I apply the policy to the table. as a result the same predicate is used when enforcing the policy irrespective of the user logged on user.

    SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
    policy_name => 'POLICY_NAME'
    ,schema_name => 'DB_USER'
    ,table_name => 'ols_test'
    ,table_options =>'READ_CONTROL,WRITE_CONTROL,CHECK_CONTROL'
    ,label_function =>NULL
    ,predicate =>my_function(SA_SESSION.SA_USER_NAME(''POLICY_NAME''))
    );
    end;

    In principle what I want is to generate the predicate based on the user that is logged on.

    Regards
    Thobela

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Thobela View Post
    In principle what I want is to generate the predicate based on the user that is logged on.
    Have you considered dynamic sql?
    Just build the statement on the fly and execute it via "execute immediate".
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Nov 2010
    Posts
    4
    I solved this by using a VPD policy to generate the predicate on the fly. so I am using an OLS policy together with a VPD policy on the same table.

    what I had hoped for was using just an OLS policy and provide a SQL function for the predicate option/argument when applying the OLS policy to a table such that this function is called when the OLS policy is being enforced (meaning that the predicate would be generated on the fly based on user session).

    Pablo, thanks for you replies.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Thobela View Post
    I solved this by using a VPD policy to generate the predicate on the fly. so I am using an OLS policy together with a VPD policy on the same table.

    what I had hoped for was using just an OLS policy and provide a SQL function for the predicate option/argument when applying the OLS policy to a table such that this function is called when the OLS policy is being enforced (meaning that the predicate would be generated on the fly based on user session).

    Pablo, thanks for you replies.
    Nicely done, congrats!
    Glad to help.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width