对操作oracle数据库的用户进行安全权限控制
2007-11-12 10:33
一、采用数据库级别触发器控制非sys、system的alter/drop/truncate权限
--控制alter操作
CREATE OR REPLACE TRIGGER SYS.TRIGGER_ALTER
BEFORE ALTER ON DATABASE begin if ora_login_user not in ('SYS','SYSTEM') THEN Raise_application_error (-20001,'Please not do ALTER!'); end if; end; /
--控制drop操作
CREATE OR REPLACE TRIGGER SYS.TRIGGER_DROP
BEFORE DROP ON DATABASE begin if ora_login_user not in ('SYS','SYSTEM') THEN Raise_application_error (-20001,'Please not do DROP!'); end if; end; /
--控制truncate操作
CREATE OR REPLACE TRIGGER SYS.TRIGGER_truncate BEFORE truncate ON DATABASE begin if ora_login_user not in ('SYS','SYSTEM') THEN Raise_application_error (-20001,'Please not do TRUNCATE!'); end if; end; /
经过测试,一般用户进入数据库后无法进行alter、drop、truncate的操作
限制了对数据的破坏性操作,若进行上述操作,系统会产生
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Please do not DROP!
之类错误
PS:未限制create操作,因为1、create操作不会对生产数据产生破坏性影响;
2、若限制create操作,可能会导致create index的操作失败
======================================================
二、调整tssa用户权限
REVOKE "DBA" FROM "TSSA";
GRANT "CONNECT" TO "TSSA";
将dba权限从tssa用户移除,并重新对其赋予connect权限
经过业务测试,未发现引起业务异常
=======================================================
三、创建只读用户bzwh
CREATE USER "BZWH" PROFILE "DEFAULT" IDENTIFIED BY "bzwh" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK; GRANT CREATE SESSION TO "BZWH" GRANT SELECT ANY DICTIONARY TO "BZWH" GRANT SELECT ANY SEQUENCE TO "BZWH" GRANT SELECT ANY TABLE TO "BZWH"
http://hi.baidu.com/xoy2129/blog/item/0afc9251de35ad2742a75b9b.html
|