How to prevent users from selecting other schemas' tables?

[volcano]

Hi, I have created a new database user. I found that the account can select data of other schemas' tables. So how can I restrict the new account to only manipulate its owned tables ? Thanks

 

[SantaMufasa]

Volcano,

Be default, users cannot access objects of other schemas. If you created a user that can select data from other schemae tables, then they can do so by virtue of other grants that your DBA should probably revoke or limit. Amongst the privileges (or role memberships) that could cause the results you are seeing is if your DBA has done (but not limited to) any of the following GRANTs:

GRANT SELECT ANY TABLE TO PUBLIC;
GRANT IMP_FULL_DATABASE TO PUBLIC;
GRANT EXP_FULL_DATABASE TO PUBLIC;

Even if your DBA did not perform any of these grants to "PUBLIC", the same effect exists if they did any of these grants to a role to which your new user is a member.

Amongst the methods by which you can isolate the "over-granted" privilege, you can connect as the new user and issue the following SELECTs that will list that users' privileges that could account for the unwanted privilege:

select privilege from user_sys_privs;
select granted_role from user_role_privs;
select privilege, owner||'.'||table_name from user_tab_privs;

I believe that the above queries will disclose privileges that you probably do not want that user to have. You can remove unwanted privileges by causing a user with administrative rights to the privilege or role to issue the command:

REVOKE <privilege or role> FROM <user_name>;

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 09:05 (22Nov04) UTC (aka "GMT" and "Zulu"),
@ 02:05 (22Nov04) Mountain Time

 

[volcano]

Hi Mufasa, thanks for your explanation. I have tried your queries and get the following results. But I am still able to select data of USER2 tables from USER1 account. Under what conditions could I retrieve both schemas' tables without explicitly granting any rights to USER1? Thanks


SQL> select privilege from user_sys_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE

SQL> select granted_role from user_role_privs;
no rows selected

SQL> select privilege, owner||'.'||table_name from user_tab_privs;
no rows selected

 

[SantaMufasa]

Volcano,

In my earlier post when I mentioned that the access to someone else's tables could come via permissions that exist for "PUBLIC", I failed to give you the queries to check to see what privileges exist for "PUBLIC". Following are queries that are counterparts for the ones I gave you earlier. (Note: Unless you have access to an Oracle login that has DBA privileges, you may need to have your DBA run the queries that access the "DBA_..." views, and have her/him disclose the results to you.):

select privilege, table_schema||'.'||table_name from all_tab_privs
where grantee = 'PUBLIC'
and table_schema not in ('SYS','SYSTEM');

select privilege from dba_sys_privs where grantee = 'PUBLIC';

select granted_role from dba_role_privs where grantee = 'PUBLIC';

Please post the results of these queries.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 16:12 (22Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:12 (22Nov04) Mountain Time

 

[volcano]

Hi Mufasa, I have tried your new queries. The results are shown below.

select privilege from dba_sys_privs where grantee = 'PUBLIC';
no rows selected

select granted_role from dba_role_privs where grantee = 'PUBLIC';
no rows selected

Your first query returned a lot of rows. Among them I found there are many entries of the schema's tables that I don't want them to be queried by my newly created account. Is there any way to revoke all privileges on the schema from that new account instead of revoking on tables one by one?
Thanks.

 

[SantaMufasa]

Volcano,

Here is the deal: If there is ever a business case for even one user not to have access to a table, then that table should not have privileges to it granted to 'PUBLIC'. Instead, the privileges should be granted to a ROLE and the ROLE shoulde be granted to individual USERS.

We need to set that structure in place for you now. To make that happen, you need to 1) revoke privileges from PUBLIC to access the schemas tables, 2) create a ROLE that represents the privileges to access that schema's tables, 3) grant the access privileges on the subject schema's table to the ROLE, the 4) grant the ROLE to appropriate users that should be accessing the tables.

Your first task is to create an appropriate role to which you GRANT privileges on the subject user's objects and to which you will eventually grant membership for various users:

CREATE ROLE <role_name>;

So that you won't have to do individual privilege REVOKEs from PUBLIC and individual privilege GRANTs to the new ROLE, here is a script to facilitate those activities. Since the script, below, prompts for input, you should save the code to a script, then invoke the script from your SQL*Plus prompt. (There is no harm in running the script to test it since it will not actually take effect until you decide to run the resulting generated/SPOOLed script.):

set echo off
set pagesize 0
set feedback off
set trimspool on
set verify off
accept role_name prompt "Enter the ROLE to replace PUBLIC's granted privs from this schema: "
spool temp.sql
select 'revoke '||privilege||' on '||table_name||' from PUBLIC;'
from USER_TAB_PRIVS_MADE
where grantee = 'PUBLIC';
select 'grant '||privilege||' on '||table_name||' to &role_name;'
from USER_TAB_PRIVS_MADE
where grantee = 'PUBLIC';
spool off
prompt
prompt Remember to GRANT &role_name membership to appropriate Oracle users.
prompt 
prompt Wrote REVOKE and GRANT commands to 'temp.sql'. To execute, 'SQL> @temp'.
prompt

Once you run the script, above, and it resulting 'test.sql' script, you will want to "GRANT <new_role_name> TO ..." only those that should has access to the subject tables.

Let us know your findings and satisfaction/applicability with the above.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 02:17 (23Nov04) UTC (aka "GMT" and "Zulu"),
@ 19:17 (22Nov04) Mountain Time

 

[volcano]

Hi Mufasa, it seems that your script will revoke all privileges from PUBLIC. In my case there are many different project teams sharing the DB. So there may be a risk to revoke all other privileges although your approach is appropriate. Now I change a bit your script as shown below. The script should just revoke all privileges of the schema concerned from PUBLIC.

select 'revoke '||privilege||' on '||table_name||' from PUBLIC;'
from ALL_TAB_PRIVS
where grantee = 'PUBLIC'
and table_name in ('TABLE1', 'TABLE2'......);

P.S. I found that ALL_TAB_PRIVS contains my wanted table names but USER_TAB_PRIVS_MADE does not.

 

[SantaMufasa]

The script should just revoke all privileges of the schema concerned from PUBLIC.

I had hoped that you picked up on the idea that you should connect to "the schema concerned" and run the script. That is why I accessed USER_TAB_PRIVS_MADE...to limit the revokes to the schema concerned. My expectation was that you know your schemas and privileges better than I and you would tailor either the script or the resulting "temp.sql" script to do precisely what you wanted if my suggestion wasn't quite what you needed.

So, cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 06:50 (23Nov04) UTC (aka "GMT" and "Zulu"),
@ 23:50 (22Nov04) Mountain Time

 

[volcano]

Hi Mufasa, I know your idea now. Thanks! Your queries are correct when I logged in to the "schema concerned" (I connected as sysdba to try your queries). Thanks for your expertise and patience to an experience-less DBA, haha

Cheers