Session 5.pdf

Database Security and User Management Objectives To discuss database security principles (AAA) To create and manage database user accounts:  Authenticate users  Assign default storage areas (tablespaces)  To create and manage profiles:  Implement standard password security...

Database Security and User Management Objectives To discuss database security principles (AAA) To create and manage database user accounts:  Authenticate users  Assign default storage areas (tablespaces)  To create and manage profiles:  Implement standard password security features  Control resource usage by users To discuss authorisation implementation:  How to grant and revoke privileges  How to create and manage roles 3 Database Security Overview Fundamental data security requirements:  Confidentiality  Integrity  Availability Major aspects of database security:  Authentication  Authorisation  Auditing 5 Authentication Authentication verifies the user’s identity Authentication methods can be classified as:  something you know (password)  something you are (biometric)  something you have (smart card) Basic authentication  Database user identified by a password  Database user identified by the operating system Strong authentication  Confirming the identity of the user with something other than a password: smart cards, biometrics, certificates, etc. 6 Authorisation Authorisation includes primarily two processes:  Giving users certain permissions to access, process, or alter data  Applying varying limitations on user access or actions The limitations placed on (or removed from) users can apply to  objects such as schemas, tables, or rows  to resources such as time (CPU, connect, or idle times) 7  Auditing Auditing  is the monitoring and recording of selected user database actions  can be based on individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on  can audit both successful and failed activities  can be done as a high level monitoring as well as Fine- grained auditing But auditing of any type increases the amount of work that the database must do ! 8 Database User Accounts Each database user account has:  A unique username  An authentication method  A default tablespace  A temporary tablespace  A tablespace quota  A user profile 9 Creating User in SQL*Plus  Use CREATE USER SQL statement to create a database user account  To create a user, you must have the CREATE USER system privilege  Because it is a powerful privilege, a database administrator or security administrator is usually the only user who has the CREATE USER system privilege Example: SQL> CREATE USER jsmith IDENTIFIED BY pass4You DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE clerk_profile; 10 Authenticating Users Password External  This is authentication by a method outside the database (operating system, biometrics, etc.) Global  Using LDAP services, Oracle Internet Directory 11 Assigning a Default Tablespace to the User Each user should have a default tablespace  When user creates an object the database stores the object in the default user's tablespace (unless DDL command contains different tablespace)  The default setting for the default tablespaces of all users is set in the initialization parameter file  At the creation of the database it might be set to SYSTEM tablespace, but it is strongly recommended to change default tablespace from SYSTEM to other tablespace, for example USERS  By separating the user data from the system data, you reduce the likelihood of problems with the SYSTEM tablespace, which can in some circumstances cause the entire database to become nonfunctional 13 Assigning a Tablespace Quota to the User By default, a user has no quota on any tablespace in the database Assigning a quota accomplishes the following:  Users with privileges to create certain types of objects can create those objects in the specified tablespace  Oracle Database limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota  You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces  Specific quotas prevent a user's objects from using too much space in the database 14 Assigning a Temporary Tablespace to the User DBA should assign each user a temporary tablespace When a user executes a SQL statement that requires a temporary segment, the database stores the segment in the temporary tablespace of the user If DBA does not explicitly assign the user a temporary tablespace, then the database assigns the user the default temporary tablespace that was specified:  at database creation  by an ALTER DATABASE statement at a later time 15  Profiles A profile is a named set of resource limits and password parameters that restrict database usage and instance resources for a user. Each user can have only one profile, and creating a new one supersedes an earlier version. Profiles are used to manage the resource limits of related users. DBA needs to create and manage user profiles only if resource limits are a requirement of your database security policy. 16  Creating profiles  Syntax: CREATE PROFILE profile_name LIMIT { resource_parameters | password_parameters };  resource_parameters:  SESSIONS_PER_USER (the number of concurrent logins that can be made to the same user account)  CONNECT_TIME (in minutes, the max duration of a session)  IDLE_TIME (in minutes the maximum time a session can be idle)  CPU_PER_CALL ( the CPU time in centiseconds that a session’s server process is allowed to use to execute one SQL statement before the it forcibly terminated)  etc.  password_parameters:  FAILED_LOGIN_ATTEMPTS  PASSWORD_LOCK_TIME (the number of days an account will be locked after the specified number of consecutive failed login attempts)  PASSWORD_LIFE_TIME (the number of days before the password expires)  PASSWORD_REUSE_TIME (the number of days before a password can be reused)  PASSWORD_REUSE_MAX (the number of times a password can be reused)  If either parameter is specified as UNLIMITED, then the user can never reuse a password  If both parameters are set to UNLIMITED, then Oracle Database ignores both, and the user can reuse any password at any time 17  Creating Profiles Examples SQL> CREATE PROFILE app_prof LIMIT SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CONNECT_TIME 45; FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1 PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 45 PASSWORD_REUSE_MAX 4  The user with this profile:  can have any number of concurrent sessions ; in a single session, the user can consume an unlimited amount of CPU time; a single session cannot last for more than 45 minutes  will be locked after 3 failed login attempts; it will be locked for 1 day; the password will expire in 60 days; the user has to wait for 45 days before re-using the same password; the same password can be reused 4 times 18  Profile Enforcement  Profiles can be assigned only to users and not to other profiles SQL> CREATE USER user1 IDENTIFIED BY passw1 PROFILE app_prof; SQL> ALTER USER sh PROFILE new_profile;  Profile resource limits are enforced only when resource limitation is enable for the database  Limitation can be enabled  using the RESOURCE_LIMIT initialization parameter (before starting up the database )  using the ALTER SYSTEM statement (while database is open ) SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE; 19 User Authorisation Authorisation determines what types of objects, privileges, and resources the user is permitted to access or use. The resources are limited by a profile attached to the user. Access to the objects and structures in the database is controlled by setting an individual user privileges, or collection of privileges – roles. 21  Privileges There two types of privileges:  System: Enables users to perform particular actions in the database  Usually corresponds to the permission to run DDL commands  Object: Enables users to access and manipulate a content of a specific object  Usually corresponds to the permission to run DML commands HR_DBA Object privilege: System privilege: Update ON employees Create session 22 Granting System Privileges SQL> GRANT create session TO user123; SQL> GRANT create table TO user123; SQL> GRANT alter any sequence TO user456; SQL> GRANT create table, create sequence, create view TO user123, user456; 23 Granting System Privileges WITH ADMIN OPTION WITH ADMIN OPTION SYS HR_DBA scott CREATE TABLE region (id … CREATE TABLE GRANT stats (id … create table, TO HR_DBA WITH ADMIN OPTION; GRANT create table TO scott; 24  Revoking System Privileges To revoke a system privilege, you must have been granted the privilege with the ADMIN OPTION You can revoke any privilege if you have the GRANT ANY PRIVILEGE system privilege  Syntax: REVOKE { system_privilege | role | ALL PRIVILEGES } FROM { user [ IDENTIFIED BY password ] | role | PUBLIC } ;  Example: SQL> REVOKE CREATE TABLE,DROP TABLE FROM hr_user, oe_user; 25 Revoking System Privileges SYS HR_DBA scott GRANT GRANT Users WITH ADMIN OPTION GRANT GRANT Privileges Objects SYS HR_DBA scott REVOKE Users GRANT GRANT Privileges Objects 26 Understanding Object Privileges Object privileges vary from object to object An owner has all the privileges on the object An owner can grant specific privileges on the owner’s object to another user or to PUBLIC Object privileges cannot be granted along with system privileges and roles in the same GRANT statement Some object privileges are only applicable to certain types of objects  For example, the DELETE privilege only makes sense with table or views, but not sequences 27 Granting Object Privileges To grant object privileges you must specify the name of the object as a part of the GRANT statement SQL> GRANT SELECT ON employees TO user123; SQL> GRANT SELECT, UPDATE ON employees TO user123; SQL> GRANT SELECT ON employees TO user123; SQL> GRANT SELECT ON departments TO user123; SQL> GRANT UPDATE (location_id) ON departments TO user123, mgr1; 28 Revoking Object Privileges Syntax: REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} [CASCADE CONSTRAINTS | FORCE]; Example: SQL> REVOKE select, insert ON departments FROM my_scott; Revoke select succeeded. 29 Cascade Revoke SYS HR_DBA scott GRANT GRANT Users WITH GRANT OPTION Privileges GRANT GRANT Objects SYS HR_DBA scott REVOKE Users GRANT... GRANT Privileges Objects 30 Roles Users Manager Role 1 2 3 4 Privileges 1 2 3 4 Allocating privileges Allocating privileges without a role with a role 32 About Roles The role is a named group of related privileges  The role simplifies the managing multiple system and object privileges You create roles, grant system and object privileges to the roles, and then grant roles to users  You can also grant roles to other roles  Unlike schema objects, roles are not contained in any schema 33  Managing Roles Creating Roles SQL> CREATE ROLE clerk; SQL> CREATE ROLE manager; Assigning privileges to a role SQL> GRANT CREATE TABLE,CREATE VIEW TO manager; SQL> GRANT SELECT, UPDATE ON orders TO clerk; Assigning roles to users SQL> GRANT manager TO user123; SQL> GRANT clerk TO user123, user555, user767, user999; Assigning roles to roles SQL> GRANT clerk TO manager;  Question: What privileges role manager will have in this case? 34  Oracle Database Predefined Roles Oracle Database has many predefined roles; the following three are widely used:  CONNECT (changed since 10g Release 2)  Enables a user to connect to the database  RESOURCE  Enables a user to create, modify, and delete certain types of schema objects in the schema associated with that user  This role grants set of the system privileges (CREATE TABLE , CREATE PROCEDURE, etc)  DBA  Enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating, modifying, and deleting schema objects in any schema; and more, but does not include the privileges to start or shut down the database instance 35 QUESTIONS 36