Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI Oracle Security Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Where Is Security Enforced? • Application • Applications often have the need to be security-aware. • Some commercial applications use the One Big Application user model. The SQL that users submit is restricted by the application. • Are application users also database users. (Does the application have a user table?) What user is used to connect to Oracle? • Applications whose users are not database users have no choice but to manage security themselves. • Security best practices recommend authenticating the user with the database, not using a shared applid. Sharing an applid compromises security because the identity of the user is unknown to the database • Ad-hoc tools like SQLPlus, Access, Excel or Toad do not restrict the SQL and bypass application security. • Universal implementation of security requires that every application module correctly implement security – difficult and expensive.

3. Where Is Security Enforced? • Database • Applications whose users are also database users can choose to implement application or database security. • Database security cannot be bypassed, even with ad hoc tools. • Oracle audit features can record who does what inside the database. • Database roles can be set up for different classes of users, allowing and restricting access as appropriate. • Database roles can be derived from enterprise roles maintained outside of Oracle (LDAP with Oracle Identity Management), reducing administrative burden.

4. Basic Oracle Security • By What Authority? • Users • Roles • Grant and revoke • Synonyms 4

5. CREATE USER • CREATE USER • Create user username identified by password; • Create user username identified externally; • Changing passwords • Alter user username identified by password; • Password Management – determined by DBA created profiles. • Password lifetime • Grace period • Account lock rules • Password reuse rules • Moving to another user, connect sqlplus command • CONNECT • CONNECT username • CONNECT username / password 5

6. Password Management • CREATE PROFILE prof LIMIT FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 30 PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 3; ALTER USER johndoe PROFILE prof; • ALTER USER johndoe ACCOUNT UNLOCK; • CREATE USER jbrown IDENTIFIED BY zX83yT ... PASSWORD EXPIRE;

7. CREATE ROLE • Roles manage sets of privileges. • CREATE ROLE • Create role rolename; • Standard Oracle Roles • CONNECT – connect to database and perform very limited functions. • RESOURCE – for basic users. • DBA – all system privileges. • Maximum roles allowed is set at startup, max_enabled_roles parameter (30 on phoenix). • Roles can have passwords, but do not by default. 7

8. Role-based Security Model • Roles are a named set of privileges • Resolves delete anomolies like dropping a user loosing all the security rules. • Users are never directly assigned privileges. • More than one role can be active.

9. GRANT and REVOKE • Grant for object privileges GRANT {privilege, … | ALL} [ (column,…) ] on object to {user | role} [with grant option] [with hierarchy option]; • Grant for system privileges GRANT {system privilege | role | ALL} to {user | role} [, {user | role}, …] [identified by password] [with admin option]; • Revoke takes privileges from roles or users. REVOKE {system privilege | role | ALL} [, {system privilege | role | ALL} …] from {user | role} [, {user | role}, …]; 9

10. Synonyms, Examples, Other • What you can grant to other users • Tables: alter, references, index, on commit refresh, query rewrite, all • PL/SQL Procedures and Functions: execute • Sequences: select, alter • Synonyms provide for another name for an object. (location independence) • CREATE [PUBLIC] SYNONYM synonymFOR SCHEMA.OBJECT[@LINK]; • Examples: • CREATE ROLE MYTEAM; • GRANT MYTEAM TO JOE, TOM, SUE; • GRANT SELECT ON MYTABLE TO MYTEAM; • GRANT UPDATE (COL1) ON MYTABLE TO MYTEAM; • CREATE PUBLIC SYNONYM TAB1 FOR MYSCHEMA.MYTABLE; • Advanced Options for Security by User • Virtual private database (VPD) adds a where clause to all commands issued by the user to restrict data to only his view of the database. • Oracle Label Security uses security labels on all rows, users only have access to those in their hierarchy. 10

11. Enterprise-level Considerations Application access is strictly enforced using roles. Roles are defined based on function, not operation. For example, a role APPL_USER_WRITE_ROLE means that the user can modify application data. The role may include SELECT, EXECUTE grants as well as INSERT, UPDATE, DELETE. Role APPL_USER_READONLY_ROLE may also include some INSERT/UPDATE privileges to activity logs, etc., but the user cannot modify application data. Public synonyms cannot be used when there is more than one instance of an application in a database instance. This often happens for test environments: string, integration, user acceptance, capacity, etc. Batch jobs also require roles, such as APPL_BATCH_WRITE_ROLE. Under no circumstances should any user or job ever login as the schema owner.

12. Advanced Security – VPDs • Virtual Private Databases - VPDs • VPDs are an advanced security topic that requires fluency in several different areas including contexts, packages, triggers and SQL. • The grants discussed previously control access at an object-level. You can grant SELECT to a VIEW as an example. • VPD implements what is called fine-grained access control. Fine-grained access control means that security is implemented at a row-level. For example, the following querySELECT * FROM emp; can be changed by a VPD security policy to add a predicateSELECT * FROM emp WHERE division = 'RETAIL'; • VPD can also be set up to add predicates based on what columns are mentioned.

13. Acknowledgements • Loney, Oracle Database 10g The Complete Reference