数据库安全

--1.创建角色并给角色授权进行授权


select 'GRANT '||PRIVILEGE || ' TO WCS_ROLE;' from dba_sys_privs WHERE GRANTEE='WCS_ROLE';


--查看当前session内的权限,注意其可能不是最新的
SELECT * FROM Session_Privs;
--查看角色权限
select * from DBA_sys_privs WHERE grantee='WCS_ROLE';
--查看当前用户权限
SELECT * FROM user_role_privs;
SELECT * FROM Dba_Role_Privs WHERE grantee='WCS_ROLE';


--创建角色并给角色授权进行授权
CREATE ROLE wcs_role;
--GRANT CONNECT,RESOURCE,CTXAPP TO wcs_role;
--REVOKE DBA FROM rudy;
--DROP ROLE wcs_role;
GRANT wcs_role TO rudy;
GRANT CREATE VIEW TO wcs_role;
GRANT CREATE MATERIALIZED VIEW TO WCS_ROLE;
GRANT CREATE SESSION TO WCS_ROLE;
GRANT RESUMABLE TO WCS_ROLE;
GRANT CREATE TYPE TO WCS_ROLE;
GRANT CREATE TRIGGER TO WCS_ROLE;
GRANT CREATE VIEW TO WCS_ROLE;
GRANT CREATE JOB TO WCS_ROLE;
GRANT CREATE EVALUATION CONTEXT TO WCS_ROLE;
GRANT CREATE INDEXTYPE TO WCS_ROLE;
GRANT CREATE  SEQUENCE TO WCS_ROLE;
GRANT CREATE  TABLE TO WCS_ROLE;
GRANT MANAGE SCHEDULER TO WCS_ROLE;
GRANT DROP PUBLIC DATABASE LINK TO WCS_ROLE;
GRANT UNLIMITED TABLESPACE TO WCS_ROLE;
GRANT DEBUG CONNECT SESSION TO WCS_ROLE;
GRANT ON COMMIT REFRESH TO WCS_ROLE;
GRANT CREATE ANY INDEXTYPE TO WCS_ROLE;
GRANT EXECUTE ANY INDEXTYPE TO WCS_ROLE;
GRANT CREATE TYPE TO WCS_ROLE;
GRANT CREATE PROCEDURE TO WCS_ROLE;
GRANT FORCE TRANSACTION TO WCS_ROLE;
GRANT CREATE SEQUENCE TO WCS_ROLE;
GRANT CREATE VIEW TO WCS_ROLE;
GRANT DROP PUBLIC SYNONYM TO WCS_ROLE;
GRANT CREATE TABLE TO WCS_ROLE;
GRANT CREATE  TRIGGER TO WCS_ROLE;
GRANT CREATE PUBLIC SYNONYM TO WCS_ROLE;
GRANT CREATE SYNONYM TO WCS_ROLE;
GRANT FLASHBACK ARCHIVE ADMINISTER TO WCS_ROLE;
GRANT CREATE MATERIALIZED VIEW TO WCS_ROLE;
GRANT CREATE PUBLIC DATABASE LINK TO WCS_ROLE;
GRANT CREATE DATABASE LINK TO WCS_ROLE;
GRANT CREATE ANY SYNONYM TO WCS_ROLE;
GRANT EXECUTE ANY EVALUATION CONTEXT TO WCS_ROLE;
GRANT EXECUTE ANY LIBRARY TO WCS_ROLE;
GRANT EXECUTE ANY PROCEDURE TO WCS_ROLE;
GRANT EXECUTE ANY PROGRAM TO WCS_ROLE;
GRANT EXECUTE ANY OPERATOR TO WCS_ROLE;
GRANT EXECUTE ANY INDEXTYPE TO WCS_ROLE;
GRANT EXECUTE ANY ASSEMBLY TO WCS_ROLE;
GRANT EXECUTE ANY CLASS TO WCS_ROLE;
GRANT EXECUTE ANY TYPE TO WCS_ROLE;
--grant unlimited tablespace to rudy;


--禁止某一用户执行ddl
CREATE OR REPLACE TRIGGER DDL_DENY
  BEFORE CREATE OR ALTER OR DROP OR TRUNCATE OR GRANT OR REVOKE ON DATABASE
