表空间限额
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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24756465/viewspace-717790/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24756465/viewspace-717790/