Securing the Oracle Database

1. Securing the Oracle Database 中原大學應用數學系 劉 立 民

2. Introducation • Passwords and Users • Oracle Password Management Features • Default Oracle Users • Privileges, Grants, Roles, and Views • Oracle9i Security Products and Features

3. Password features

4. Password enhancements • User PROFILE • Default profile • Create/alter profile SQL> describe DBA_PROFILES; 名稱 空值? 類型 ----------------------------------------------------------------- -------- ----------------------- PROFILE NOT NULL VARCHAR2(30) RESOURCE_NAME NOT NULL VARCHAR2(32) RESOURCE_TYPE VARCHAR2(8) LIMIT VARCHAR2(40) SQL> select * from DBA_PROFILES; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ----------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

5. Password enhancements (Con’t) • Resource_type • Password • Kernel SQL> select * from dba_profiles where resource_type = 'PASSWORD' PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- --------- DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED 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 已選取 7 個資料列. • Execute ultpwdmg.sql at $ORACLE_HOME/rdbms/admin @”D:\Oracle\ora92\rdbms\admin\utlpwdmg.sql”

6. Password enhancements (Con’t) SQL> select * from dba_profiles where resource_type = 'PASSWORD' PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- --------- DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3 DEFAULT PASSWORD_LIFE_TIME PASSWORD 60 DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800 DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006 DEFAULT PASSWORD_GRACE_TIME PASSWORD 10 已選取 7 個資料列.

7. Aging and expiration • Password_life_time • Number of days the same password can be used for authentication • Password_grace_time • Number of days in the grace period Ex: Password_life_time = 90 Password_grace_time = 15 After day 90+15, the account will be locked.

8. Password History • Parameters prevents users from avoiding password expiration by changing a password and changing it back to the original password. • Password_reuse_time • Number of days until a password cannot be resued. • Password_reuse_max • Number of password changes required before the current password can be reused. • Setting both to UNLIMITED allows passwords to be reused immediately.

9. Account locking • Failed_login_attempts • Number of attempts to log in that can fail before the user account is locked • Password_lock_time • Amount of time an account remains locked after the specified number of consecutive failed login attempts is reached • If set to UNLIMITED, the account will no unlock automatically.

10. Password profile parameters

11. Password profile parameters (Con’t)

12. Enabling password parameters • Create a user-defined profile create profile MY_PROFILE limit failed_login_attempts 5 password_lock_time .5 password_reuse_max UNLIMITED password_life_time 90 password_reuse_time 60 password_verify_function MY_PASSWORD_FUNCT password_grace_time 15; • Create users with specific profile create user NELSON identified by nel_123 default tablespace USERS temporary tablespace TMP password expire profile MY_PROFILE;

13. Default Oracle Users • Several default users are created when you create your database. • Check these users via DBA_USERS describe DBA_USERS • Lock those unused users • Verify users’ granted_role, and privileges select * from dba_role_privs where grantee = 'HR'; select * from dba_sys_privs where grantee = 'HR'; select * from dba_tab_privs where grantee = 'HR‘;

14. DBA_ views that display user privileges

15. Database objects

16. Object privileges

17. Controlling user access • Product-level Security • Users access can be restricted based on the SQL*Plus product usage. • DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. • PRODUCT_USER_PROFILE table consists of the following columns:

18. PRODUCT_USER_PROFILE • PRODUCT_USER_PROFILE table is owned by SYSTEM • Commands that can be applied in PRODUCT_USER_PROFILE table includes: ALTER, AUDIT, ANALYZE, CREATE, DELETE, DROP, INSERT, LOCK, NOAUDIT, RENAME, SELECT, UPDATE, VALIDATE, etc. • Creating the PRODUCT_USER_PROFILE table by Execute pupbld.sql at $ORACLE_HOME/sqlplus/admin @”D:\Oracle\ora92\sqlplus\admin\pupbld.sql” • Example: insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'CREATE', 'DISABLED'); insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'UPDATE', 'DISABLED'); insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'DELETE', 'DISABLED'); insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'INSERT', 'DISABLED'); insert into product_user_profile(product, userid, attribute, char_value) values('SQL*Plus', 'APPS', 'SELECT', 'DISABLED');