DECLARE
BEGIN


  IF ORA_LOGIN_USER = 'RUDY' THEN
    /*IF ORA_SYSEVENT = 'CREATE' THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'You have no permission to create object:' ||
                              ORA_DICT_OBJ_OWNER || '.' ||
                              ORA_DICT_OBJ_NAME || ' ' ||
                              ', please connect to admin');
    ELS
*/
IF ORA_SYSEVENT = 'ALTER' THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'You have no permission to alter object:' ||
                              ORA_DICT_OBJ_OWNER || '.' ||
                              ORA_DICT_OBJ_NAME || ' ' ||
                              ', please connect to admin');
    ELSIF ORA_SYSEVENT = 'DROP' THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'You have no permission to drop object:' ||
                              ORA_DICT_OBJ_OWNER || '.' ||
                              ORA_DICT_OBJ_NAME || ' ' ||
                              ', please connect to admin');
    ELSIF ORA_SYSEVENT = 'TRUNCATE' THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'You have no permission to truncate object:' ||
                              ORA_DICT_OBJ_OWNER || '.' ||
                              ORA_DICT_OBJ_NAME || ' ' ||
                              ', please connect to admin');
    ELSIF ORA_SYSEVENT = 'GRANT' THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'You have no permission grant privilege' ||
                              ', please connect to admin');
    ELSIF ORA_SYSEVENT = 'REVOKE' THEN
      RAISE_APPLICATION_ERROR(-20001,
                              'You have no permission revoke privilege' ||
                              ', please connect to admin');
    END IF;
  END IF;


EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;






--2. 查看用户使用默认密码以及状态


select * FROM dba_users_with_defpwd ORDER by 1;


SELECT * FROM dba_users WHERE ACCOUNT_STATUS='OPEN';


SELECT * FROM dba_users WHERE username IN (select username FROM dba_users_with_defpwd);


--用户密码默认180天过期
SELECT * FROM Dba_Profiles WHERE PROFILE='DEFAULT';


--密码永不过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


--3.审计


--查看审计是否打开
show parameter audit


--打开sys用户的审计,并打开外部审计
Alter system set audit_sys_operations=true scope=spfile;
alter system set audit_trail=db,extended scope=spfile;
--audit table


--查看当前审计的内容
SELECT user_name, audit_option, success, failure from sys.dba_stmt_audit_opts


revoke dba from rudy;
grant connect,resource,unlimited tablespace  to wcs_p;


--查看当前用户权限
SELECT * FROM session_privs;






AUDIT CREATE JOB;
AUDIT CREATE LIBRARY;
AUDIT TABLE;
AUDIT ALTER TABLE;
AUDIT CREATE TABLE;


NOAUDIT DROP ANY PROCEDURE;
NOAUDIT ALTER ANY PROCEDURE;
NOAUDIT CREATE ANY PROCEDURE;
NOAUDIT CREATE PROCEDURE;
NOAUDIT PROCEDURE;


AUDIT CREATE VIEW;
AUDIT CREATE ANY VIEW;
AUDIT VIEW;
AUDIT DROP ANY VIEW;


AUDIT CREATE MATERIALIZED VIEW;
AUDIT CREATE ANY MATERIALIZED VIEW;
AUDIT ALTER ANY MATERIALIZED VIEW;
AUDIT MATERIALIZED VIEW;
AUDIT DROP ANY MATERIALIZED VIEW;


AUDIT CREATE ANY INDEX;
AUDIT ALTER ANY INDEX;
AUDIT INDEX;
AUDIT DROP ANY INDEX;


AUDIT CREATE TYPE;
AUDIT CREATE ANY TYPE;
AUDIT ALTER ANY TYPE;
AUDIT TYPE;
AUDIT DROP ANY TYPE;


--4.端口


--查看监听端口是什么,以及状态


lsnrctl status


--关闭监听


lsnrctl stop


--修改监听端口
listener.ora


--查看端口是否被占用
netstat -apn|grep  31564


--修改数据库监听端口,注意如果是asm,则asm上也要执行这个操作
alter system set local_listener="(address=(protocol=tcp)(host=asm)(port=31564))" scope=both;


--查看数据库监听端口
show parameter local_listener


--修改客户端监听端口
tnsnames.ora


--数据库端配置
asm_centos01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = asm_centos01)(PORT = 31564))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
--asm实现配置
asm_centos02 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = asm_centos01)(PORT = 31564))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )


--打开监听
lsnrctl start


--受影响网络
--112.124.109.24 运营一个服务,stage一个服务
--112.124.2.225 一个prd服务
--112.124.2.252 一个prd服务
--112.124.41.131 一个stage服务,一个prd服务
--192.168.10.191 dev服务


--设置密码


set password
set current_listener listener
change_password
save_config


--设置密码后，远程操作将会因缺失密码而出现失败,但本地操作还是有需要密码的