How to use "/ as sysdba" during exp/imp
On Windows platform:
userid="'"/ as sysdba"'"
or
userid="""/ as sysdba"""
On Unix Platform:
userid=\'/ as sysdba\'
These tricks can be used with Oracle datapump (expdp/impdp) as well.
Reference : http://www.dbaglobe.com/2009/09/how-to-use-as-sysdba-during-expimp.html
Tuesday, August 2, 2011
Tuesday, September 14, 2010
Oracle : Upgrade to 10.2.0.5 hit ORA-00600: internal error code, arguments: [voprrfjcvm: Original Table Not F], [], [], [], [], [], [
Encounter the following after upgrading database to 10.2.0.5
ORA-00600: internal error code, arguments: [voprrfjcvm: Original Table Not F], [], [], [], [], [], [
After checking with Oracle Support found that we have hit a BUG :(
BUG#9951326 duplicate of BUG#6963442. This is fixed in version 11.2.0.1
Solution /workaround :
1. set undocumented parameter
alter session set "_fix_control"='6146906:off' scope=both;
OR
alter session set "_complex_view_merging"=false scope=both;
or apply interim patch 6963442
patch is available in version 10.2.0.5 and 11.1.0.7.0
Patch readme :
https://updates.oracle.com/Orion/Services/download?type=readme&aru=11691458
ORA-00600: internal error code, arguments: [voprrfjcvm: Original Table Not F], [], [], [], [], [], [
After checking with Oracle Support found that we have hit a BUG :(
BUG#9951326 duplicate of BUG#6963442. This is fixed in version 11.2.0.1
Solution /workaround :
1. set undocumented parameter
alter session set "_fix_control"='6146906:off' scope=both;
OR
alter session set "_complex_view_merging"=false scope=both;
or apply interim patch 6963442
patch is available in version 10.2.0.5 and 11.1.0.7.0
Patch readme :
https://updates.oracle.com/Orion/Services/download?type=readme&aru=11691458
Sunday, June 27, 2010
Oracle : Flash Recovery area - Space management Warning & Alerts
Subject:
Flash Recovery area - Space management Warning & Alerts
Doc ID:
Note:305812.1
Type:
BULLETIN
Last Revision Date:
13-JUN-2006
Status:
PUBLISHED
PURPOSE
-------
Flash Recovery area
1. How Oracle Manages Disk Space in the Flash Recovery Area
2. Space management Warning & Alerts
3. Impact of retention policy
4. How to resolve full Flash Recovery Area conditions.
SCOPE & APPLICATION
-------------------
Space management in the Flash Recovery Area for DBAs, support
1. How Oracle Manages Disk Space in the Flash Recovery Area
-----------------------------------------------------------
You can query the V$RECOVERY_FILE_DEST view to find out the current location,
disk quota, space in use, space reclaimable by deleting files, and total
number of files in the Flash Recovery Area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:\Oracle\flash_recovery_area 838860800 292490752 0 44
Oracle does not delete eligible files from the Flash Recovery Area until the
space must be reclaimed for some other purpose. The effect is that files
recently moved to tape are often still available on disk for use in recovery.
The recovery area can thus serve as a kind of cache for tape. Once the Flash
Recovery Area is full, Oracle automatically deletes eligible files to reclaim
space in the Flash Recovery Area as needed.
The following rules apply for files to become eligible for
deletion from the Flash Recovery Area:
- Files that are obsolete under the configured retention policy
- Transient files that have been copied to tape.
- In a Data Guard environment, archived redolog deletion policy governs when
archived redolog files can be deleted from the Flash Recovery Area.
- Permanent files are never eligible for deletion.
2. Flash Recovery area - Space management Warning & Alerts
----------------------------------------------------------
The database issues a warning alert when reclaimable space is less than 15%
and a critical alert when reclaimable space is less than 3%. To warn the DBA
of this condition, an entry is added to the alert log and to the
DBA_OUTSTANDING_ALERTS table (used by Enterprise Manager). However, the
database continues to consume space in the Flash Recovery Area until there is
no reclaimable space left. When the Flash Recovery Area is completely full, the
following error will be reported:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim bytes disk space from limit
where is the number of bytes required and is the disk quota for
the Flash Recovery Area.
The following Error would be reported in alert.log
ORA-19815: WARNING: db_recovery_file_dest_size of
bytes is 100.00% used, and has 0 remaining bytes available.
Issue the following query to see the message:
SQL> SELECT object_type, message_type, message_level,
reason, suggested_action
FROM dba_outstanding_alerts;
The following actions can be done to resolve the space issue :
- Add disk space to the Flash Recovery Area.
- Back up your files to a tertiary device.
- Delete the files from the Flash Recovery Area using RMAN.
- Changing RMAN retention policy.
The database handles a Flash Recovery Area with insufficient reclaimable space
just as it handles a disk full condition. Often, the result is a hang of the database.
3. Retention Policy / Obsolete Files in Flash Recovery Area
-----------------------------------------------------------
Correct use of a Flash Recovery Area, requires a retention policy. No retention
policy will cause files in the Flash Recovery Area never become obsolete, causing
major issues on the database.
The RMAN status OBSOLETE is always determined in reference to a retention
policy. For example, if a database backup is OBSOLETE in the RMAN repository,
it is because it is either not needed for recovery to a point within the
recovery window, or it is redundant.
If a Flash Recovery Area is configured, then the database uses an internal
algorithm to delete files from the Flash Recovery Area that are no longer
needed because they are redundant, orphaned, and so forth. The backups with
status OBSOLETE form a subset of the files deemed eligible for deletion by the
disk quota rules.
When space is required in the Flash Recovery Area, then the following files are
deleted:
a) Any backups which have become obsolete as per the retention policy.
b) Any files in the Flash Recovery Area which has been already backed up
to a tertiary device such as tape.
c) Flashback logs may be deleted from the Flash Recovery Area to make space available
for other required files.
A safe and reliable way to control deletion of files from the Flash Recovery
Area is to change the retention policy.
4. Resolving a Full Flash Recovery Area
---------------------------------------
There are a number of choices on how to resolve a full Flash Recovery Area when
there are NO files eligible for deletion:
- Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE
to reflect the new space.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE= ;
- Use the command BACKUP RECOVERY AREA, to back up the contents of the
Flash Recovery Area to a tertiary device such as tape.
Example:
RMAN> backup device type 'sbt_tape' recovery area;
- Delete unnecessary files from the Flash Recovery Area using the RMAN delete
command.
NOTE: If a host operating system command is used to delete files,
then the database will not be aware of the resulting free space. Run
the RMAN CROSSCHECK command to have RMAN re-check the contents of the
Flash Recovery Area and identify the deleted files. Afterwards run DELETE
EXPIRED to remove deleted files from the RMAN repository.
- You may also need to consider changing your backup retention policy
Example :
RMAN> configure retention policy to recovery window of 30 days;
- When using Data Guard, consider changing your archivelog deletion policy.
REFERENCE
Note 305817.1 FAQ - Flash Recovery Area feature of 10G
Reference : http://umardba.blogspot.com/2009/12/flash-recovery-area-space-management.html
Flash Recovery area - Space management Warning & Alerts
Doc ID:
Note:305812.1
Type:
BULLETIN
Last Revision Date:
13-JUN-2006
Status:
PUBLISHED
PURPOSE
-------
Flash Recovery area
1. How Oracle Manages Disk Space in the Flash Recovery Area
2. Space management Warning & Alerts
3. Impact of retention policy
4. How to resolve full Flash Recovery Area conditions.
SCOPE & APPLICATION
-------------------
Space management in the Flash Recovery Area for DBAs, support
1. How Oracle Manages Disk Space in the Flash Recovery Area
-----------------------------------------------------------
You can query the V$RECOVERY_FILE_DEST view to find out the current location,
disk quota, space in use, space reclaimable by deleting files, and total
number of files in the Flash Recovery Area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:\Oracle\flash_recovery_area 838860800 292490752 0 44
Oracle does not delete eligible files from the Flash Recovery Area until the
space must be reclaimed for some other purpose. The effect is that files
recently moved to tape are often still available on disk for use in recovery.
The recovery area can thus serve as a kind of cache for tape. Once the Flash
Recovery Area is full, Oracle automatically deletes eligible files to reclaim
space in the Flash Recovery Area as needed.
The following rules apply for files to become eligible for
deletion from the Flash Recovery Area:
- Files that are obsolete under the configured retention policy
- Transient files that have been copied to tape.
- In a Data Guard environment, archived redolog deletion policy governs when
archived redolog files can be deleted from the Flash Recovery Area.
- Permanent files are never eligible for deletion.
2. Flash Recovery area - Space management Warning & Alerts
----------------------------------------------------------
The database issues a warning alert when reclaimable space is less than 15%
and a critical alert when reclaimable space is less than 3%. To warn the DBA
of this condition, an entry is added to the alert log and to the
DBA_OUTSTANDING_ALERTS table (used by Enterprise Manager). However, the
database continues to consume space in the Flash Recovery Area until there is
no reclaimable space left. When the Flash Recovery Area is completely full, the
following error will be reported:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim bytes disk space from limit
where is the number of bytes required and is the disk quota for
the Flash Recovery Area.
The following Error would be reported in alert.log
ORA-19815: WARNING: db_recovery_file_dest_size of
bytes is 100.00% used, and has 0 remaining bytes available.
Issue the following query to see the message:
SQL> SELECT object_type, message_type, message_level,
reason, suggested_action
FROM dba_outstanding_alerts;
The following actions can be done to resolve the space issue :
- Add disk space to the Flash Recovery Area.
- Back up your files to a tertiary device.
- Delete the files from the Flash Recovery Area using RMAN.
- Changing RMAN retention policy.
The database handles a Flash Recovery Area with insufficient reclaimable space
just as it handles a disk full condition. Often, the result is a hang of the database.
3. Retention Policy / Obsolete Files in Flash Recovery Area
-----------------------------------------------------------
Correct use of a Flash Recovery Area, requires a retention policy. No retention
policy will cause files in the Flash Recovery Area never become obsolete, causing
major issues on the database.
The RMAN status OBSOLETE is always determined in reference to a retention
policy. For example, if a database backup is OBSOLETE in the RMAN repository,
it is because it is either not needed for recovery to a point within the
recovery window, or it is redundant.
If a Flash Recovery Area is configured, then the database uses an internal
algorithm to delete files from the Flash Recovery Area that are no longer
needed because they are redundant, orphaned, and so forth. The backups with
status OBSOLETE form a subset of the files deemed eligible for deletion by the
disk quota rules.
When space is required in the Flash Recovery Area, then the following files are
deleted:
a) Any backups which have become obsolete as per the retention policy.
b) Any files in the Flash Recovery Area which has been already backed up
to a tertiary device such as tape.
c) Flashback logs may be deleted from the Flash Recovery Area to make space available
for other required files.
A safe and reliable way to control deletion of files from the Flash Recovery
Area is to change the retention policy.
4. Resolving a Full Flash Recovery Area
---------------------------------------
There are a number of choices on how to resolve a full Flash Recovery Area when
there are NO files eligible for deletion:
- Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE
to reflect the new space.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE= ;
- Use the command BACKUP RECOVERY AREA, to back up the contents of the
Flash Recovery Area to a tertiary device such as tape.
Example:
RMAN> backup device type 'sbt_tape' recovery area;
- Delete unnecessary files from the Flash Recovery Area using the RMAN delete
command.
NOTE: If a host operating system command is used to delete files,
then the database will not be aware of the resulting free space. Run
the RMAN CROSSCHECK command to have RMAN re-check the contents of the
Flash Recovery Area and identify the deleted files. Afterwards run DELETE
EXPIRED to remove deleted files from the RMAN repository.
- You may also need to consider changing your backup retention policy
Example :
RMAN> configure retention policy to recovery window of 30 days;
- When using Data Guard, consider changing your archivelog deletion policy.
REFERENCE
Note 305817.1 FAQ - Flash Recovery Area feature of 10G
Reference : http://umardba.blogspot.com/2009/12/flash-recovery-area-space-management.html
Wednesday, June 16, 2010
Oracle : How does non DBA users access SYS.AUD$ and SYS.LINK$ tables
AUD$ and LINK$ has always been the favorite for auditor to pick on.
Prior to 9i, Oracle was very promiscuous with regards to the dollar tables in the data dictionary. It was very easy to get access. Quite frankly, you could have your way with the SYS schema, back in those days.
The SELECT ANY TABLE privilege was sufficient to access any dollar table in Oracle 8i. So if you gave someone this access to see any non-SYS table in the database, they also got to see the dollar tables.
In Oracle 9i, the SELECT ANY DICTIONARY privilege was introduced to give access to the dollar tables. The SELECT ANY TABLE privilege no longer gave you the dollar tables, unless you changed O7_DICTIONARY_ACCESSIBILITY to true ( which is usually advise against).
In order to find out how non-DBA database user can directly access to these tables some tests were conducted and conclusion as follow :
Conclusion :
With O7_DICTIONARY_ACCESSIBILITY=false
Granted : AUD$ LINK$
select any table privilege Not accessible Not accessible
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible
With O7_DICTIONARY_ACCESSIBILITY=true
Granted : AUD$ LINK$
select any table privilege Accessible Insufficient privileges
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible
However we do find out that if EXP_FULL_DATABASE role is granted to a user. The user can access the SYS.LINK$ table on special circumstance via Oracle Export utility and during logon from a third party software call PL/SQL Developer. But direct select on the table will still give
ORA-00942: table or view does not exist ( if select any table privilege not granted )
ORA-01031: insufficient privileges depending ( if select any table privilege not granted )
Audit all on LINK$ will still have an audit trail with returncode=0 for select on SYS.LINK$ if user is granted with EXP_FULL_DATABASE role and is doing a export or using PL/SQL Developer.
So in short O7_DICTIONARY_ACCESSIBILITY=false is recommended and select any dictionary is to be granted with care.
Test results :
With O7_DICTIONARY_ACCESSIBILITY=FALSE
TEST 1 : Grant select any tables
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 2 : Grant select_catalog_role
SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;
Revoke succeeded.
SQL> grant select_catalog_role to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 3 : Grant select any dictionary
SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;
Revoke succeeded.
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracle
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
With O7_DICTIONARY_ACCESSIBILITY=true
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL>
SQL> startup force
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2074048 bytes
Variable Size 285215296 bytes
Database Buffers 230686720 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> revoke select any dictionary from test;
Revoke succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 1 : Grant select any tables
SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracbk
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
SQL> select * from sys.link$
2 ;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
TEST 2 : Grant select_catalog_role
SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;
Revoke succeeded.
SQL> grant select_catalog_role to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 3 : Grant select any dictionary
SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;
Revoke succeeded.
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracbk
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
SPECIAL TEST : granted with RESOURCE, CONNECT, EXP_FULL_DATABASE, IMP_FULL_DATABASE
TEST 1a : Granted with SELECT ANY DICTIONARY
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-01031: insufficient privileges
TEST 1b : Not Granted with SELECT ANY DICTIONARY
SQL> revoke select any dictionary from test;
Revoke succeeded.
SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Prior to 9i, Oracle was very promiscuous with regards to the dollar tables in the data dictionary. It was very easy to get access. Quite frankly, you could have your way with the SYS schema, back in those days.
The SELECT ANY TABLE privilege was sufficient to access any dollar table in Oracle 8i. So if you gave someone this access to see any non-SYS table in the database, they also got to see the dollar tables.
In Oracle 9i, the SELECT ANY DICTIONARY privilege was introduced to give access to the dollar tables. The SELECT ANY TABLE privilege no longer gave you the dollar tables, unless you changed O7_DICTIONARY_ACCESSIBILITY to true ( which is usually advise against).
In order to find out how non-DBA database user can directly access to these tables some tests were conducted and conclusion as follow :
Conclusion :
With O7_DICTIONARY_ACCESSIBILITY=false
Granted : AUD$ LINK$
select any table privilege Not accessible Not accessible
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible
With O7_DICTIONARY_ACCESSIBILITY=true
Granted : AUD$ LINK$
select any table privilege Accessible Insufficient privileges
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible
However we do find out that if EXP_FULL_DATABASE role is granted to a user. The user can access the SYS.LINK$ table on special circumstance via Oracle Export utility and during logon from a third party software call PL/SQL Developer. But direct select on the table will still give
ORA-00942: table or view does not exist ( if select any table privilege not granted )
ORA-01031: insufficient privileges depending ( if select any table privilege not granted )
Audit all on LINK$ will still have an audit trail with returncode=0 for select on SYS.LINK$ if user is granted with EXP_FULL_DATABASE role and is doing a export or using PL/SQL Developer.
So in short O7_DICTIONARY_ACCESSIBILITY=false is recommended and select any dictionary is to be granted with care.
Test results :
With O7_DICTIONARY_ACCESSIBILITY=FALSE
TEST 1 : Grant select any tables
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 2 : Grant select_catalog_role
SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;
Revoke succeeded.
SQL> grant select_catalog_role to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 3 : Grant select any dictionary
SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;
Revoke succeeded.
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracle
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
With O7_DICTIONARY_ACCESSIBILITY=true
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL>
SQL> startup force
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 2074048 bytes
Variable Size 285215296 bytes
Database Buffers 230686720 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> revoke select any dictionary from test;
Revoke succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 1 : Grant select any tables
SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracbk
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
SQL> select * from sys.link$
2 ;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
TEST 2 : Grant select_catalog_role
SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;
Revoke succeeded.
SQL> grant select_catalog_role to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist
TEST 3 : Grant select any dictionary
SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;
Revoke succeeded.
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4
INSERT
oracbk
151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0
SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
SPECIAL TEST : granted with RESOURCE, CONNECT, EXP_FULL_DATABASE, IMP_FULL_DATABASE
TEST 1a : Granted with SELECT ANY DICTIONARY
SQL> grant select any dictionary to test;
Grant succeeded.
SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-01031: insufficient privileges
TEST 1b : Not Granted with SELECT ANY DICTIONARY
SQL> revoke select any dictionary from test;
Revoke succeeded.
SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Sunday, June 6, 2010
Oracle : Why dba_audit_trail shows more logoff than logon
Some of you guys might be puzzled why there are more logoff then logon from the dba_audit_trail.
That is because each logon is turned into a logoff upon logoff.
looking at the full record.
when you have a logon event, it looks like this:
-----------------
OS_USERNAME : "tkyte"
USERNAME : "OPS$TKYTE"
USERHOST : ""
TERMINAL : ""
TIMESTAMP : "28-jul-2005 09:10:33"
ACTION_NAME : "LOGON"
LOGOFF_TIME : ""
LOGOFF_LREAD : ""
LOGOFF_PREAD : ""
LOGOFF_LWRITE : ""
LOGOFF_DLOCK : ""
SESSIONID : "5942"
RETURNCODE : "0"
CLIENT_ID : ""
SESSION_CPU : ""
when that logs off -- it updates it to this:
OS_USERNAME : "tkyte"
USERNAME : "OPS$TKYTE"
USERHOST : ""
TERMINAL : ""
TIMESTAMP : "28-jul-2005 09:10:33"
ACTION_NAME : "LOGOFF"
LOGOFF_TIME : "28-jul-2005 09:12:10"
LOGOFF_LREAD : "2738"
LOGOFF_PREAD : "278"
LOGOFF_LWRITE : "18"
LOGOFF_DLOCK : "0"
SESSIONID : "5942"
RETURNCODE : "0"
CLIENT_ID : ""
SESSION_CPU : "26"
You have the logon logoff times tracked for you already.
Source : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439
That is because each logon is turned into a logoff upon logoff.
looking at the full record.
when you have a logon event, it looks like this:
-----------------
OS_USERNAME : "tkyte"
USERNAME : "OPS$TKYTE"
USERHOST : ""
TERMINAL : ""
TIMESTAMP : "28-jul-2005 09:10:33"
ACTION_NAME : "LOGON"
LOGOFF_TIME : ""
LOGOFF_LREAD : ""
LOGOFF_PREAD : ""
LOGOFF_LWRITE : ""
LOGOFF_DLOCK : ""
SESSIONID : "5942"
RETURNCODE : "0"
CLIENT_ID : ""
SESSION_CPU : ""
when that logs off -- it updates it to this:
OS_USERNAME : "tkyte"
USERNAME : "OPS$TKYTE"
USERHOST : ""
TERMINAL : ""
TIMESTAMP : "28-jul-2005 09:10:33"
ACTION_NAME : "LOGOFF"
LOGOFF_TIME : "28-jul-2005 09:12:10"
LOGOFF_LREAD : "2738"
LOGOFF_PREAD : "278"
LOGOFF_LWRITE : "18"
LOGOFF_DLOCK : "0"
SESSIONID : "5942"
RETURNCODE : "0"
CLIENT_ID : ""
SESSION_CPU : "26"
You have the logon logoff times tracked for you already.
Source : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439
Oracle : How to track logon & logout in Oracle.
Most company will have an audit requirement to track logon / logoff information.
The easiest way is to turn on audit and audit connect.
From the dba_audit_trail we can get the information using the following sql
* replace From_data and To_date with actual dates of the period that the information is required.
alter session SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
col OS_USERNAME format a25
col USERNAME format a20
col USERHOST format a20
col TERMINAL format a10
col TIMESTAMP format a20
col OWNER format a20
col OBJ_NAME format a20
col ACTION_NAME format a18
col COMMENT_TEXT format a150
select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, LOGOFF_TIME, ACTION_NAME, COMMENT_TEXT from dba_audit_trail where action_name in ('LOGOFF','LOGON','LOGOFF BY CLEANUP')
and TIMESTAMP between to_date('','DD-MM-YYYY HH24:MI:SS') and to_date('','DD-MM-YYYY HH24:MI:SS')
order by TIMESTAMP;
As an alternative to auditing logons/logoffs, you might as well create LOGON and LOGOFF triggers
that populate a regular history table. I myself prefer it. It's far simpler:
create table log_trail (name varchar2(30), time date, action varchar2(10));
create or replace trigger tr_logon
after logon on database
begin
insert into log_trail values (user, sysdate, 'LOGON');
commit;
end tr_logon;
create or replace trigger tr_logoff
before logoff on database
begin
insert into log_trail values (user, sysdate, 'LOGOFF');
commit;
end tr_logon;
Notes:
- Beware of logon triggers. If they are not working, you may not logon to Oracle.
- You must have the CREATE (ANY) TRIGGER and ADMINISTER DATABASE TRIGGER privileges to implement
DATABASE triggers.
Reference : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439
The easiest way is to turn on audit and audit connect.
From the dba_audit_trail we can get the information using the following sql
* replace From_data and To_date with actual dates of the period that the information is required.
alter session SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
col OS_USERNAME format a25
col USERNAME format a20
col USERHOST format a20
col TERMINAL format a10
col TIMESTAMP format a20
col OWNER format a20
col OBJ_NAME format a20
col ACTION_NAME format a18
col COMMENT_TEXT format a150
select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, LOGOFF_TIME, ACTION_NAME, COMMENT_TEXT from dba_audit_trail where action_name in ('LOGOFF','LOGON','LOGOFF BY CLEANUP')
and TIMESTAMP between to_date('
order by TIMESTAMP;
As an alternative to auditing logons/logoffs, you might as well create LOGON and LOGOFF triggers
that populate a regular history table. I myself prefer it. It's far simpler:
create table log_trail (name varchar2(30), time date, action varchar2(10));
create or replace trigger tr_logon
after logon on database
begin
insert into log_trail values (user, sysdate, 'LOGON');
commit;
end tr_logon;
create or replace trigger tr_logoff
before logoff on database
begin
insert into log_trail values (user, sysdate, 'LOGOFF');
commit;
end tr_logon;
Notes:
- Beware of logon triggers. If they are not working, you may not logon to Oracle.
- You must have the CREATE (ANY) TRIGGER and ADMINISTER DATABASE TRIGGER privileges to implement
DATABASE triggers.
Reference : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439
Tuesday, May 11, 2010
Oracle : ORA-28221: REPLACE not specified
You are trying to change the password of a user and getting this error.
The database is Oracle release 9.2 or higher and password complexity is enabled, either with an Oracle supplied function or with a custom function.
The REPLACE keyword was first introduced in Oracle 9.2. In previous versions of Oracle, any user could change their password using the ALTER USER ... IDENTIFIED BY ... statement.
Keep in mind that this includes ALL users regardless if they have the ALTER USER system privilege.
With Oracle 9.2 and higher, if you DO NOT have the system privilege ALTER USER and there is a PASSWORD_VERIFY_FUNCTION on a profile that is assigned to that user, then you MUST use the REPLACE keyword along with the old password.
If you have the ALTER USER system privilege, then you do not need to use REPLACE.
Example : Alter user XXX identified by replace ;
Source : http://www.dbmotive.com/oracle_error_codes.php?type=ORA&errcode=28221
The database is Oracle release 9.2 or higher and password complexity is enabled, either with an Oracle supplied function or with a custom function.
The REPLACE keyword was first introduced in Oracle 9.2. In previous versions of Oracle, any user could change their password using the ALTER USER ... IDENTIFIED BY ... statement.
Keep in mind that this includes ALL users regardless if they have the ALTER USER system privilege.
With Oracle 9.2 and higher, if you DO NOT have the system privilege ALTER USER and there is a PASSWORD_VERIFY_FUNCTION on a profile that is assigned to that user, then you MUST use the REPLACE keyword along with the old password.
If you have the ALTER USER system privilege, then you do not need to use REPLACE.
Example : Alter user XXX identified by
Source : http://www.dbmotive.com/oracle_error_codes.php?type=ORA&errcode=28221
Subscribe to:
Posts (Atom)