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  |
 |
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 #562021 is a reply to message #562018] |
Fri, 27 July 2012 01:41  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #562037 is a reply to message #562036] |
Fri, 27 July 2012 04:58 |
 |
Littlefoot
Messages: 21823
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 #562516 is a reply to message #562061] |
Wed, 01 August 2012 16:42 |
 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Dec 22 01:34:23 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