19. Preventing access using PL/SQL • The DELETE privilege disabled for user APPS can easily be executed through a PL/SQL block. • This can be avoided by removing the PL/SQL block creation access itself. insert into product_profile (product, userid, attribute, char_value) values ('SQL*Plus', 'APPS', 'DECLARE', 'DISABLED'); insert into product_profile (product, userid, attribute, char_value) values ('SQL*Plus', 'APPS', 'BEGIN', 'DISABLED'); • PRODUCT_PROFILE is a synonym of PRODUCT_USER_PROFILE exists to ensure backwards compatibility.

20. With Grant Option • DBAs can grant different privileges to different users. SQL> CREATE USER TOM IDENTIFIED BY ICE; User created. SQL> GRANT CREATE SESSION TO TOM; Grant succeeded. SQL> GRANT CREATE TABLE TO TOM; Grant succeeded. SQL> GRANT CREATE SEQUENCE TO TOM; Grant succeeded. • DBAs can also grant users “with grant option” to allow them to grant access to other users without any action on the DBA parts. SWL> CREATE TABLE TEST (COL NUMBER); Table created. SQL> GRANT SELECT, UPDATE ON TEST TO TOM WITH GRANT OPTION; Grant succeeded.

21. With Admin Option • DBA can grant users administration privileges using “with admin option” SQL> GRANT CREATE TABLE TO TOM WITH ADMIN OPTION; Grant succeeded. • Revoke uses’ administration privileges SQL> REVOKE CREATE TABLE FROM TOM; Revoke succeeded. • Privileges granted by TOM still retain after DBA revoke TOM’s administration privileges.

22. Using Role • Why using roles • Grant privileges to individual directly can be very tedious. • Example, 100 objects with 100 users needs to make 10,000 grants; one people leave the company needs to make 100 revokes. • Steps of using roles • Group users into categories • Define one or more roles for each category • Grant proper privileges to each role • Assign roles to users in each category

23. Create roles • Assume table EMP has two kinds of users: query EMP and update EMP. SQL> create role report_writer identified by rep123; Role created. SQL> create role data_changer identified by your_pwd; Role created. • Grant privileges to roles SQL> GRANT SELECT ON EMP TO REPORT_WRITER; Grant succeeded. SQL> GRANT UPDATE, DELETE, INSERT ON EMP TO DATA_CHANGER; Grant succeeded.

24. Assign roles to users • Once we create roles with proper privileges, we can assign roles to users. SQL> GRANT REPORT_WRITER TO TOM; Grant succeeded. SQL> GRANT DATA_CHANGER TO HENRY; Grant succeeded. • Revoke roles from users to maintain the proper privileges SQL> REVOLE DATA_CHANGE FROM HENRY; Revoke succeeded. • DBA_ROLES SQL> select * from dba_roles;

25. Oracle-supplied roles • CONNECT • Gives the user the ability to access the database • RESOURCE • Gives the user the ability to create objects and use space in the database • DBA • Gives the user administrative privileges in the database

26. Querying roles

27. Set roles • Default roles will be enabled automatically and accessible when users connect to the database • For roles requiring password, users need to use set role to enable the role privileges. SQL> set role DATA_CHANGE identified by your_pwd;

28. Using Views to Manage Privileges • Rather than granting users privileges on a particular table, you can give them access to a view of the table. • Views add two more levels of security: • A view can limit access to only selected columns of the base table. • A view can provide value-based security for the information in a table. Thus a WHERE clause in the definition of a view can display only selected rows of a base table. • The user need not be given privileges on base objects underlying the view.

29. An Example of a View

30. View privileges • View creation SQL> CREATE VIEW STAFF AS ( 2 SELECT EMPNO, ENAME, JOB, MGR, DEPTNO FROM EMP 3 ); View created. • Grant privileges to roles SQL> GRANT SELECT ON STAFF TO REPORT_WRITER; Grant succeeded.

31. Row Level Security • Two more effective approaches to implement row level security: • Virtual Private Database (VPD), in which you create your own implementation of row level security; and • Label-based access control, in which you customize a ready-made VPD policy to accomplish this.

