Database Programming

1
Database Programming

• Sections 13Creating, revoking objects privileges

2
System vs. Object Privileges

• System Privileges
• The right to access the database and its objects
  create table, create view, back up any table,
  etc.
• Generally granted by the DBA
• Object Privileges
• The right to manipulate the content of the
  objects database alter, delete, execute, index,
  insert, reference, select, update
• Grant by the owner of the object

3
Control of User Access

• Oracle Server database security, you can do the
  following
• Control database access
• Give access to specific objects in the database
• Confirm given and received privileges within the
  Oracle data dictionary
• Create synonyms for database objects

4
Privileges

• Right to execute particular SQL statements.
• DBA high-level user with ability to grant users
  access
• Users require system privileges to gain, access
  to databases/objects to manipulate content
• Users can be given privilege to grant additional
  privileges to other users/roles

5
System privileges of DBA

• DBAs generally allocate system privileges
• Any user who owns an object can grant object
  privileges

6
Schema

• A schema is a collection of objects, such as
  tables, views, and sequences.
• The schema is owned by a database user and has
  the same name as that user.
• In this course, your schema name is -
  usca_cc_sql01_sxx, where xx is your number.

7
System Privileges

• Below are listed typical privileges provided by
  the database administrator.

8
Object Privileges

• An owner can give specific privileges on that
  owners object
• GRANT obj_privilege(columns)ON objectTO
  USERROLEPUBLICWITH GRANT OPTION
• To grant privileges on an object, the object must
  be in your schema, or you must have been granted
  the object privileges WITH GRANT OPTION
• An object owner can grant any object privilege on
  the object to any other user or role of the
  database
• The owner of an object automatically acquires all
  object privileges on that object
• GRANT select, insert (name, email)ON
  address_bookTO Scott WITH GRANT OPTION
• REVOKE select, insertON address_bookFROM scott

9
Guidelines to grant object privileges

• To grant privileges on an object, the object must
  be in your own schema, or you must have been
  granted the object privileges WITH GRANT OPTION.
• An object owner can grant any object privilege on
  the object to any other user or role of the
  database.
• The owner of an object automatically acquires all
  object privileges on that object.

10
Syntax
11
Granting privileges for objects

• Only privileges that apply to a sequence are
  SELECT and ALTER.
• You can grant UPDATE, REFERENCES, and INSERT on
  individual columns on a table. For example
• GRANT UPDATE (auth_expense)ON d_partners TO
  allison_plumb
• You can restricted SELECT privilege by creating
  a view with a subset of columns and granting the
  SELECT privilege only on the view.
• You can't grant SELECT on individual columns.
• Privilege granted to synonym is converted to a
  privilege on the base table referenced by the
  synonym.

12
Grant Revoke Syntax

• GRANT object_priv(columns)ON objectTO
  userrolePUBLICWITH GRANT OPTION
• REVOKE privilege , privilege...AllON
  objectFROM user, user...rolePUBLICCASCADE
  CONSTRAINTS
• CASCADE CONSTRAINTS - required to remove any
  referential integrity constraints made to the
  object by means of the REFERENCES privilege
  like creating a reference to your table via
  foreign key

13
Examples DP.13.3.10

• 1. GRANT  select (Scott owns d_songs and
  typed this command) ON     d_songs TO
    PUBLIC
• 2. GRANT  update (title, artist) (Scott owns
  d_songs and is granting ON     d_songs
  authorization to update these
  columns) TO     jennifer_cho, manager
• 3. SELECT   (Jennifer now types this
  to view Scotts d_songs table) FROM
     scott_king.d_songs Jennifer types
  the following
• 4. CREATE SYNONYM songs FOR
  scott_king.d_songs
• 5. SELECT FROM songs (songs is the
  synonym)

14
WITH GRANT OPTION

• GRANT  select, insertON     d_songsTO
      scott_kingWITH   GRANT OPTION
• With grant option clause allows the privileges to
  be passed on to other users.
• With Grant Option can be revoked when users
  privileges are revoked.

15
Pictorial view WITH GRANT OPTION
16
PUBLIC keyword

• GRANT  selectON     jason_tsang.d_songsTO
      PUBLIC
• Owner of a table can grant access to all users by
  using keyword PUBLIC

17
REVOKE privilege

• REVOKE privilege , privilege...ALLON
   objectFROM   user, user...rolePUBLICCASC
  ADE CONSTRAINTS
• CASCADE CONSTRAINTS required to remove any
  referential integrity constraints made to the
  object by means of the REFERENCES privilege.

18
Revoke privilege

• REVOKE select, insertON d_songsFROM
  usca_cc_sql01_sxx

19
Access errors

• Oracle Server error message table or view does
  not exist, you have done one of the following
• Named a table or view that does not exist
• Attempted to perform an operation on a table or
  view for which you do not have the appropriate
  privileges

20
Viewing privilege in data dictionary

• Types of privilege you can view

21
Example of privileges commands

• SELECT FROM role_tab_privsWHERE role
  MANAGER
• SELECT FROM user_sys_privs
• SELECT FROM user_role_privs

22
Displaying your privileges

• To show what privileges a user has on the
  databases enter
• SELECT FROM SESSION_PRIVS
• You have a list of privileges you have displayed.
• Run the command to see what you get. See next
  slide.

23
Results of previous command
24
What is a Role?

• A role is a named group of related privileges
  that can be granted to the user.
• Makes it easier to revoke and maintain
  privileges.
• A user can have access to several roles, and
  several users can be assigned the same role.
• Roles are typically created for a database
  application.
• Syntax
• CREATE ROLE role

25
Example of a Role

• CREATE ROLE manager
• Role created.
• GRANT create table TO manager
• Grant succeeded.
• GRANT manager TO User_name
• Grant succeeded.
• PRIVILEGES ARE GRANTED TO ROLES
• PEOPLE ARE ASSIGNED ROLES

26
Why Roles are easier?

• How it works.