1 / 30

Chapter 6

Chapter 6. Users and Basic Security. Progression of Steps for Creating a Database Environment. Install Oracle database binaries (Chapter 1) Create database (Chapter 2) Establish a way to manage your environment (Chapter 3) Create and manage tablespaces and datafiles (Chapter 4 )

maeve
Download Presentation

Chapter 6

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 6 Users and Basic Security

  2. Progression of Steps for Creating a Database Environment • Install Oracle database binaries (Chapter 1) • Create database (Chapter 2) • Establish a way to manage your environment (Chapter 3) • Create and manage tablespaces and datafiles (Chapter 4) • Understand and manage control files and online redo logs (Chapter 5) • Create users and establish basic security (Chapter 6)

  3. User Management and Basic Security Concepts • Creating users • Establishing password security • Modifying users • Dropping users • Database profiles • System privileges • Object privileges • Database roles

  4. Difference between User and Schema • User is the account through which you logon to the database • Schema is a collection of database objects owned by a user • When you logon to a database, by default your logon user is associated with a schema of the same name • You can alter the schema that your logon user is associated with via the ALTER SESSION SET CURRENT_SCHEMA statement • Understanding the difference between user and schema is subtle, and usually is not that important • People use the term user and schema synonymously, but there is a slight difference

  5. User vs. Schema example SQL> conn / as sysdba select user, schemaname from sys.gv_$session where sid =(select sid from sys.gv$mystat where rownum = 1); USER SCHEMANAME ------------------------------ ------------------------------ SYS SYS SQL> alter session set current_schema = system; select user, schemaname from sys.gv_$session where sid =(select sid from sys.gv$mystat where rownum = 1); USER SCHEMANAME ------------------------------ ------------------------------ SYS SYSTEM

  6. Managing Default Users • Lock default accounts not being used • Unlock them as required • SQL to generate SQL to lock and expire passwords: select 'alter user ' || username || ' password expire account lock;' from dba_users;

  7. Checking Default Accounts select distinct u.username ,case when d.user_name is null then 'DBA created account' else 'Oracle created account' end from dba_users u ,default_pwd$ d where u.username=d.user_name(+); Note: The DEFAULT_PWD$ view is available starting with Oracle Database 11g. For more details about guidelines regarding checking for default passwords, see My Oracle Support note 227010.1.

  8. Password ever been changed? select name ,to_char(ctime,'dd-mon-yy hh24:mi:ss') ,to_char(ptime,'dd-mon-yy hh24:mi:ss') ,length(password) from user$ where password is not null and password not in ('GLOBAL','EXTERNAL') and ctime=ptime;

  9. Considerations when Creating Users • User name • Authentication method • Basic privileges • Default permanent tablespace • Default temporary tablespace

  10. Choosing a User Name and Authentication Method • Usually you should choose a username that provides some idea as to the use of the account • Authentication methods • Password • External service, such as the OS • Global user via enterprise directory service (Oracle Internet Directory)

  11. Assigning Default Permanent and Temporary Tablespaces select username ,password ,default_tablespace ,temporary_tablespace from dba_users; • None of your users, other than the SYS user, should have a default permanent tablespace of SYSTEM • All of your users should have a temporary tablespace of something like TEMP

  12. Modifying Passwords • Use the ALTER USER ... IDENTIFIED BY statement • Oracle Database 11g and higher, passwords are case sensitive

  13. Enforcing Password Security • Use easily remembered passwords so you don’t have them written down or recorded in a file somewhere. Because the passwords aren’t sophisticated, they aren’t very secure. • Enforce a level of sophistication for passwords. Such passwords aren’t easily remembered and thus must be recorded somewhere, which isn’t secure.

  14. Enforcing Password Security SQL> @?/rdbms/admin/utlpwdmg • For Oracle Database 11g, set the PASSWORD_VERIFY_FUNCTION of the DEFAULT profile to VERIFY_FUNCTION_11G : SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION verify_function_11G; • If for any reason you need to back out of the new security modifications, run this statement to disable the password function: SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null;

  15. Reasons for Logging On as a Different User • You’re copying a user from one environment (such as production) to a different environment (such as test), and you want to retain the original password. • You’re working in a production environment, and you need to be able to connect as the user who owns objects to execute CREATE TABLE statements, issue grants, and so on. In a production environment, you may not know the user’s password because of poor maintenance procedures.

  16. Logging On as a Different User • As a DBA, temporarily store a user’s encrypted password. • Change the user’s password. • Connect to the user with the new password, and run Data Definition Language (DDL) statements. • Connect as a DBA, and change the password back to the original.

  17. Reasons to Modify a User Account • To change a user’s password • To lock or unlock a user • To change the default permanent and/or temporary tablespace • To change a profile or role • To change system or object privileges • To modify quotas on tablespaces

  18. Dropping Users • First lock the account, this locks access to the account, but not the account’s objects • Make sure the account isn’t being used, you may want to export the account before you drop it • Drop the account SQL> drop user mv_maint cascade;

  19. Database Profiles • Enforcing password security settings • Limiting system resources that a user consumes

  20. Implementing Password Security • Use a database profile • Attributes you can control: • FAILED_LOGIN_ATTEMPTS • PASSWORD_GRACE_TIME • PASSWORD_LIFE_TIME • PASSWORD_LOCK_TIME • PASSWORD_REUSE_MAX • PASSWORD_REUSE_TIME • PASSWORD_VERIFY_FUNCTION

  21. Limiting Database Resource Usage • Set RESOURCE_LIMIT to TRUE • Use a database profile • Resource limits you can control: • COMPOSITE_LIMIT • CONNECT_TIME • CPU_PER_CALL • CPU_PER_SESSION • IDLE_TIME • LOGICAL_READS_PER_CALL • LOGICAL_READS_PER_SESSION • PRIVATE_SGA • SESSIONS_PER_USER

  22. Types of Privileges • System privileges • Object privileges

  23. Assigning Database System Privileges • Database system privileges allow you to do tasks such as connecting to the database and creating and modifying objects • There are hundreds of different system privileges • You can view system privileges by querying the DBA_SYS_PRIVSview: SQL> select distinct privilege from dba_sys_privs; PRIVILEGE ---------------------------------------- ALTER ANY DIMENSION ALTER ANY RULE UPDATE ANY CUBE BUILD PROCESS ALTER SYSTEM ...................

  24. Assigning Database System Privileges • SQL> grant create table, create database link to inv_mgmt; • SQL> grant create trigger to inv_user; • To revoke a system privilege: SQL> REVOKE <priv> FROM <user>;

  25.  Assigning Database Object Privileges • Database object privileges allow you to access and manipulate other users’ objects • The types of database objects on which you can grant privileges include tables, views, materialized views, sequences, packages, functions, procedures, user-defined types, and directories • To be able to grant object privileges, one of the following must be true: • You own the object • You’ve been granted the object privilege with GRANT OPTION • You have the GRANT ANY OBJECT PRIVILEGE system privilege

  26.  Assigning Database Object Privileges Table level grants: SQL> grant insert, update, delete, select on registrations to inv_mgmt_app; Column level grants: SQL> grant insert (inv_id, inv_name, inv_desc) on inventory to inv_mgmt_app;

  27. Grouping and Assigning Privileges • A role is a database object that allows you to logically group system and/or object privileges together so you can assign those privileges in one operation to a user • Roles help you manage aspects of database security in that they provide a central object that has privileges assigned to it

  28. Grouping and Assigning Privileges SQL> grant select any table to jr_dba; SQL> grant create any table to jr_dba; SQL> grant create any view to jr_dba; SQL> grant create synonym to jr_dba; SQL> grant create database link to jr_dba; SQL> grant jr_dba to lellison;

  29. PL/SQL and Roles • In PL/SQL, cannot access an object through a role • Access must be granted explicitly

  30. Summary • After you create a database, one of your first tasks is to secure any default user accounts. • You should consider using a secure profile for any users you create. • Additionally, think about password security when creating users.

More Related