SQL> select t.username,t.default_tablespace,t.temporary_tablespace from dba_users t where t.username = 'SCOTT';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
SCOTT USERS TEMP
alter database default tablespace
alter database default temporary tablespace
create user usr1 identified by usr1 default tablespace users default temporary tablespace temp;
drop user username cascade;
SQL> select t.username,t.account_status from dba_users t where t.account_status <> 'OPEN';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED
APEX_PUBLIC_USER EXPIRED & LOCKED
DIP EXPIRED & LOCKED
IX EXPIRED & LOCKED
MDDATA EXPIRED & LOCKED
PM EXPIRED & LOCKED
SQL> select * from Dba_Profiles t where t.resource_name='FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
SQL> select t.name,t.lcount from user$ t where t.name='SYS';
NAME LCOUNT
------------------------------ ----------
SYS 0
SQL> select t.name,t.lcount from user$ t where t.name='SCOTT';
NAME LCOUNT
------------------------------ ----------
SCOTT 1
SQL> select t.name,t.Password,T.SPARE4 from user$ t where t.name='SCOTT';
NAME PASSWORD SPARE4
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SCOTT F894844C34402B67 S:D24E5C00D8CDFE8BDF82664D133C08FBC974B33D2FBD7066779D1FF8C5D3
SQL> select t.name,t.Password,T.SPARE4 from user$ t where t.name='SCOTT';
NAME PASSWORD SPARE4
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SCOTT F894844C34402B67 S:D24E5C00D8CDFE8BDF82664D133C08FBC974B33D2FBD7066779D1FF8C5D3
SQL> CONN scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> CONN scott/TIGER
Connected.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL> CONN scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> CONN scott/TIGER
Connected.
SQL> select * from dba_users_with_defpwd;
USERNAME
------------------------------
DIP
MDSYS
XS$NULL
SPATIAL_WFS_ADMIN_USR
CTXSYS
HR
OLAPSYS
OUTLN
OWBSYS
SPATIAL_CSW_ADMIN_USR
EXFSYS
ORACLE_OCM
SCOTT
MDDATA
OE
ORDPLUGINS
ORDSYS
PM
SH
APPQOSSYS
USERNAME
------------------------------
IX
ORDDATA
XDB
SI_INFORMTN_SCHEMA
WMSYS
25 rows selected
select t.view_name,t.text from dba_views t where t.view_name='DBA_USERS_WITH_DEFPWD';
SELECT DISTINCT u.name
FROM SYS.user$ u, SYS.default_pwd$ dp
WHERE
(u.type# = 1
AND bitand(u.astatus, 16) = 16
) OR
(u.type# = 1
AND u.password = dp.pwd_verifier
AND u.name = dp.user_name
AND dp.pv_type = 0)
SQL> select * from default_pwd$ where rownum <=10;
USER_NAME PWD_VERIFIER PV_TYPE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
AASH 9B52488370BB3D77 0
ABA1 30FD307004F350DE 0
ABM D0F2982F121C7840 0
AD_MONITOR 54F0C83F51B03F49 0
ADAMS 72CDEF4A3483F60D 0
ADLDEMO 147215F51929A6E8 0
ADS D23F0F5D871EB69F 0
ADSEUL_US 4953B2EB6FCB4339 0
AHL 7910AE63C9F7EEEE 0
AHM 33C2E27CF5E401A4 0
10 rows selected
SQL> select t.username,t.password,t.password_versions from dba_users t where rownum <7;
USERNAME PASSWORD PASSWORD_VERSIONS
------------------------------ ------------------------------ -----------------
DBSNMP 10G 11G
SYSTEM 10G 11G
SYS 10G 11G
MGMT_VIEW 10G 11G
PERFSTAT 10G 11G
USR1 10G 11G
6 rows selected
select t.view_name,t.text from dba_views t where t.view_name='DBA_USERS';
select u.name, u.user#,
decode(u.password, 'GLOBAL', u.password,
'EXTERNAL', u.password,
NULL),
m.status,
decode(u.astatus, 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
decode(u.astatus,
1, u.exptime,
2, u.exptime,
5, u.exptime,
6, u.exptime,
9, u.exptime,
10, u.exptime,
decode(u.ptime, '', to_date(NULL),
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400)))),
dts.name, tts.name, u.ctime, p.name,
nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
u.ext_username,
decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL),
decode(bitand(u.spare1, 16),
16, 'Y',
'N'),
decode(u.password, 'GLOBAL', 'GLOBAL',
'EXTERNAL', 'EXTERNAL',
'PASSWORD')
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
cgm.value = u.name),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
[oracle@dba ~]$ cd /u01/app/oracle/product/11.2.0/rdbms/admin/
[oracle@dba admin]$ ls -l utlpwdmg.sql
-rw-r--r-- 1 oracle oinstall 11555 8月 13 2006 utlpwdmg.sql
@?/rdbms/admin/utlpwdmg.sql
SQL> select t.username,t.profile from dba_users t where t.username='SCOTT';
USERNAME PROFILE
------------------------------ ------------------------------
SCOTT DEFAULT
SQL> select * from dict where rownum <6;
TABLE_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
DBA_CONS_COLUMNS Information about accessible columns in constraint definitions
DBA_LOG_GROUP_COLUMNS Information about columns in log group definitions
DBA_LOBS Description of LOBs contained in all tables
DBA_CATALOG All database Tables, Views, Synonyms, Sequences
DBA_CLUSTERS Description of all clusters in the database
SQL> conn usr3/usr3
ERROR:
ORA-01045: user USR3 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> grant create session to usr3;
Grant succeede
SQL> conn usr3/usr3
Connected.
SQL> select count(*) from dict;
COUNT(*)
----------
852
SQL> desc dict
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> select TABLE_NAME from dict where rownum <6;
TABLE_NAME
-------------------------
USER_CONS_COLUMNS
USER_LOG_GROUP_COLUMNS
USER_LOBS
USER_CATALOG
USER_CLUSTERS
SQL> select count(*) from user_tables;
COUNT(*)
----------
0
SQL> select count(*) from user_views;
COUNT(*)
----------
0
SQL> create table usr3 (name varchar2(20));
create table usr3 (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant create table to usr3;
Grant succeeded
SQL> create table usr3 (name varchar2(20));
Table created.
SQL> insert into usr3 values('hufei');
insert into usr3 values('hufei')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
SQL> alter user usr3 quota 10m on users;
User altered
SQL> insert into usr3 values('hufei');
1 row created.
SQL> desc dba_sys_privs
Name Type Nullable Default Comments
------------ ------------ -------- ------- ----------------------------------------------
GRANTEE VARCHAR2(30) Grantee Name, User or Role receiving the grant
PRIVILEGE VARCHAR2(40) System privilege
ADMIN_OPTION VARCHAR2(3) Y Grant was with the ADMIN option
SQL> select * from dba_sys_privs t where t.grantee='USR3';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
USR3 CREATE SESSION NO
USR3 CREATE TABLE NO
SQL> select distinct t.privilege from dba_sys_privs t where t.privilege like '%TABLE';
PRIVILEGE
----------------------------------------
CREATE TABLE
ALTER ANY TABLE
UNDER ANY TABLE
COMMENT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
CREATE ANY TABLE
DELETE ANY TABLE
DROP ANY TABLE
FLASHBACK ANY TABLE
BACKUP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE
13 rows selected
SQL> select * from dba_sys_privs t where t.grantee='CONNECT';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
CONNECT CREATE SESSION NO
SQL> select * from dba_sys_privs t where t.grantee='RESOURCE';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
SQL> drop user usr3 cascade;
User dropped.
SQL> create user usr3 identified by usr3;
User created.
SQL> grant connect,resource to usr3;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dba ~]$ sql /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 5 15:21:45 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn usr3/usr3
Connected.
SQL> create table usr3 (name varchar2(20));
Table created.
SQL> insert into usr3 values('hufei');
1 row created.
SQL> select * from dba_sys_privs t where t.grantee='USR3';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
USR3 UNLIMITED TABLESPACE NO
SQL> revoke unlimited tablespace from usr3;
Revoke succeeded.
SQL> alter user usr3 quota unlimited on users;
User altered.
SQL> select * from dba_ts_quotas t where t.username='USR3';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
USERS USR3 65536 -1 8 -1 NO
SQL> desc dba_tab_privs
Name Type Nullable Default Comments
---------- ------------ -------- ------- ----------------------------------------
GRANTEE VARCHAR2(30) User to whom access was granted
OWNER VARCHAR2(30) Owner of the object
TABLE_NAME VARCHAR2(30) Name of the object
GRANTOR VARCHAR2(30) Name of the user who performed the grant
PRIVILEGE VARCHAR2(40) Table Privilege
GRANTABLE VARCHAR2(3) Y Privilege is grantable
HIERARCHY VARCHAR2(3) Y Privilege is with hierarchy option
SQL> drop user eygle cascade;
User dropped.
SQL> create user eygle identified by eygle;
User created.
SQL> create user julia identified by julia;
User created.
SQL> grant connect,resource to eygle,julia;
Grant succeeded.
SQL> conn eygle/eygle
Connected.
SQL> create table test as select * from dual;
Table created.
SQL> grant select on test to julia;
Grant succeeded.
SQL> conn julia/julia
Connected.
SQL> select * from eygle.test;
D
-
X
SQL> select * from dba_tab_privs t where t.grantee='JULIA';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
JULIA EYGLE TEST EYGLE SELECT NO NO
SQL> conn eygle/eygle
Connected.
SQL> grant delete on test to julia;
Grant succeeded.
SQL> select * from dba_tab_privs t where t.grantee='JULIA';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
JULIA EYGLE TEST EYGLE DELETE NO NO
JULIA EYGLE TEST EYGLE SELECT NO NO
SQL> desc user$
Name Type Nullable Default Comments
------------ -------------- -------- ------- --------
USER# NUMBER
NAME VARCHAR2(30)
TYPE# NUMBER
PASSWORD VARCHAR2(30) Y
DATATS# NUMBER
TEMPTS# NUMBER
CTIME DATE
PTIME DATE Y
EXPTIME DATE Y
LTIME DATE Y
RESOURCE$ NUMBER
AUDIT$ VARCHAR2(38) Y
DEFROLE NUMBER
DEFGRP# NUMBER Y
DEFGRP_SEQ# NUMBER Y
ASTATUS NUMBER 0
LCOUNT NUMBER 0
DEFSCHCLASS VARCHAR2(30) Y
EXT_USERNAME VARCHAR2(4000) Y
SPARE1 NUMBER Y
SPARE2 NUMBER Y
SPARE3 NUMBER Y
SPARE4 VARCHAR2(1000) Y
SPARE5 VARCHAR2(1000) Y
SPARE6 DATE Y
SQL> select * from dba_roles;
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CONNECT NO NONE
RESOURCE NO NONE
DBA NO NONE
SELECT_CATALOG_ROLE NO NONE
EXECUTE_CATALOG_ROLE NO NONE
DELETE_CATALOG_ROLE NO NONE
EXP_FULL_DATABASE NO NONE
IMP_FULL_DATABASE NO NONE
Execution Plan
----------------------------------------------------------
Plan hash value: 4163253942
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 1320 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| USER$ | 55 | 1320 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE#"=0 AND "NAME"<>'PUBLIC' AND "NAME"<>'_NEXT_USER')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
2445 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
54 rows processed
select text from dba_views t where t.view_name='DBA_ROLES';
select name, decode(password, null, 'NO',
'EXTERNAL', 'EXTERNAL',
'GLOBAL', 'GLOBAL',
'YES'),
decode(password, null, 'NONE',
'EXTERNAL', 'EXTERNAL',
'GLOBAL', 'GLOBAL',
'APPLICATION', 'APPLICATION',
'PASSWORD')
from user$
where type# = 0 and name not in ('PUBLIC', '_NEXT_USER')
SQL> select name from user$ where type#=0 minus select role from dba_roles;
NAME
--------------------------------------------------
PUBLIC
_NEXT_USER
SQL> select * from role_sys_privs t where t.role='CONNECT';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
CONNECT CREATE SESSION NO
SQL> select * from role_sys_privs t where t.role='RESOURCE';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
SQL> SELECT * FROM DBA_ROLE_PRIVS T WHERE T.granted_role='DBA';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
SYS DBA YES YES
LOFE DBA NO YES
USR1 DBA NO YES
SYSTEM DBA YES YES
USR2 DBA NO YES
SQL> select count(*) from dba_tab_privs t where t.grantee ='PUBLIC';
COUNT(*)
----------
28497
SQL> select t.table_name,t.grantee,t.privilege from dba_tab_privs t where t.grantee='PUBLIC' and t.table_name like 'UTL%';
TABLE_NAME GRANTEE PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
UTL_ALL_IND_COMPS PUBLIC SELECT
UTL_BINARYINPUTSTREAM PUBLIC EXECUTE
UTL_BINARYOUTPUTSTREAM PUBLIC EXECUTE
UTL_CHARACTERINPUTSTREAM PUBLIC EXECUTE
UTL_CHARACTEROUTPUTSTREAM PUBLIC EXECUTE
UTL_COLL PUBLIC EXECUTE
UTL_COMPRESS PUBLIC EXECUTE
UTL_ENCODE PUBLIC EXECUTE
UTL_FILE PUBLIC EXECUTE
UTL_GDK PUBLIC EXECUTE
UTL_HTTP PUBLIC EXECUTE
UTL_I18N PUBLIC EXECUTE
UTL_IDENT PUBLIC EXECUTE
UTL_INADDR PUBLIC EXECUTE
UTL_LMS PUBLIC EXECUTE
UTL_MATCH PUBLIC EXECUTE
UTL_NLA PUBLIC EXECUTE
UTL_NLA_ARRAY_DBL PUBLIC EXECUTE
UTL_NLA_ARRAY_FLT PUBLIC EXECUTE
UTL_NLA_ARRAY_INT PUBLIC EXECUTE
TABLE_NAME GRANTEE PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
UTL_RAW PUBLIC EXECUTE
UTL_REF PUBLIC EXECUTE
UTL_SMTP PUBLIC EXECUTE
UTL_TCP PUBLIC EXECUTE
UTL_URL PUBLIC EXECUTE
25 rows selected
SQL> select count(*) from dba_objects t where t.status='INVALID';
COUNT(*)
----------
1
SQL> create user hacker identified by hacker;
User created.
SQL> grant create session to hacker;
Grant succeeded.
SQL> grant execute any procedure to hacker;
Grant succeeded.
SQL> create user loser identified by loser;
User created.
SQL> grant connect to loser;
Grant succeeded.
-------------------------------------------------------------------------------------------------------------------------------------------
SQL> drop user eygle cascade;
User dropped.
SQL> create user eygle identified by eygle;
User created.
SQL> grant connect,resource to eygle,julia;
Grant succeeded.
SQL> grant execute any procedure to eygle;
Grant succeeded.
SQL> create or replace procedure system.do(p_text varchar2)
2 is
3 begin
4 execute immediate p_text;
5 end;
6 /
Procedure created.
SQL> create or replace procedure system.do(p_text varchar2)
is
begin
execute immediate p_text;
end;
/
Procedure created.
SQL> exec system.do('grant dba to eygle');
PL/SQL procedure successfully completed.
SQL> show user
USER is "EYGLE"
SQL> select count(*) from session_privs;
COUNT(*)
----------
202
SQL> show parameter o7_di
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 398459248 bytes
Database Buffers 327155712 bytes
Redo Buffers 2867200 bytes
Database mounted.
Database opened.
SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 3134
Session ID: 48 Serial number: 11
ERROR:
ORA-03114: not connected to ORACLE
SQL> conn eygle/eygle
Connected.
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
75521
SQL> conn sys/oracle
Connected.
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=false scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 398459248 bytes
Database Buffers 327155712 bytes
Redo Buffers 2867200 bytes
Database mounted.
Database opened.
SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL> conn sys/oracle
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Warning: You are no longer connected to ORACLE.
SQL> conn eygle/eygle
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> grant select any dictionary to eygle;
Grant succeeded.
SQL> conn eygle/eygle
Connected.
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
75521
SQL> revoke select any table from eygle;
Revoke succeeded.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SELECT ANY DICTIONARY
11 rows selected.
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------------------------------------------------
VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS
----------------- ------------------- ---------- --- ---------- ------- --------------- ----------
SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--- ----------------- ------------------ --------- ---
1 lote dba.fei.com
11.2.0.3.0 2014-05-06 08:53:54 OPEN NO 1 STARTED ALLOWED
NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> revoke select any dictionary from eygle;
Revoke succeeded.
SQL> conn eygle/eygle
Connected.
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> revoke select any dictionary from eygle;
Revoke succeeded.
SQL> grant select_catalog_role to eygle;
Grant succeeded.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2925
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> revoke select_catalog_role from eygle;
Revoke succeeded.
SQL> conn eygle/eygle
Connected.
SQL> select count(*) from dba_tables;
select count(*) from dba_tables
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show parameter os_a
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
SQL> alter system set os_authent_prefix='' scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 398459248 bytes
Database Buffers 327155712 bytes
Redo Buffers 2867200 bytes
Database mounted.
Database opened.
SQL> create user oracle identified externally;
User created.
SQL> grant connect to oracle
2 ;
Grant succeeded.
SQL> select username,password from dba_users where username='ORACLE';
USERNAME PASSWORD
------------------------------ ------------------------------
ORACLE EXTERNAL
SQL> connect /
Connected.
SQL> show user
USER is "ORACLE"
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string NONE
select a.SPID "Process ID",b.SID "Session ID" from V$process a,v$session b where a.ADDR=b.PADDR and b.SID=(select sid from v$mystat where rownum =1);
Process ID Session ID
------------------------ ----------
4216 1
[oracle@dba adump]$ more /u01/app/oracle/admin/orcl/adump/lote_ora_4216.*
/u01/app/oracle/admin/orcl/adump/lote_ora_4216.*: 没有那个文件或目录
[oracle@dba adump]$ more /u01/app/oracle/admin/orcl/adump/lote_ora_4216*
Audit file /u01/app/oracle/admin/orcl/adump/lote_ora_4216_1.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/
System name: Linux
Node name: dba.fei.com
Release: 2.6.32-358.el6.x86_64
Version: #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: lote
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 4216, image: oracle@dba.fei.com (TNS V1-V3)
Tue May 6 09:18:51 2014 +08:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
SQL> show parameter audit_s
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean FALSE
audit_syslog_level string
SQL> alter system set audit_sys_operations=true scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 398459248 bytes
Database Buffers 327155712 bytes
Redo Buffers 2867200 bytes
Database mounted.
Database opened.
SQL> show parameter audit_s
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE
audit_syslog_level string
SQL> select a.SPID "Process ID",b.SID "Session ID" from V$process a,v$session b where a.ADDR=b.PADDR and b.SID=(select sid from v$mystat where rownum =1);
Process ID Session ID
------------------------ ----------
4857 1
SQL> create table eygle as select * from dba_users;
create table eygle as select * from dba_users
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop table eygle purge;
Table dropped.
SQL> create table eygle as select * from dba_users;
Table created.
SQL> insert into eygle select * from dba_users;
45 rows created.
SQL> delete from eygle;
90 rows deleted.
SQL> commit;
Commit complete.
[oracle@dba adump]$ more /u01/app/oracle/admin/orcl/adump/lote_ora_4857*
Audit file /u01/app/oracle/admin/orcl/adump/lote_ora_4857_1.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/
System name: Linux
Node name: dba.fei.com
Release: 2.6.32-358.el6.x86_64
Version: #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: lote
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 4857, image: oracle@dba.fei.com (TNS V1-V3)
Tue May 6 09:35:12 2014 +08:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:35:14 2014 +08:00
LENGTH : '173'
ACTION :[19] 'ALTER DATABASE OPEN'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:35:29 2014 +08:00
LENGTH : '444'
ACTION :[289] 'SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big integer
', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_
PLUS_SHOW_PARAM,ROWNUM'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:36:22 2014 +08:00
LENGTH : '303'
ACTION :[148] 'select a.SPID "Process ID",b.SID "Session ID" from V$process a,v$session b where a.ADDR=b.PADDR and b.SID=(select sid from v$m
ystat where rownum =1)'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:36:22 2014 +08:00
LENGTH : '206'
ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:36:48 2014 +08:00
LENGTH : '201'
ACTION :[45] 'create table eygle as select * from dba_users'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[3] '955'
DBID:[10] '1373626619'
Tue May 6 09:36:48 2014 +08:00
LENGTH : '206'
ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:01 2014 +08:00
LENGTH : '176'
ACTION :[22] 'drop table eygle purge'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:01 2014 +08:00
LENGTH : '206'
ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:07 2014 +08:00
LENGTH : '199'
ACTION :[45] 'create table eygle as select * from dba_users'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:07 2014 +08:00
LENGTH : '206'
ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:20 2014 +08:00
LENGTH : '195'
ACTION :[41] 'insert into eygle select * from dba_users'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:20 2014 +08:00
LENGTH : '206'
ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:27 2014 +08:00
LENGTH : '171'
ACTION :[17] 'delete from eygle'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:27 2014 +08:00
LENGTH : '206'
ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:34 2014 +08:00
LENGTH : '159'
ACTION :[6] 'commit'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
Tue May 6 09:37:34 2014 +08:00
LENGTH : '206'
ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/5'
STATUS:[1] '0'
DBID:[10] '1373626619'
SQL> alter system set audit_trail=db scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 398459248 bytes
Database Buffers 327155712 bytes
Redo Buffers 2867200 bytes
Database mounted.
Database opened.
SQL> create user eygle identified by eygle;
User created.
SQL> grant connect,resource to eygle;
Grant succeeded.
SQL> audit all by eygle by access;
Audit succeeded.
SQL> audit select table,update table,insert table,delete table by eygle by access;
Audit succeeded.
SQL> audit execute procedure by eygle by access;
Audit succeeded.
SQL> conn eygle/eygle
Connected.
SQL> create table eygle as select * from dict;
Table created.
SQL> insert into eygle select * from dict where rownum<9;
8 rows created.
SQL> commit;
Commit complete.
SQL> delete from eygle where rownum <11;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> drop table eygle purge;
Table dropped.
select t.username,t.extended_timestamp,t.owner,t.obj_name,t.action_name from dba_audit_trail t where t.owner='EYGLE' order by timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- ----------------------------
EYGLE 06-5月 -14 09.50.11.366224 上午 +08:00 EYGLE EYGLE CREATE TABLE
EYGLE 06-5月 -14 09.50.27.755403 上午 +08:00 EYGLE EYGLE INSERT
EYGLE 06-5月 -14 09.51.00.539427 上午 +08:00 EYGLE EYGLE DELETE
EYGLE 06-5月 -14 09.51.12.141294 上午 +08:00 EYGLE EYGLE DROP TABLE
SQL> create table eygle as select * from dict where 1=0;
Table created.
begin
dbms_fga.add_policy(
object_schema => 'EYGLE',
object_name => 'EYGLE',
policy_name => 'AUDIT_EYGLE',
audit_condition => 'NULL',
audit_column => 'TABLE_NAME',
statement_types =>'SELECT,INSERT,UPDATE,DELETE');
END;
SQL> insert into eygle select * from dict where rownum <3;
insert into eygle select * from dict where rownum <3
*
ERROR at line 1:
ORA-28138: Error in Policy Predicate
begin
dbms_fga.drop_policy(
object_schema => 'EYGLE',
object_name => 'EYGLE',
policy_name => 'AUDIT_EYGLE');
END;
select t.username,t.password from dba_users t where t.username='EYGLE';
-----------------------------------------------------------------------------------------------------
SQL> create user proxy identified by proxy;
User created.
SQL> grant connect to proxy;
Grant succeeded.
SQL> alter user eygle grant connect through proxy;
User altered.
SQL> connect proxy[eygle]/proxy
Connected.
SQL> show user
USER is "EYGLE"
SQL> create table eygle (name varchar2(20));
Table created.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
10 rows selected.
SQL> conn proxy/proxy
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SQL> conn /as sysdba
Connected.
SQL> create user sudo identified by proxy;
User created.
SQL> alter user eygle grant connect through sudo;
User altered.
SQL> conn sudo[eygle]/proxy
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EYGLE TABLE
SQL> select * from eygle;
no rows selected
SQL> insert into eygle values('eygle');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from eygle;
NAME
--------------------------------------------------
eygle