Roles - PowerPoint PPT Presentation

Loading...

PPT – Roles PowerPoint presentation | free to download - id: 81eda6-YTIyM



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Roles

Description:

Roles & privileges A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. The types of privileges are ... – PowerPoint PPT presentation

Number of Views:70
Avg rating:3.0/5.0
Slides: 16
Provided by: cun141
Learn more at: http://www.ms.mff.cuni.cz
Category:
Tags: roles

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Roles


1
Roles privileges
  • A user privilege is a right to execute a
    particular type of SQL statement, or a right to
    access another user's object. The types of
    privileges are defined by Oracle.
  • Roles, on the other hand, are created by users
    (usually administrators) and are used to group
    together privileges or other roles. They are a
    means of facilitating the granting of multiple
    privileges or roles to users.

2
System Privileges
  • Over 100 system privileges
  • System privileges can be very powerful
  • Oracle recommends that you configure your
    database to prevent regular (non-DBA) users
    exercising ANY system privileges
  • System privileges providing access to objects in
    other schemas do not give other users access to
    objects in the SYS schema
  • For example, the SELECT ANY TABLE privilege
    allows users to access views and tables in other
    schemas, but NOT in SYS schema

3
Accessing Objects in the SYS Schema
  • The SYS users and those who connect with
    administrative privileges (SYSDBA) can access
    objects in the SYS schema.
  • Other means of allowing access to objects in the
    SYS schema is by granting users any of the
    following roles
  • SELECT_CATALOG_ROLE
  • This role can be granted to users to allow SELECT
    privileges on all data dictionary views.
  • EXECUTE_CATALOG_ROLE
  • This role can be granted to users to allow
    EXECUTE privileges for packages and procedures in
    the data dictionary.

4
Object Privileges
  • Each type of object has different privileges
    associated with it
  • Grant A Single Privilege
  • CREATE TABLE test (testcol VARCHAR2(20))
  • GRANT SELECT ON test TO abc
  • Grant Multiple Privileges
  • GRANT INSERT, DELETE ON test TO abc
  • Grant All Privileges
  • GRANT ALL ON test TO abc
  • Grant Execute (procedures, functions, packages)
  • GRANT EXECUTE ON getosuser TO abc

5
Object Privileges
  • Each type of object has different privileges
    associated with it
  • Revoke A Single Privilege
  • REVOKE SELECT ON test FROM abc
  • Revoke Multiple Privileges
  • REVOKE INSERT, DELETE ON test FROM abc
  • Revoke All Privileges
  • REVOKE ALL ON test FROM abc
  • Revoke Execute (procedures, functions, packages)
  • REVOKE EXECUTE ON getosuser FROM abc

6
Object Privileges
  • Each type of object has different privileges
    associated with it
  • Check privileges from the grantor's point of view
  • Grantor is logged in
  • SELECT grantee, table_name, privilege
  • FROM user_tab_privs_made
  • Check privileges from the grantee's point of view
  • Grantee is logged in
  • SELECT grantor, table_name, privilege
  • FROM user_tab_privs_recd

7
User Roles
  • A role groups several privileges and roles, so
    that they can be granted to and revoked from
    users simultaneously.
  • A role must be enabled for a user before it can
    be used by the user.
  • Crucial role CONNECT
  • Includes the following system privileges
  • ALTER SESSION, CREATE CLUSTER,
  • CREATE DATABASE LINK, CREATE SEQUENCE,
  • CREATE SESSION, CREATE SYNONYM,
  • CREATE TABLE, CREATE VIEW

8
Creating a role
  • You can create a role using the CREATE ROLE
    statement, but you must have the CREATE ROLE
    system privilege to do so. Typically, only
    security administrators have this system
    privilege
  • CREATE ROLE manager IDENTIFIED BY morework
  • Password 'morework' is then needed in the SET
    ROLE statement
  • There are other means how to authorize a role
    than password
  • by an Application
  • by an External Source
  • by the Operating System

9
Granting a role
  • GRANT new_dba TO michael
  • WITH ADMIN OPTION
  • WITH ADMIN OPTION rarely granted

10
Auditing
  • Auditing is a method of "crime detection" not
    "crime prevention."
  • It simply allows the site to keep a record of
    actions taken with respect to the database.
  • The level of detail can be controlled by
    specifying auditing
  • BY SESSION ACCESS.
  • default is to audit an action only once per
    session.
  • an action can be audited
  • WHENEVER (NOT) SUCCESSFUL.
  • Depending on the setting, you can request
    successes only, failures only, or both.
  • 3 level auditing

11
1)Statement level
  • tracks usage of any of a related group of
    commands.
  • For example the "table" statement would cause any
    of the following DDL commands to be audited
  • CREATE TABLE
  • DROP TABLE
  • TRUNCATE TABLE.
  • This level can be imposed across selected
    UserID's only, or the entire instance.

12
2)Privilege level
  • tracks any command that relies on the specified
    system privilege.
  • This level can also be imposed across selected
    UserID's only, or the entire instance

13
2)Object level
  • tracks specified references to a given object
  • (such as one table)
  • will be imposed across all UserID's

14
Auditing options
  • Assuming that the "fireid" user is to be audited
  • CONNECT sys/password AS SYSDBA
  • AUDIT ALL BY fireid BY ACCESS
  • AUDIT
  • SELECT TABLE,
  • UPDATE TABLE,
  • INSERT TABLE,
  • DELETE TABLE BY fireid BY ACCESS
  • AUDIT EXECUTE PROCEDURE BY fireid BY ACCESS

15
Auditing data
  • View DBA_AUDIT_TRAIL
  • Username Oracle Username.
  • Terminal Machine that the user performed
    the action from.
  • Timestamp When the action occured.
  • Object Owner The owner of the object that
    was interacted with.
  • Object Name The name of the object that was
    interacted with.
  • Action Name The action that occured against
    the object. (INSERT, UPDATE, DELETE, SELECT,
    EXECUTE)
About PowerShow.com