Timestamp: 16:59:19.794
Connect: yng@testdb
Session: yng@testdb
Timestamp: 16:59:20.303
SELECT version, product, sysdate FROM sys.PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE ‘%ORACLE%’
Session: yng@testdb
Timestamp: 16:59:20.327
Declare
debug_connect_session number;
alter_any_table number;
alter_session number;
alter_system number;
alter_database number;
alter_user number;
create_table number;
create_role number;
create_trigger number;
create_sequence number;
adm_db_trig number;
create_any_index number;
create_any_sequence number;
create_any_table number;
create_any_trigger number;
debug_any_procedure number;
execute_any_procedure number;
select_any_table number;
grant_any_object_priv number;
grant_any_role number;
grant_any_privilege number;
select_any_dictionary number;
update_any_table number;
create_public_syn number;
has_dba_synonyms number;
has_dba_objects number;
select_catalog_role number;
db_charset nls_database_parameters.value%type;
compatible_version varchar2(60);
sid number;
local_trans_id varchar2(60);
qry_str varchar2(4000);
has_dbms_java number;
has_dbms_debug number;
qu_version_owner varchar2(30);
CTRepositoryVersion number;
is_RAC number;
instance_id number;
instance_name varchar2(100);
server_name varchar2(100);
team_coding_schema varchar2(30);
team_coding_table varchar2(30);
team_coding_synonym varchar2(30);
team_coding_table_selectable number;
OS_USERNAME varchar2(60);
dbid varchar2(30);
authentication_type varchar2(30);
AmazonRDSPackage varchar2(30);
OraDBType number;
cx_table_count number;
Begin
– Put Toad data into v$session columns
Begin
SYS.DBMS_APPLICATION_INFO.SET_MODULE(:IN_MDL, null);
Exception
When others then null; – we can live without this.
End;
–
– Retrieve specific system privileges
select
sum(decode(Privilege, ‘ADMINISTER DATABASE TRIGGER’, 1, 0)) adm_db_trig,
sum(decode(Privilege, ‘ALTER ANY TABLE’, 1, 0)) alter_any_table,
sum(decode(Privilege, ‘ALTER DATABASE’, 1, 0)) alter_database,
sum(decode(Privilege, ‘ALTER SESSION’, 1, 0)) alter_session,
sum(decode(Privilege, ‘ALTER SYSTEM’, 1, 0)) alter_system,
sum(decode(Privilege, ‘ALTER USER’, 1, 0)) alter_user,
sum(decode(Privilege, ‘CREATE ANY INDEX’, 1, 0)) create_any_ind,
sum(decode(Privilege, ‘CREATE ANY SEQUENCE’, 1, 0)) create_any_seq,
sum(decode(Privilege, ‘CREATE ANY TABLE’, 1, 0)) create_any_table,
sum(decode(Privilege, ‘CREATE ANY TRIGGER’, 1, 0)) create_any_trig,
sum(decode(Privilege, ‘CREATE PUBLIC SYNONYM’, 1, 0)) create_public_syn,
sum(decode(Privilege, ‘CREATE ROLE’, 1, 0)) create_role,
sum(decode(Privilege, ‘CREATE SEQUENCE’, 1, 0)) create_sequence,
sum(decode(Privilege, ‘CREATE TABLE’, 1, 0)) create_table,
sum(decode(Privilege, ‘CREATE TRIGGER’, 1, 0)) create_trigger,
sum(decode(Privilege, ‘DEBUG ANY PROCEDURE’, 1, 0)) debug_any_proc,
sum(decode(Privilege, ‘DEBUG CONNECT SESSION’, 1, 0)) debug_connect_session,
sum(decode(Privilege, ‘EXECUTE ANY PROCEDURE’, 1, 0)) execute_any_procedure,
sum(decode(Privilege, ‘GRANT ANY OBJECT PRIVILEGE’, 1, 0)) grant_any_object_priv,
sum(decode(Privilege, ‘GRANT ANY ROLE’, 1, 0)) grant_any_role,
sum(decode(Privilege, ‘GRANT ANY PRIVILEGE’, 1, 0)) grant_any_privilege,
sum(decode(Privilege, ‘SELECT ANY DICTIONARY’, 1, 0)) select_any_dictionary,
sum(decode(Privilege, ‘SELECT ANY TABLE’, 1, 0)) select_any_table,
sum(decode(Privilege, ‘UPDATE ANY TABLE’, 1, 0)) update_any_table
into adm_db_trig, alter_any_table, alter_database, alter_session, alter_system, alter_user,
create_any_index, create_any_sequence, create_any_table, create_any_trigger, create_public_syn, create_role,
create_sequence, create_table, create_trigger, debug_any_procedure, debug_connect_session,
execute_any_procedure, grant_any_object_priv, grant_any_role, grant_any_privilege, select_any_dictionary, select_any_table, update_any_table
from sys.session_privs
where privilege in (‘DEBUG CONNECT SESSION’,
‘ALTER ANY TABLE’,
‘ALTER SESSION’,
‘ALTER SYSTEM’,
‘ALTER DATABASE’,
‘ALTER USER’,
‘ADMINISTER DATABASE TRIGGER’,
‘CREATE TABLE’,
‘CREATE ROLE’,
‘CREATE TRIGGER’,
‘CREATE SEQUENCE’,
‘EXECUTE ANY PROCEDURE’,
‘SELECT ANY TABLE’,
‘UPDATE ANY TABLE’,
‘GRANT ANY OBJECT PRIVILEGE’,
‘GRANT ANY ROLE’,
‘GRANT ANY PRIVILEGE’,
‘SELECT ANY DICTIONARY’,
‘CREATE ANY INDEX’,
‘CREATE ANY SEQUENCE’,
‘CREATE ANY TABLE’,
‘CREATE ANY TRIGGER’,
‘DEBUG ANY PROCEDURE’,
‘CREATE PUBLIC SYNONYM’);
–
– Check for SELECT_CATALOG_ROLE. It must be a default role to appear in the result of this query.
Select
sum(decode(role, ‘SELECT_CATALOG_ROLE’, 1, 0)) select_catalog_role
into select_catalog_role
from sys.session_roles
where role = ‘SELECT_CATALOG_ROLE’;
–
– determine database character set
select value into db_charset from sys.nls_database_parameters where parameter = ‘NLS_CHARACTERSET’;
–
– check for other ways dba_objects and dba_synonyms could be granted
if (select_any_dictionary = 1) or (select_catalog_role = 1) then
has_dba_synonyms := 1;
has_dba_objects := 1;
else
–
– just try to select from dba_synonyms and dba_objects as a privilege check
begin
qry_str := ‘select 1 from DBA_SYNONYMS where rownum = 1’;
execute immediate qry_str into has_dba_synonyms;
exception
when others then
has_dba_synonyms := 0;
end;
begin
qry_str := ‘select 1 from DBA_OBJECTS where rownum = 1’;
execute immediate qry_str into has_dba_objects;
exception
when others then
has_dba_objects := 0;
end;
end if;
–
– retrieve OS Username
begin
qry_str := ‘select SYS_CONTEXT(’‘USERENV’’,’‘OS_USER’’) from dual’;
execute immediate qry_str into OS_USERNAME;
exception
when others then
OS_USERNAME := null;
end;
–
– retrieve SID if possible
begin
qry_str := ‘select SYS_CONTEXT(’‘USERENV’’,’‘SID’’) from dual’;
execute immediate qry_str into sid;
exception
when others then
sid := -1;
end;
–
– get authentication type
begin
qry_str := ‘Select SYS_CONTEXT(’‘USERENV’’, ‘‘IDENTIFICATION_TYPE’’) from dual’;
execute immediate qry_str into authentication_type;
exception
when others then
authentication_type := null;
end;
–
– get compatible version
begin
qry_str := ‘select value from v$parameter where name = ‘‘compatible’’’;
execute immediate qry_str into compatible_version;
exception
when others then
compatible_version := null;
end;
–
– check for privileges on sys.dbms_transaction
begin
execute immediate ‘select sys.dbms_transaction.local_transaction_id from dual’ into local_trans_id;
exception
when others then
local_trans_id := ‘’;
end;
–
– determine if certain packages are installed
qry_str := ‘Select sum(decode(object_name, ‘‘DBMS_JAVA’’, 1, 0)) jv,’ ||
’ sum(decode(object_name, ‘‘DBMS_DEBUG’’, 1, 0)) dbg ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where owner = ‘‘SYS’’ ’ ||
'and object_type = ‘‘PACKAGE’’ ’ ||
‘and object_name in (’‘DBMS_JAVA’’, ‘‘DBMS_DEBUG’’)’;
execute immediate qry_str into has_dbms_java, has_dbms_debug;
–
– determine if code tester repository is installed
begin
Select user into qu_version_owner
FROM sys.user_objects
WHERE object_name = ‘QU_VERSION’
AND object_type in (‘PACKAGE’, ‘SYNONYM’)
AND rownum = 1;
exception
when no_data_found then
qu_version_owner := null;
end;
– if not found then look in other schemas
if qu_version_owner is null then
begin
qry_str := 'Select owner ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where object_name = ‘‘QU_VERSION’’ ’ ||
‘and object_type in (’‘PACKAGE’’,’‘SYNONYM’’) and rownum = 1’;
execute immediate qry_str into qu_version_owner;
exception
when others then
qu_version_owner := null;
end;
end if;
–
– if it is installed, determine code tester version
CTRepositoryVersion := 0;
if qu_version_owner is not null then
begin
qry_str := ‘select (10 * QU_VERSION.major_number) + QU_VERSION.minor_number from dual’;
execute immediate qry_str into CTRepositoryVersion;
exception
when others then
CTRepositoryVersion := 0;
end;
end if;
–
– find the team coding schema, if any
begin
– all_objects is generally faster than all_synonyms, so first look there to see if the synonym exists.
qry_str := 'Select object_name ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where owner = ‘‘PUBLIC’’ and object_name = ‘‘DELL_COM_TEAM_CODING’’ and object_type = ‘‘SYNONYM’’ ';
execute immediate qry_str into team_coding_synonym;
exception
when others then
team_coding_synonym := null;
end;
– now look to find team coding table name only if we know synonym exists.
if team_coding_synonym is not null then
begin
qry_str := 'Select table_owner, table_name ';
if has_dba_synonyms > 0 then
qry_str := qry_str || 'from sys.dba_synonyms ';
else
qry_str := qry_str || 'from sys.all_synonyms ';
end if;
qry_str := qry_str || 'where synonym_name = ‘‘DELL_COM_TEAM_CODING’’ and owner = ‘‘PUBLIC’’ ';
execute immediate qry_str into team_coding_schema, team_coding_table;
exception
when others then
team_coding_schema := null;
team_coding_table := null;
end;
end if;
– if we found a table, make sure we can select from it.
if team_coding_table is not null then
begin
qry_str := ‘SELECT 1 FROM ’ || team_coding_schema || ‘.’ || team_coding_table || ’ where rownum = 1’;
execute immediate qry_str into team_coding_table_selectable;
exception
when no_data_found then
team_coding_table_selectable := 1;
when others then
team_coding_table_selectable := 0;
end;
end if;
–
– find the team coding schema, if any
begin
– all_objects is generally faster than all_synonyms, so first look there to see if the synonym exists.
qry_str := 'Select object_name ';
if has_dba_objects > 0 then
qry_str := qry_str || 'from sys.dba_objects ';
else
qry_str := qry_str || 'from sys.all_objects ';
end if;
qry_str := qry_str || 'where owner = ‘‘PUBLIC’’ and object_name = ‘‘DELL_COM_TEAM_CODING’’ and object_type = ‘‘SYNONYM’’ ';
execute immediate qry_str into team_coding_synonym;
exception
when others then
team_coding_synonym := null;
end;
– now look to find team coding table name only if we know synonym exists.
if team_coding_synonym is not null then
begin
qry_str := 'Select table_owner, table_name ';
if has_dba_synonyms > 0 then
qry_str := qry_str || 'from sys.dba_synonyms ';
else
qry_str := qry_str || 'from sys.all_synonyms ';
end if;
qry_str := qry_str || 'where synonym_name = ‘‘DELL_COM_TEAM_CODING’’ and owner = ‘‘PUBLIC’’ ';
execute immediate qry_str into team_coding_schema, team_coding_table;
exception
when others then
team_coding_schema := null;
team_coding_table := null;
end;
end if;
– if we found a table, make sure we can select from it.
if team_coding_table is not null then
begin
qry_str := ‘SELECT 1 FROM ’ || team_coding_schema || ‘.’ || team_coding_table || ’ where rownum = 1’;
execute immediate qry_str into team_coding_table_selectable;
exception
when no_data_found then
team_coding_table_selectable := 1;
when others then
team_coding_table_selectable := 0;
end;
end if;
–
– determine if this is a RAC instance
qry_str := ‘begin if dbms_utility.is_cluster_database’;
qry_str := qry_str || ’ then :isRAC := 1; else :isRAC := 0; end if; end;’;
–
– determine if this is a RAC DB
begin
execute immediate qry_str using out is_RAC;
exception
when others then
:isRAC := 0;
end;
–
– determine instance id
begin
execute immediate ‘select dbms_utility.current_instance from dual’ into instance_id;
exception
when others then
instance_id := 1;
end;
–
– determine DBID if possible
begin
qry_str := ‘select dbid from v$database’;
execute immediate qry_str into dbid;
exception
when others then
null;
end;
–
– determine instance name and server name if possible
qry_str := ‘select sys_context(’‘USERENV’’,’‘INSTANCE_NAME’’), sys_context(’‘USERENV’’,’‘SERVER_HOST’’) from dual’;
begin
execute immediate qry_str into instance_name, server_name;
exception
– if we don’t have privs on v$instance and not rac, then try to get db name
when others then
if is_rac = 0 then
begin
qry_str := ‘select sys_context(’‘USERENV’’,’‘DB_NAME’’) from dual’;
execute immediate qry_str into instance_name;
exception
when others then
instance_name := null;
end;
end if;
end;
–
– Is this an Amazon RDS Instance?
begin
execute immediate ‘select object_name from sys.all_procedures where owner = ‘‘RDSADMIN’’ and object_name = ‘‘RDSADMIN_UTIL’’ and object_type = ‘‘PACKAGE’’ and subprogram_id = 0’ into AmazonRDSPackage;
exception
when others then
AmazonRDSPackage := null;
end;
–
– What kind of database is this? (pluggable, container, normal)
begin
qry_str := ‘select’ || CHR(13) || CHR(10) ||
’ Case’ || CHR(13) || CHR(10) ||
’ when sys_context(’‘USERENV’’,’‘CON_NAME’’) = ‘‘CDB$ROOT’’ then 1 – container,’ || CHR(13) || CHR(10) ||
’ when sys_context(’‘USERENV’’,’‘CDB_NAME’’) is null then 0 – normal,’ || CHR(13) || CHR(10) ||
’ else 2 – pluggable’ || CHR(13) || CHR(10) ||
’ end db_type’ || CHR(13) || CHR(10) ||
‘from dual’;
execute immediate qry_str into OraDBType;
exception
when others then
OraDBType := 0;
end;
–
– check for existence of Codexpert tables
Select count(‘x’)
into cx_table_count
from sys.all_tables
where owner in (‘TOAD’, USER)
and table_name in (‘CX_XPERTRUN’, ‘CX_XPERTLINES’, ‘CX_XPERTITEMS’, ‘CX_TYPES’, ‘CX_SEVERITYS’, ‘CX_SETRULES’, ‘CX_RULESETS’, ‘CX_METRICS’, ‘CX_METRICRANGES’, ‘CX_CATEGORYS’, ‘CX_BASERULES’);
–
– Assign results to output variables
:OUT_DEBUG_CONNECT_SESSION := debug_connect_session;
:OUT_ALTER_ANY_TABLE := alter_any_table;
:OUT_ALTER_SESSION := alter_session;
:OUT_ALTER_SYSTEM := alter_system;
:OUT_ALTER_DATABASE := alter_database;
:OUT_ALTER_USER := alter_user;
:OUT_CREATE_TABLE := create_table;
:OUT_CREATE_ROLE := create_role;
:OUT_CREATE_TRIGGER := create_trigger;
:OUT_CREATE_SEQUENCE := create_sequence;
:OUT_ADM_DB_TRIG := adm_db_trig;
:OUT_EXECUTE_ANY_PROCEDURE := execute_any_procedure;
:OUT_CREATE_ANY_INDEX := create_any_index;
:OUT_CREATE_ANY_SEQUENCE := create_any_sequence;
:OUT_CREATE_ANY_TABLE := create_any_table;
:OUT_CREATE_ANY_TRIGGER := create_any_trigger;
:OUT_DEBUG_ANY_PROCEDURE := debug_any_procedure;
:OUT_SELECT_ANY_TABLE := select_any_table;
:OUT_UPDATE_ANY_TABLE := update_any_table;
:OUT_GRANT_ANY_OBJECT_PRIV := grant_any_object_priv;
:OUT_GRANT_ANY_ROLE := grant_any_role;
:OUT_GRANT_ANY_PRIVILEGE := grant_any_privilege;
:OUT_SELECT_ANY_DICTIONARY := select_any_dictionary;
:OUT_CREATE_PUBLIC_SYN := create_public_syn;
:OUT_SELECT_CATALOG_ROLE := select_catalog_role;
:OUT_USER := user;
:OUT_DB_CHARSET := db_charset;
:OUT_CX_TABLE_COUNT := cx_table_count;
:OUT_OSUSERNAME := OS_USERNAME;
:OUT_COMPATIBLE_VERSION := compatible_version;
:OUT_TEAM_CODING_SCHEMA := team_coding_schema;
:OUT_TEAM_CODING_TABLE := team_coding_table;
:OUT_TC_TABLE_SELECTABLE := team_coding_table_selectable;
:OUT_SID := sid;
:OUT_LOCAL_TRANS_ID := local_trans_id;
:OUT_HAS_DBMS_JAVA := has_dbms_java;
:OUT_HAS_DBMS_DEBUG := has_dbms_debug;
:OUT_QU_VERSION_OWNER := qu_version_owner;
:OUT_QU_VERSION := CTRepositoryVersion;
:OUT_IS_RAC := is_rac;
:OUT_INSTANCE_ID := instance_id;
:OUT_INSTANCE_NAME := instance_name;
:OUT_SERVER_NAME := server_name;
:OUT_DBID := dbid;
:OUT_AUTHENTICATION_TYPE := authentication_type;
:OUT_IS_AMAZON_RDS := AmazonRDSPackage;
:OUT_DB_TYPE := OraDBType;
end;
:IN_MDL(VARCHAR[14],IN)=‘TOAD 12.7.1.11’
:isRAC(Unknown,IN)=
:OUT_DEBUG_CONNECT_SESSION(INTEGER,OUT)=
:OUT_ALTER_ANY_TABLE(INTEGER,OUT)=
:OUT_ALTER_SESSION(INTEGER,OUT)=
:OUT_ALTER_SYSTEM(INTEGER,OUT)=
:OUT_ALTER_DATABASE(INTEGER,OUT)=
:OUT_ALTER_USER(INTEGER,OUT)=
:OUT_CREATE_TABLE(INTEGER,OUT)=
:OUT_CREATE_ROLE(INTEGER,OUT)=
:OUT_CREATE_TRIGGER(INTEGER,OUT)=
:OUT_CREATE_SEQUENCE(INTEGER,OUT)=
:OUT_ADM_DB_TRIG(INTEGER,OUT)=
:OUT_EXECUTE_ANY_PROCEDURE(INTEGER,OUT)=
:OUT_CREATE_ANY_INDEX(INTEGER,OUT)=
:OUT_CREATE_ANY_SEQUENCE(INTEGER,OUT)=
:OUT_CREATE_ANY_TABLE(INTEGER,OUT)=
:OUT_CREATE_ANY_TRIGGER(INTEGER,OUT)=
:OUT_DEBUG_ANY_PROCEDURE(INTEGER,OUT)=
:OUT_SELECT_ANY_TABLE(INTEGER,OUT)=
:OUT_UPDATE_ANY_TABLE(INTEGER,OUT)=
:OUT_GRANT_ANY_OBJECT_PRIV(INTEGER,OUT)=
:OUT_GRANT_ANY_ROLE(INTEGER,OUT)=
:OUT_GRANT_ANY_PRIVILEGE(INTEGER,OUT)=
:OUT_SELECT_ANY_DICTIONARY(INTEGER,OUT)=
:OUT_CREATE_PUBLIC_SYN(INTEGER,OUT)=
:OUT_SELECT_CATALOG_ROLE(INTEGER,OUT)=
:OUT_USER(VARCHAR[0],OUT)=
:OUT_DB_CHARSET(VARCHAR[0],OUT)=
:OUT_CX_TABLE_COUNT(INTEGER,OUT)=
:OUT_OSUSERNAME(VARCHAR[0],OUT)=
:OUT_COMPATIBLE_VERSION(VARCHAR[0],OUT)=
:OUT_TEAM_CODING_SCHEMA(VARCHAR[0],OUT)=
:OUT_TEAM_CODING_TABLE(VARCHAR[0],OUT)=
:OUT_TC_TABLE_SELECTABLE(INTEGER,OUT)=
:OUT_SID(VARCHAR[0],OUT)=
:OUT_LOCAL_TRANS_ID(VARCHAR[0],OUT)=
:OUT_HAS_DBMS_JAVA(INTEGER,OUT)=
:OUT_HAS_DBMS_DEBUG(INTEGER,OUT)=
:OUT_QU_VERSION_OWNER(VARCHAR[0],OUT)=
:OUT_QU_VERSION(INTEGER,OUT)=
:OUT_IS_RAC(INTEGER,OUT)=
:OUT_INSTANCE_ID(INTEGER,OUT)=
:OUT_INSTANCE_NAME(VARCHAR[0],OUT)=
:OUT_SERVER_NAME(VARCHAR[0],OUT)=
:OUT_DBID(VARCHAR[0],OUT)=
:OUT_AUTHENTICATION_TYPE(VARCHAR[0],OUT)=
:OUT_IS_AMAZON_RDS(VARCHAR[0],OUT)=
:OUT_DB_TYPE(INTEGER,OUT)=
Session: yng@testdb
Timestamp: 16:59:20.962
SELECT null
FROM SYS.USER$
WHERE 0=1
Error: ORA-01031: insufficient privileges
Session: yng@testdb
Timestamp: 16:59:21.009
Select username
from sys.DBA_USERS
order by username
Session: yng@testdb
Timestamp: 16:59:21.268
DECLARE
TYPE tc_syn_type IS VARRAY (2) OF VARCHAR2 (30);
syns CONSTANT tc_syn_type := tc_syn_type (‘QUEST_COM_TEAM_CODING’, ‘SQLNAV4_VCS_CONFIG’);
local_owner VARCHAR2 (30);
local_table VARCHAR2 (30);
local_options VARCHAR2 (16);
local_version VARCHAR2 (16);
TABLE_MISSING EXCEPTION;
PRAGMA EXCEPTION_INIT (TABLE_MISSING, -942);
BEGIN
local_owner := NULL;
local_table := NULL;
local_options := NULL;
local_version := NULL;
– Look for Team Coding synonym
FOR i IN syns.FIRST … syns.LAST
LOOP
BEGIN
Select table_owner, table_name
INTO local_owner, local_table
FROM sys.DBA_SYNONYMS
WHERE owner = ‘PUBLIC’ AND synonym_name = syns (i);