CREATE ROLE data_analyst;
GRANT SELECT ON schema_name.table_name TO data_analyst;
GRANT data_analyst TO user_name;
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER_NAME';
CREATE VIEW v_employee_restricted AS
SELECT emp_id, name, department FROM employees;
GRANT SELECT ON v_employee_restricted TO user_name;
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_POLICY',
function_schema => 'SEC_ADMIN',
policy_function => 'AUTH_EMP',
statement_types => 'SELECT,UPDATE,DELETE'
);
END;
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 10
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;
AUDIT EXECUTE ANY PROCEDURE BY ACCESS;
AUDIT ALL BY user_name BY ACCESS;
SELECT * FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP DESC;
ALTER USER app_user GRANT CONNECT THROUGH app_proxy_user;
SELECT username, account_status, expiry_date
FROM dba_users
WHERE account_status != 'LOCKED'
ORDER BY expiry_date;
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER_NAME';
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER_NAME';
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
column_name => 'SALARY',
policy_name => 'redact_emp_sal',
function_type => DBMS_REDACT.PARTIAL,
function_parameters => DBMS_REDACT.PARTIAL_PARAMETERS(
'VVVVFVVVVFVVVVFVVVV', '9', '1', '0', '12')
);
END;
通过实施这些最佳实践,您可以显著提高Oracle数据库的安全性,确保数据访问既满足业务需求又符合安全要求。