管理ユーザ（マスターユーザ）でログイン後、DBの中を確認してみました。
※RDSへの接続方法はこちら
SQL> sho user
USER is "TEST"
SQL>
TESTユーザのシステム権限をUSER_SYS_PRIVSビューで確認
SQL> set pages 100 line 200
SQL> col username for a10
SQL> col privilege for a30
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM COM
---------- ------------------------------ --- ---
TEST DROP ANY DIRECTORY YES NO
TEST EXEMPT REDACTION POLICY YES NO
TEST FLASHBACK ANY TABLE YES NO
TEST EXEMPT ACCESS POLICY YES NO
TEST SELECT ANY TABLE YES NO
TEST CHANGE NOTIFICATION YES NO
TEST GRANT ANY OBJECT PRIVILEGE YES NO
TEST ALTER PUBLIC DATABASE LINK YES NO
TEST EXEMPT IDENTITY POLICY YES NO
TEST ALTER DATABASE LINK YES NO
TEST UNLIMITED TABLESPACE YES NO
TEST RESTRICTED SESSION YES NO
12 rows selected.
TESTユーザに付与されたロールをUSER_ROLE_PRIVSビューで確認
SQL> col granted_role for a30
SQL> select granted_role from user_role_privs;
GRANTED_ROLE
------------------------------
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
CAPTURE_ADMIN
CONNECT
CTXAPP
DATAPUMP_EXP_FULL_DATABASE ★DataPumpつかえる
DATAPUMP_IMP_FULL_DATABASE ★DataPumpつかえる
DBA ★
DELETE_CATALOG_ROLE
EM_EXPRESS_ALL
EM_EXPRESS_BASIC
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
OEM_ADVISOR
OEM_MONITOR
OPTIMIZER_PROCESSING_RATE
RDS_MASTER_ROLE
RECOVERY_CATALOG_OWNER
RECOVERY_CATALOG_USER
RESOURCE ★
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
SODA_APP
XDBADMIN
XDB_SET_INVOKER
29 rows selected.
DBAロールの中身をROLE_SYS_PRIVSビューで確認
SQL> set pages 300 line 200
SQL> col role for a20
SQL> col privilege for a50
SQL> select * from role_sys_privs where role='DBA' order by 2;
ROLE PRIVILEGE ADM COM
-------------------- -------------------------------------------------- --- ---
DBA ADMINISTER ANY SQL TUNING SET YES YES
DBA ADMINISTER DATABASE TRIGGER YES YES
DBA ADMINISTER RESOURCE MANAGER YES YES
DBA ADMINISTER SQL MANAGEMENT OBJECT YES YES
DBA ADMINISTER SQL TUNING SET YES YES
DBA ADVISOR YES YES
DBA ALTER ANY ASSEMBLY YES YES
DBA ALTER ANY CLUSTER YES YES
DBA ALTER ANY CUBE YES YES
DBA ALTER ANY CUBE BUILD PROCESS YES YES
DBA ALTER ANY CUBE DIMENSION YES YES
DBA ALTER ANY DIMENSION YES YES
DBA ALTER ANY EDITION YES YES
DBA ALTER ANY EVALUATION CONTEXT YES YES
DBA ALTER ANY INDEX YES YES
DBA ALTER ANY INDEXTYPE YES YES
DBA ALTER ANY LIBRARY YES YES
DBA ALTER ANY MATERIALIZED VIEW YES YES
DBA ALTER ANY MEASURE FOLDER YES YES
DBA ALTER ANY MINING MODEL YES YES
DBA ALTER ANY OPERATOR YES YES
DBA ALTER ANY OUTLINE YES YES
DBA ALTER ANY PROCEDURE YES YES
DBA ALTER ANY ROLE YES YES
DBA ALTER ANY RULE YES YES
DBA ALTER ANY RULE SET YES YES
DBA ALTER ANY SEQUENCE YES YES
DBA ALTER ANY SQL PROFILE YES YES
DBA ALTER ANY SQL TRANSLATION PROFILE YES YES
DBA ALTER ANY TABLE YES YES
DBA ALTER ANY TRIGGER YES YES
DBA ALTER ANY TYPE YES YES
DBA ALTER PROFILE YES YES
DBA ALTER RESOURCE COST YES YES
DBA ALTER ROLLBACK SEGMENT YES YES
DBA ALTER SESSION YES YES
DBA ALTER TABLESPACE YES YES
DBA ALTER USER YES YES
DBA ANALYZE ANY YES YES
DBA ANALYZE ANY DICTIONARY YES YES
DBA AUDIT ANY YES YES
DBA AUDIT SYSTEM YES YES
DBA BACKUP ANY TABLE YES YES
DBA BECOME USER YES YES
DBA CHANGE NOTIFICATION YES YES
DBA COMMENT ANY MINING MODEL YES YES
DBA COMMENT ANY TABLE YES YES
DBA CREATE ANY ASSEMBLY YES YES
DBA CREATE ANY CLUSTER YES YES
DBA CREATE ANY CONTEXT YES YES
DBA CREATE ANY CREDENTIAL YES YES
DBA CREATE ANY CUBE YES YES
DBA CREATE ANY CUBE BUILD PROCESS YES YES
DBA CREATE ANY CUBE DIMENSION YES YES
DBA CREATE ANY DIMENSION YES YES
DBA CREATE ANY EDITION YES YES
DBA CREATE ANY EVALUATION CONTEXT YES YES
DBA CREATE ANY INDEX YES YES
DBA CREATE ANY INDEXTYPE YES YES
DBA CREATE ANY JOB YES YES
DBA CREATE ANY LIBRARY YES YES
DBA CREATE ANY MATERIALIZED VIEW YES YES
DBA CREATE ANY MEASURE FOLDER YES YES
DBA CREATE ANY MINING MODEL YES YES
DBA CREATE ANY OPERATOR YES YES
DBA CREATE ANY OUTLINE YES YES
DBA CREATE ANY PROCEDURE YES YES
DBA CREATE ANY RULE YES YES
DBA CREATE ANY RULE SET YES YES
DBA CREATE ANY SEQUENCE YES YES
DBA CREATE ANY SQL PROFILE YES YES
DBA CREATE ANY SQL TRANSLATION PROFILE YES YES
DBA CREATE ANY SYNONYM YES YES
DBA CREATE ANY TABLE YES YES
DBA CREATE ANY TRIGGER YES YES
DBA CREATE ANY TYPE YES YES
DBA CREATE ANY VIEW YES YES
DBA CREATE ASSEMBLY YES YES
DBA CREATE CLUSTER YES YES
DBA CREATE CREDENTIAL YES YES
DBA CREATE CUBE YES YES
DBA CREATE CUBE BUILD PROCESS YES YES
DBA CREATE CUBE DIMENSION YES YES
DBA CREATE DATABASE LINK YES YES
DBA CREATE DIMENSION YES YES
DBA CREATE EVALUATION CONTEXT YES YES
DBA CREATE INDEXTYPE YES YES
DBA CREATE JOB YES YES
DBA CREATE LIBRARY YES YES
DBA CREATE MATERIALIZED VIEW YES YES
DBA CREATE MEASURE FOLDER YES YES
DBA CREATE MINING MODEL YES YES
DBA CREATE OPERATOR YES YES
DBA CREATE PLUGGABLE DATABASE YES YES
DBA CREATE PROCEDURE YES YES
DBA CREATE PROFILE YES YES
DBA CREATE PUBLIC DATABASE LINK YES YES
DBA CREATE PUBLIC SYNONYM YES YES
DBA CREATE ROLE YES YES
DBA CREATE ROLLBACK SEGMENT YES YES
DBA CREATE RULE YES YES
DBA CREATE RULE SET YES YES
DBA CREATE SEQUENCE YES YES
DBA CREATE SESSION YES YES
DBA CREATE SQL TRANSLATION PROFILE YES YES
DBA CREATE SYNONYM YES YES
DBA CREATE TABLE YES YES
DBA CREATE TABLESPACE YES YES
DBA CREATE TRIGGER YES YES
DBA CREATE TYPE YES YES
DBA CREATE USER YES YES
DBA CREATE VIEW YES YES
DBA DEBUG ANY PROCEDURE YES YES
DBA DEBUG CONNECT SESSION YES YES
DBA DELETE ANY CUBE DIMENSION YES YES
DBA DELETE ANY MEASURE FOLDER YES YES
DBA DELETE ANY TABLE YES YES
DBA DEQUEUE ANY QUEUE YES YES
DBA DROP ANY ASSEMBLY YES YES
DBA DROP ANY CLUSTER YES YES
DBA DROP ANY CONTEXT YES YES
DBA DROP ANY CUBE YES YES
DBA DROP ANY CUBE BUILD PROCESS YES YES
DBA DROP ANY CUBE DIMENSION YES YES
DBA DROP ANY DIMENSION YES YES
DBA DROP ANY EDITION YES YES
DBA DROP ANY EVALUATION CONTEXT YES YES
DBA DROP ANY INDEX YES YES
DBA DROP ANY INDEXTYPE YES YES
DBA DROP ANY LIBRARY YES YES
DBA DROP ANY MATERIALIZED VIEW YES YES
DBA DROP ANY MEASURE FOLDER YES YES
DBA DROP ANY MINING MODEL YES YES
DBA DROP ANY OPERATOR YES YES
DBA DROP ANY OUTLINE YES YES
DBA DROP ANY PROCEDURE YES YES
DBA DROP ANY ROLE YES YES
DBA DROP ANY RULE YES YES
DBA DROP ANY RULE SET YES YES
DBA DROP ANY SEQUENCE YES YES
DBA DROP ANY SQL PROFILE YES YES
DBA DROP ANY SQL TRANSLATION PROFILE YES YES
DBA DROP ANY SYNONYM YES YES
DBA DROP ANY TABLE YES YES
DBA DROP ANY TRIGGER YES YES
DBA DROP ANY TYPE YES YES
DBA DROP ANY VIEW YES YES
DBA DROP PROFILE YES YES
DBA DROP PUBLIC DATABASE LINK YES YES
DBA DROP PUBLIC SYNONYM YES YES
DBA DROP ROLLBACK SEGMENT YES YES
DBA DROP TABLESPACE YES YES
DBA DROP USER YES YES
DBA EM EXPRESS CONNECT YES YES
DBA ENQUEUE ANY QUEUE YES YES
DBA EXECUTE ANY ASSEMBLY YES YES
DBA EXECUTE ANY CLASS YES YES
DBA EXECUTE ANY EVALUATION CONTEXT YES YES
DBA EXECUTE ANY INDEXTYPE YES YES
DBA EXECUTE ANY LIBRARY YES YES
DBA EXECUTE ANY OPERATOR YES YES
DBA EXECUTE ANY PROCEDURE YES YES
DBA EXECUTE ANY PROGRAM YES YES
DBA EXECUTE ANY RULE YES YES
DBA EXECUTE ANY RULE SET YES YES
DBA EXECUTE ANY TYPE YES YES
DBA EXECUTE ASSEMBLY YES YES
DBA EXEMPT DDL REDACTION POLICY YES YES
DBA EXEMPT DML REDACTION POLICY YES YES
DBA EXPORT FULL DATABASE YES YES
DBA FLASHBACK ANY TABLE YES YES
DBA FLASHBACK ARCHIVE ADMINISTER YES YES
DBA FORCE ANY TRANSACTION YES YES
DBA FORCE TRANSACTION YES YES
DBA GLOBAL QUERY REWRITE YES YES
DBA GRANT ANY OBJECT PRIVILEGE YES YES
DBA IMPORT FULL DATABASE YES YES
DBA INSERT ANY CUBE DIMENSION YES YES
DBA INSERT ANY MEASURE FOLDER YES YES
DBA INSERT ANY TABLE YES YES
DBA LOCK ANY TABLE YES YES
DBA LOGMINING YES YES
DBA MANAGE ANY FILE GROUP YES YES
DBA MANAGE ANY QUEUE YES YES
DBA MANAGE FILE GROUP YES YES
DBA MANAGE SCHEDULER YES YES
DBA MANAGE TABLESPACE YES YES
DBA MERGE ANY VIEW YES YES
DBA ON COMMIT REFRESH YES YES
DBA QUERY REWRITE YES YES
DBA READ ANY TABLE YES YES
DBA REDEFINE ANY TABLE YES YES
DBA RESTRICTED SESSION YES YES
DBA RESUMABLE YES YES
DBA SELECT ANY CUBE YES YES
DBA SELECT ANY CUBE BUILD PROCESS YES YES
DBA SELECT ANY CUBE DIMENSION YES YES
DBA SELECT ANY DICTIONARY YES YES
DBA SELECT ANY MEASURE FOLDER YES YES
DBA SELECT ANY MINING MODEL YES YES
DBA SELECT ANY SEQUENCE YES YES
DBA SELECT ANY TABLE YES YES
DBA SELECT ANY TRANSACTION YES YES
DBA SET CONTAINER YES YES
DBA UNDER ANY TABLE YES YES
DBA UNDER ANY TYPE YES YES
DBA UNDER ANY VIEW YES YES
DBA UPDATE ANY CUBE YES YES
DBA UPDATE ANY CUBE BUILD PROCESS YES YES
DBA UPDATE ANY CUBE DIMENSION YES YES
DBA UPDATE ANY TABLE YES YES
DBA USE ANY SQL TRANSLATION PROFILE YES YES
212 rows selected.
RESOURCEロールの中身を確認
select * from role_sys_privs where role='RESOURCE' order by 2;
ROLE PRIVILEGE ADM COM
-------------------- -------------------------------------------------- --- ---
RESOURCE CREATE CLUSTER NO YES
RESOURCE CREATE INDEXTYPE NO YES
RESOURCE CREATE OPERATOR NO YES
RESOURCE CREATE PROCEDURE NO YES
RESOURCE CREATE SEQUENCE NO YES
RESOURCE CREATE TABLE NO YES
RESOURCE CREATE TRIGGER NO YES
RESOURCE CREATE TYPE NO YES
8 rows selected.
デフォルトユーザ確認
SQL> col username for a20
SQL> select username from dba_users;
USERNAME
--------------------
SYS
SYSTEM
GSMADMIN_INTERNAL
TEST ★RDS作成時に指定したマスターユーザ
AUDSYS
GSMUSER
XS$NULL
SYSKM
APPQOSSYS
XDB
RDSADMIN
DBSNMP
SYSDG
DIP
OUTLN
ANONYMOUS
CTXSYS
SYSBACKUP
GSMCATUSER
19 rows selected.
V$DATABASEビューでアーカイブログモードの確認
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL1 ARCHIVELOG ★デフォルトでアーカイブログモードになっている
ちなみに、archive log listでアーカイブログモードを確認しようとすると「権限が不足してると」怒られる
SQL> archive log list;
ORA-01031: insufficient privileges
その他にも shutdownコマンド も使えない
SQL> shu immediate
ORA-01031: insufficient privileges
(ノーアーカイブログモードにするにはどうすれば。。。)
制御ファイルはデフォルト１つ
SQL> sho parameter control_fi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /rdsdbdata/db/ORCL1_A/controlfile/control-01.ctl
オンラインREDOログ・ファイルは1グループ1メンバ
SQL> col member for a60
SQL> select group#,status,type,member from v$logfile order by 1,4;SQL>
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
1 ONLINE /rdsdbdata/db/ORCL1_A/onlinelog/o1_mf_1_gk942xc0_.log
2 ONLINE /rdsdbdata/db/ORCL1_A/onlinelog/o1_mf_2_gk942yh1_.log
3 ONLINE /rdsdbdata/db/ORCL1_A/onlinelog/o1_mf_3_gk9430l1_.log
4 ONLINE /rdsdbdata/db/ORCL1_A/onlinelog/o1_mf_4_gk9432od_.log
表領域とデータファイル確認。RDSADMIN表領域ってのがある。
SQL> col file_name for a60
SQL> select tablespace_name,file_name,status,bytes/1024/1024 mbytes,increment_by,autoextensible,online_status from dba_data_files;
TABLESPACE_NAME FILE_NAME STATUS MBYTES INCREMENT_BY AUT ONLINE_
------------------------------ ------------------------------------------------------------ --------- ---------- ------------ --- -------
SYSTEM /rdsdbdata/db/ORCL1_A/datafile/o1_mf_system_gf76cbh1_.dbf AVAILABLE 400 12800 YES SYSTEM
SYSAUX /rdsdbdata/db/ORCL1_A/datafile/o1_mf_sysaux_gf76d1l1_.dbf AVAILABLE 328.9375 12800 YES ONLINE
UNDO_T1 /rdsdbdata/db/ORCL1_A/datafile/o1_mf_undo_t1_gf76df41_.dbf AVAILABLE 300 1280 YES ONLINE
USERS /rdsdbdata/db/ORCL1_A/datafile/o1_mf_users_gf76dh0r_.dbf AVAILABLE 100 12800 YES ONLINE
RDSADMIN /rdsdbdata/db/ORCL1_A/datafile/o1_mf_rdsadmin_gf77glhp_.dbf AVAILABLE 7 128 YES ONLINE
一時表領域を確認。
SQL> col TABLESPACE_NAME for a10
SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 as MB from dba_temp_files;SQL>
TABLESPACE FILE_NAME MB
---------- ------------------------------------------------------------ ----------
TEMP /rdsdbdata/db/ORCL1_A/datafile/o1_mf_temp_gk94bo9j_.tmp 100
alter system文 での初期化パラメータの変更はできなさそう（権限が不十分と言われる）。
初期化パラメータの変更はマネージメントコンソールからおこなうみたい。
SQL> sho parameter strea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>
SQL>
SQL> alter system set streams_pool_size=150m scope=both;
alter system set streams_pool_size=150m scope=both
*
ERROR at line 1:
ORA-01031: insufficient privileges
PFILEもくつれない（そもそもOSにアクセスできない）
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-01031: insufficient privileges
DATABASE_PROPERTIESビューでDBのプロパティを確認。
デフォルト表領域は 「USERS」、デフォルト一時表領域は「TEMP」
SQL> col property_name for a30
SQL> col property_value for a40
SQL> select property_name,property_value from database_properties;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
Flashback Timestamp TimeZone GMT
TDE_MASTER_KEY_ID
EXPORT_VIEWS_VERSION 8
DEFAULT_TBS_TYPE BIGFILE
GLOBAL_DB_NAME ORCL1
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_NUMERIC_CHARACTERS .,
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 33
DST_UPGRADE_STATE NONE
MAX_STRING_SIZE STANDARD
DBTIMEZONE +00:00
WORKLOAD_CAPTURE_MODE
WORKLOAD_REPLAY_MODE
NO_USERID_VERIFIER_SALT 9FBAD6D3759B5204F101F9E263E81045
37 rows selected.
FORCE LOGGINGモードの確認。デフォルトはOFF
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
サプリメンタルロギングの確認。デフォルトはOFF。
SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
NO