表空间限额

SQL> create user u1 identified by u1;

User created.

SQL> grant create session , create table to u1;

Grant succeeded.

SQL> conn scott/seker
Connected.
SQL> grant select on emp to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> create table t1 as select * from scott.emp;
create table t1 as select * from scott.emp
                                       *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL> conn / as sysdba
Connected.
SQL> alter user u1 quota 1m on users;

User altered.

SQL> conn u1/u1
Connected.
SQL> create table t1 as select * from scott.emp;

Table created.

SQL>
SQL> insert into t1 select * from t1;

7168 rows created.

SQL> /
insert into t1 select * from t1
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

SQL>

SQL> select * from user_ts_quotas;

TABLESPACE_NAME      BYTES  MAX_BYTES   BLOCKS MAX_BLOCKS DRO
--------------- ---------- ---------- ---------- ---------- ---
USERS     1048576    1048576      128 128 NO

SQL>

SQL> conn / as sysdba
Connected.
SQL> alter user u1 quota 0 on users;  0既是不可以使用

User altered.

SQL> conn u1/u1
Connected.
SQL> select * from user_ts_quotas;

TABLESPACE_NAME      BYTES  MAX_BYTES   BLOCKS MAX_BLOCKS DRO
--------------- ---------- ---------- ---------- ---------- ---
USERS     1048576     0      128   0 NO

SQL>

创建用户后,授予resource权限会顺带UNLIMITED TABLESPACE(所有表空间的使用权)
应该回收,再对用户默认表空间设置使用空间权限

SQL> revoke UNLIMITED TABLESPACE from scott;

Revoke succeeded.

SQL> alter user scott quota unlimited on users;

User altered.

SQL>

使用profile限制用户资源

SQL> select * from dba_profiles where RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS' and PROFILE='DEFAULT';

PROFILE          RESOURCE_NAME   RESOURCE LIMIT
------------------------------ -------------------------------- -------- --------------------
DEFAULT          FAILED_LOGIN_ATTEMPTS  PASSWORD 10

SQL>

SQL> create profile p1 limit FAILED_LOGIN_ATTEMPTS 2;

Profile created.

SQL> alter user u1 profile p1;

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE from dba_users where username='U1';

USERNAME   ACCOUNT_STATUS      LOCK_DATE PROFILE
---------- -------------------------------- --------- ------------------------------
U1    OPEN           P1

SQL> conn u1/sldkfj
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn u1/sldkfj
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> conn / as sysdba
Connected.
SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE,PROFILE from dba_users where username='U1';

USERNAME   ACCOUNT_STATUS      LOCK_DATE PROFILE
---------- -------------------------------- --------- ------------------------------
U1    LOCKED(TIMED)      02-APR-11 P1

SQL>

SQL> select * from dba_profiles where RESOURCE_NAME like '%PASS%' and PROFILE='DEFAULT';

PROFILE          RESOURCE_NAME   RESOURCE LIMIT
------------------------------ -------------------------------- -------- --------------------
DEFAULT          PASSWORD_LIFE_TIME  PASSWORD UNLIMITED
DEFAULT          PASSWORD_REUSE_TIME  PASSWORD UNLIMITED
DEFAULT          PASSWORD_REUSE_MAX  PASSWORD UNLIMITED
DEFAULT          PASSWORD_VERIFY_FUNCTION  PASSWORD NULL
DEFAULT          PASSWORD_LOCK_TIME  PASSWORD UNLIMITED
DEFAULT          PASSWORD_GRACE_TIME  PASSWORD UNLIMITED

6 rows selected.

SQL>

****** 审计 ******
审计不能长时间开着,会大量消耗系统资源.一般就是开一会,再关闭.在生产库上线之前开启,跟踪应用有无逻辑错误.上线后就关闭.

一定会被审计的操作(默认只跟踪这几个操作):
conn / as sysdba
startup
shutdown

审计相关参数:
audit_file_dest='D:\ORACLE\ADMIN\ORA10\ADUMP' --审计线索保存的位置
audit_sys_operations=true  --起用对SYSDBA和SYSOPER的附加审计信息,将sys的所有操作都记录到外部平面文件中.一般来说没有什么必要记录.这个没有必要打开audit_trail.

audit_trail={ none | os | db | db,extended | xml | xml,extended }  决定将用户的审计线索存在的位置,可以存放在数据库中或外部文件中.
None ：是默认值，不做审计；
os : 将审计信息记录于操作系统！windows记录于事件查看器，unxi记录于audit_file_dest所指定路径
DB ：将审计线索记录在数据库中，aud$.有的管理员会将这个表移动到别的表空间,以免占用系统表空间.但是不推荐这样作.
db,extended ：extended选项会在审计中增加SQLBIND 和 SQLTEXT的信息
xml ：将审计线索记录在xml文件中audit_file_dest参数所指定的目录

SQL> alter system set audit_trail=os scope=spfile;
SQL> startup force
SQL> audit session;
SQL> conn scott/tiger

查询审计线索中的action操作编码表:
SQL> select * from audit_actions;  

1.基本数据库审计:
语句审计会话连接:
audit session; --审计用户连接或断开连接
noaudit session;
audit session by scott; -- 指定用户
noaudit session by scott;

语句审计(审计create table,drop table,truncate table):
audit table;
noaudit table;

语句审计(审计指定用户的操作):
audit table by scott;
noaudit table by scott;

语句审计(审计失败的操作):
audit table by scott whenever not successful;
noaudit table by scott;

语句审计(审计DML语句):
audit insert table by scott by access;
audit select any table by scott by session;

(BY ACCESS每次都生成一条审计记录,
 BY SESSION相同操作只生成一条审计记录)

查看用户被启用的语句审计的选项:
select audit_option,failure,success,user_name from dba_stmt_audit_opts;
 
特权审计(使用了特定的系统权限才会触发审计线索):
audit create any table;
audit create any table by scott;
audit drop any table by scott;

查看用户被启用的特权审计的选项:
select privilege,user_name from dba_priv_audit_opts order by user_name;

对象审计(跟踪指定对象权限的操作):
audit delete on scott.e;
audit select on scott.emp by access;

查看用户被启用的对象审计的选项:
select * from dba_obj_audit_opts where wner='SCOTT';

-- 取消所有statement审计
noaudit all;
-- 取消所有权限审计
noaudit all privileges;
-- 取消所有对象审计
noaudit all on default;

清除审计信息:
delete from sys.aud$;
delete from sys.aud$ where obj$name='EMP';
commit;