32. Virtual Private Database (VPD) • Virtual Private Database is the ability to perform query modification based on a security policy you have defined in a package, and associated with a table, view. • You can implement VPD by writing a stored procedure to append a SQL predicate to each SQL statement to control row level access for that statement. • For example, SQL SELECT * FROM emp; will be modified, say SELECT * FROM emp where deptno = 10;

33. Virtual Private Database: Customers see Only Their Own Orders

34. Example of VPD • Choose the tables or views to protect at the row level SQL> create table employee ( 2 trndate date, 3 credit_val number(12,2), 4 debit_val number(12,2), 5 trn_type varchar2(10), 6 cost_center varchar2(10) ) tablespace users; Table created. • Define the business rules that will be followed for accessing data in these tables • Create a security context to manage application sessions SQL> create or replace context vpd_test using set_vpd_context; Context created.

35. Example of VPD (Con’t) • Create a procedure or function to manage setting of the security context for users SQL> CREATE TABLE emp ( empno NUMBER(4) NOT NULL, 2 ename VARCHAR2(10), 3 job VARCHAR2(9), 4 mgr NUMBER(4), 5 sal NUMBER(7,2), 6 deptno NUMBER(2)); Table created. • Define the business rules that will be followed for accessing data in these tables • Create a security context to manage application sessions CREATE OR REPLACE CONTEXT HR USING HR.Context_Package;

36. Example of VPD (Con’t) • Create a procedure or function to manage setting of the security context for users CREATE OR REPLACE PACKAGE Context_Package AS PROCEDURE Set_Context; • Write a package to generate the dynamic access predicates for access to each table CREATE OR REPLACE PACKAGE BODY Context_Package IS PROCEDURE Set_Context ISThis_PERSON_ID NUMBER;BEGINDbms_Session.Set_Context('HR','SETUP','TRUE');IF SUBSTR(SYS_CONTEXT('USERENV','SESSION_USER'),1,2)='OE' : Dbms_Session.Set_Context('HR','SETUP','FALSE'); END Set_Context; END Context_Package;

37. Example of VPD (Con’t) • Make the packages public by executing the following sql. GRANT EXECUTE ON hr.Context_Package to Public; • Automate the setting of the security context CREATE OR REPLACE TRIGGER HR.Set_Security_Context AFTER LOGON ON DATABASE BEGINHR.Context_Package.Set_Context; END;

38. Label-based access control • Label-based access control allows organizations to assign sensitivity labels to data rows. • Control access to data based on those labels, and ensure that data is marked with the appropriate sensitivity label. • Oracle9i Label Security is not installed by default with the Oracle9i Enterprise Edition.

39. Oracle Label Security

40. Oracle Label Security • Oracle Label Security is built on the Virtual Private Database toolkit, • Requires no programming whatsoever. • Oracle Label Security delivers a data dictionary and administrative tools you can use to • construct valid labels, • set user label authorizations and privileges, and • apply the resulting Oracle Label Security policy to tables and schemes.

41. Oracle9i Security Products • Oracle9i Standard Edition • Integrity • Authentication and Access Controls in Oracle9i • Privileges • Roles • Auditing • Views, Stored Program Units, Triggers • Data Encryption • High Availability • Proxy Authentication in Oracle9i

42. Oracle9i Security Products (Con’t) • Oracle9i Enterprise Edition • Internet Scale Security Features • Application Security • Virtual Private Database in Oracle9i • Secure Application Role • Fine-Grained Auditing • Oracle Auditing for Three-Tier Applications • Java Security Implementation in the Database

43. Other Oracle security products • Oracle Advanced Security • Oracle Label Security • Oracle Internet Directory • Oracle Net Services • Oracle9i Application Server

44. References • Oracle Security Handbook by Theriault • Oracle SQL*Plus: The Definitive Guide by Gennick • Oracle 9i: Security Overview (9.2) by Oracle • Oracle 9i Application Server: Security Guide (9.0.2) by Oracle • Oracle 9i: Database Administration Guide (9.2) by Oracle • Oracle 9i Net Services: Administrator’s Guide (9.2) by Oracle Thank you very much