Hi, all

Use DBMS_RLS package to resolve that problem (to add policy for select
statement for one table)

For example
USER2 wants to SELECT ANY TABLE except one table TAB1 in schema USER1

1. create policy function in schema USER1
create or replace function user1.tab1_policy_function
(p_schema in varchar2, p_object in varchar2)
return varchar2
as
begin
if (user = 'USER2') and (p_object = 'TAB1') then
raise_application_error(-20001, 'Permission denied');
else
return '';
end if;
end;

2. grant select any table to user2 and add security policy for table
USER1.TAB1 by SYS (or SYSTEM)

grant select any table to user2;

begin
dbms_rls.add_policy(object_schema => 'user1', object_name => 'tab1',
policy_name => 'tab1_policy', function_schema => 'user1',
policy_function => 'tab1_policy_function',
statement_types => 'select', update_check => true,
enable => true);
end;

3. When you execute that statement by user USER2 you get error

select * from user1.tab1;

ORA-28112: failed to execute policy function



From city Almaty, republic Kazakhstan

----- Original Message -----

From: "omar achandair "
<oracledba-ezmlmshield-x65698838.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, June 29, 2006 12:24 AM
Subject: Restrict Access Table


> Hi all,
> I want to grant a SELECT ANY TABLE except one table TAB1 ?
> Any suggestion plz.
>
>
> Omar ACHANDAIR
> DBA-DSI-AMENDIS
> GSM : 061 066 278
> mailto:omar.[Email address protected]
>
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>


Oracle LazyDBA home page