CSCI N311 - Computer and Information Science

CSCI N311 - Computer and Information Science

Department of Computer and Information Science, School of Science, IUPUI Oracle Security Dale Roberts, Lecturer Computer Science, IUPUI E-mail: [email protected] Dale Roberts 1 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 . Dale Roberts 2 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. Dale Roberts 3 Basic Oracle Security By What Authority? Users Roles

Grant and revoke Synonyms Dale Roberts 4 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 Dale Roberts 5 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; Dale Roberts 6 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. Dale Roberts 7 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. Dale Roberts 8 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}, ]; Dale Roberts 9 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 synonym FOR 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. Dale Roberts 10 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. Dale Roberts 11 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. Finegrained access control means that security is implemented at a rowlevel. For example, the following query SELECT * FROM emp; can be changed by a VPD security policy to add a predicate

SELECT * FROM emp WHERE division = 'RETAIL'; VPD can also be set up to add predicates based on what columns are mentioned. Dale Roberts 12 Acknowledgements Loney, Oracle Database 10g The Complete Reference Dale Roberts 13

Recently Viewed Presentations

  • Do Now Day 2 - Weebly

    Do Now Day 2 - Weebly

    Do Now Day 2. Draw the meaning of "survival of the fittest". In one sentence explain the endosymbiont hypothesis. What is 1 of the 4 requirements of natural selection? (Hint: the video with Charles Darwin)
  • Explorations in Music, The Arts, and Ideas- "On the ...

    Explorations in Music, The Arts, and Ideas- "On the ...

    Explorations in Music, The Arts, and Ideas- "On the Relationship of Analytical Theory to Performance and Interpretation" Eugene Narmour - University of Pennsylvania Arpi Mardirossian Wednesday, April 30, 2003 Introduction Music theory should teach how different interpretations alter the listener's...
  • SCHOOL REDESIGN IN KANSAS Kansas leads the world

    SCHOOL REDESIGN IN KANSAS Kansas leads the world

    Changes need to be made to address school culture. New dynamic roles for counselor and social workers. Collaboration between schools and businesses. Reorganize schools around students, not the system. Community service needs to play a bigger role. Kansas children need...
  • Practise Exam Questions - Alness Academy History

    Practise Exam Questions - Alness Academy History

    Practise Exam Questions World War One Exam Question Exam Question Describe fully how women helped the war effort. 8 marks Source C is a photograph from the Imperial war Museum collection of a tank in action in 1916.
  • WIDA, ELP Standards and Core English Language Instruction

    WIDA, ELP Standards and Core English Language Instruction

    In addition to using sound instructional strategies, the WIDA ELP Standards are part of the framework to building ELs' Cognitive Academic Language Proficiency. There are 5 WIDA ELP standards with related components. In the next few slides we will take...
  • Different Types of SS - University of Ottawa

    Different Types of SS - University of Ottawa

    Different Types of SS. SS stands for the sum of squared deviations. The variance is the mean SS (i.e., MS). Most statistical analyses are about how to explain SS in dependent variable (DV) by independent variables (IVs). SS in DV...
  • Building Disaster Recovery Plans with Azure Site Recovery ...

    Building Disaster Recovery Plans with Azure Site Recovery ...

    Building Disaster Recovery Plans with Azure Site Recovery, Hyper-V Replica, SQL Always ON and Desired State Configuration. Abhishek Agrawal, Senior Lead Program Mgr. ... Scales with Cloud deployments while keeping RTO low. We are just getting started. As DSC adoption...
  • COMSATS Institute of Information Technology

    COMSATS Institute of Information Technology

    Week vs. Weak. Steel vs. Steal. Barriers to Effective Communication. Semantic and Language Barriers [3/4] There are certain adjectives and adverbs which convey different meanings to different persons depending upon daily activities and way of life.