Home » RDBMS Server » Security » Ensuring read only access to any table
Ensuring read only access to any table [message #562018] Fri, 27 July 2012 01:24 Go to next message
swanand.rajure
Messages: 3
Registered: July 2012
Junior Member
Hi All,

I am an auditor. I need to ensure that a perticular user at any cost should not be able to update contents of a table.

Privileges granted:

GRANT SELECT ANY TABLE TO "ABC"
GRANT ALTER SESSION TO "ABC"
GRANT CREATE SESSION TO "ABC"

Can abc modify, alter contents of a table?
Re: Ensuring read only access to any table [message #562019 is a reply to message #562018] Fri, 27 July 2012 01:27 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Connect as ABC and try it.
Re: Ensuring read only access to any table [message #562021 is a reply to message #562018] Fri, 27 July 2012 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can abc modify, alter contents of a table?


Do you think the privileges granted allow to modify the table?
Explain what each privilege allows it and you have your answer.
What does SELECT ANY TABLE mean?
What does ALTER SESSION mean?
What does CREATE SESSION TABLE mean?

By the way, this is a bad example, "SELECT ANY TABLE" should not be granted to any user and "ALTER SESSION" should be granted with care.

Regards
Michel

[Updated on: Fri, 27 July 2012 01:42]

Report message to a moderator

Re: Ensuring read only access to any table [message #562036 is a reply to message #562021] Fri, 27 July 2012 04:52 Go to previous messageGo to next message
swanand.rajure
Messages: 3
Registered: July 2012
Junior Member
Mike,

I am a SOX auditor, not a DBA. Hence I need help who understands these concepts.
By the way ABC is a system user.


[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Fri, 27 July 2012 05:00] by Moderator

Report message to a moderator

Re: Ensuring read only access to any table [message #562037 is a reply to message #562036] Fri, 27 July 2012 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does "ABC is a system user" mean?

P.S. Here's a simple example; see if you can do that as well (i.e. I granted only tiny privileges).

1. Create user and let it connect to the database
SQL> create user abc identified by abc;

User created.

SQL> grant create session to abc;

Grant succeeded.

2. Connect as the table owner (in other words, I'm avoiding SELECT ANY TABLE and granting SELECT on the specific table owned by that specific user (Scott)):
SQL> connect scott/tiger@ora11
Connected.
SQL> grant select on dept to abc;

Grant succeeded.

3. Connect as the ABC user and see whether it can a) SELECT, b) UPDATE data owned by Scott:
SQL> connect abc/abc@ora11
Connected.
SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update scott.dept set deptno = 99;
update scott.dept set deptno = 99
             *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

Seems to be OK, right?

[Updated on: Fri, 27 July 2012 05:05]

Report message to a moderator

Re: Ensuring read only access to any table [message #562061 is a reply to message #562036] Fri, 27 July 2012 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am a SOX auditor, not a DBA.


Sorry, but if you don't know the concepts and basics of security in the database you can't be a SOX auditor.
A SOX auditor must be an expert in security in all domains (database, network, OS...)... or it must be able to launch a tool that verifies the SOX rules for him.

Regards
Michel
Re: Ensuring read only access to any table [message #562516 is a reply to message #562061] Wed, 01 August 2012 16:42 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Swanand,

Since you are an auditor, you will be interested in the following script.
I use it to see all the grants that have been created in a database.
I use it to find a missing privilege (by running it in a database that
works and in a database that does not work and comparing the results)
but you will use it for auditing.

ENALFT > @cr8_all_existing_grants.sql
-- Roles Grantes to Users, sorted by role
grant CONNECT to ALFRESCO;
grant CONNECT to IPSOFT;
grant DBA to ENALFT;
grant DBA to IPSOFT;
grant SELECT_CATALOG_ROLE to IPSOFT;
-- Roles Granted to Users, sorted by user
grant CONNECT to ALFRESCO;
grant DBA to ENALFT;
grant CONNECT to IPSOFT;
grant DBA to IPSOFT;
grant SELECT_CATALOG_ROLE to IPSOFT;
-- Table Grants:
alter session set current_schema = ALFRESCO;
grant ALTER on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant DEBUG on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant DELETE on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant FLASHBACK on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant INDEX on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant INSERT on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant ON COMMIT REFRESH on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant QUERY REWRITE on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant REFERENCES on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant SELECT on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;
grant UPDATE on ALFRESCO.ALF_NODE_ASPECTS to ENALFT ;

The full sql follows.
set pages 0
set lines 120
set wrap off
set echo off
 rem Gets all grants for all users
 set verify off
 set pagesize 0
 set feedback off
column owner noprint
column srt noprint
spool cr8_all_existing_grants.lst

select '-- Roles Grantes to Users, sorted by role' from dual;
select 'grant '||granted_role||' to '||grantee||';' from dba_role_privs
where grantee  
not in ('SYSTEM','SYS','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','XDB',
'EXFSYS','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','MDSYS',
'CTXSYS','RMAN','SMODBUSER','ENWEBP2','ENWEBP1','ENWEBP3','ORACLE_OCM',
'YSUN','CSMIG','FLOWS_FILES','APEX_030200','OWBSYS_AUDIT','ANONYMOUS',
'OWBSYS','SYSMAN','APEX_PUBLIC_USER','SCOTT','APPQOSSYS','DMSYS','MDDATA',
'MGMT_VIEW','OLAPSYS','ORDDATA','SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR','XS$NULL',
'EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','EXP_FULL_DATABASE',
'HS_ADMIN_ROLE','HS_ADMIN_ROLE','IMP_FULL_DATABASE',
'IMP_FULL_DATABASE','DBA',
'DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE',
'SELECT_CATALOG_ROLE','DATAPUMP_IMP_FULL_DATABASE',
'JAVASYSPRIV','LOGSTDBY_ADMINISTRATOR','OEM_MONITOR')
order by granted_role,grantee;

select '-- Roles Granted to Users, sorted by user' from dual;
select 'grant '||granted_role||' to '||grantee||';' from dba_role_privs
where grantee
not in ('SYSTEM','SYS','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','XDB',
'EXFSYS','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','MDSYS',
'CTXSYS','RMAN','SMODBUSER','ENWEBP2','ENWEBP1','ENWEBP3','ORACLE_OCM',
'YSUN','CSMIG','FLOWS_FILES','APEX_030200','OWBSYS_AUDIT','ANONYMOUS',
'OWBSYS','SYSMAN','APEX_PUBLIC_USER','SCOTT','APPQOSSYS','DMSYS','MDDATA',
'MGMT_VIEW','OLAPSYS','ORDDATA','SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR','XS$NULL',
'EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','EXP_FULL_DATABASE',
'HS_ADMIN_ROLE','HS_ADMIN_ROLE','IMP_FULL_DATABASE',
'IMP_FULL_DATABASE','DBA',
'DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE',
'SELECT_CATALOG_ROLE','DATAPUMP_IMP_FULL_DATABASE',
'JAVASYSPRIV','LOGSTDBY_ADMINISTRATOR','OEM_MONITOR')
order by grantee,granted_role;

select '-- Table Grants:' from dual;
select owner,'0' srt,'alter session set current_schema = '||upper(owner)||';' grnt 
from (select distinct(owner) owner from sys.dba_tab_privs 
where owner not like '%SYS%' and owner not like '%sys%' and owner not like 'ordplugins'
and owner not in ('APEX_030200','DBSNMP','FLOWS_FILES','ORDDATA',
'ORDPLUGINS','OUTLN','XDB'))
union
 select owner,'1' srt,
        'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||
         decode(grantable,'YES',' with grant option',NULL)||' ;' grnt
 from   sys.dba_tab_privs
where owner not in ('SYSTEM','SYS','OUTLN','DIP','TSMSYS','DBSNMP','WMSYS','XDB',
'EXFSYS','ORDPLUGINS','ORDSYS','SI_INFORMTN_SCHEMA','MDSYS',
'CTXSYS','RMAN','SMODBUSER','ENWEBP2','ENWEBP1','ENWEBP3','ORACLE_OCM',
'YSUN','CSMIG','FLOWS_FILES','APEX_030200','OWBSYS_AUDIT','ANONYMOUS',
'OWBSYS','SYSMAN','APEX_PUBLIC_USER','SCOTT','APPQOSSYS','DMSYS','MDDATA',
'MGMT_VIEW','OLAPSYS','ORDDATA','SPATIAL_CSW_ADMIN_USR',
'SPATIAL_WFS_ADMIN_USR','XS$NULL')
and  not  (owner in ('CTXSYS') and grantee in ('PUBLIC') )
and length(owner)>2
 order by owner,srt,grnt ;
spool off
set pages 50
set feedback on
 undefine owner
 undefine srt 
 undefine grnt
Re: Ensuring read only access to any table [message #563477 is a reply to message #562516] Tue, 14 August 2012 03:35 Go to previous message
swanand.rajure
Messages: 3
Registered: July 2012
Junior Member
Thank you Alan for the script.

And thank you Mike for your unbiased reply. Wish you all the best.
Previous Topic: Schema Creation
Next Topic: Export Policies and Import to another database?
Goto Forum:
  


Current Time: Thu Mar 28 19:01:27 CDT 2024