Lab 11:  Privileges;  Commit/Rollback Commands

Privileges:

  1. What system privileges and/or roles have you been granted?  Use the data dictionary tables USER_SYS_PRIVS and USER_ROLE_PRIVS.

  2. Create a view on the MANAGER table called MGRPUB consisting of the manager number and manager name columns.

  3. Grant read access on this view to the public.

  4. Grant read access on the MANAGER table to user DAT2355D_010A.

  5. Grant update privileges on the salary column in the MANAGER table to user DAT2355D_010B.

  6. Log on as user DAT2355D_010A (password is DAT2355D_010A).  Which of the following SQL statements execute successfully?  Why?  Substitute your userid where indicated;  change the column names to the ones you have used in your table.

      7. select * from userid.manager
      select * from userid.mgrpub
      update userid.manager set managername = 'Black' where managerno  = 'M2';
      update userid.mgrpub set managername = 'Black' where managerno  = 'M2';
      update userid.manager set salary = salary + 1000 where managerno = 'M2';

  7. Log on as user DAT2355D_010B (password is DAT2355D_010B).  Which of the following SQL statements execute successfully?  Why?  Substitute your userid where indicated;  change the column names to the ones you have used in your table.

      8. select * from userid.manager
      select * from userid.mgrpub
      update userid.manager set managername = 'Black' where managerno  = 'M2';
      update userid.mgrpub set managername = 'Black' where managerno  = 'M2';
      update userid.manager set salary = salary + 1000 where managerno = 'M2';

  8. Log back on using your own userid.  Revoke the privileges granted in Steps 3 - 5.


Commit/Rollback:

Delete the stock symbol foreign key on the HOLDING table before doing the following exercises.
  1. Enter the command:     select * from stock;      to see the current entries in the STOCK table.

  2. Enter the commands:

    3.     delete from stock where stocksymbol = 'JDS';
        commit;

  3. Enter the command:     select * from stock;       Has the row for JDS been deleted?

  4. Enter the command:     rollback;

  5. Enter the command:     select * from stock;       Has the delete been rolled back?  Why or why not?

  6. Enter the command:     delete from stock where stocksymbol = 'NTL';

  7. Enter the command:     select * from stock;      Has the row for NTL been deleted?

  8. Enter the command:     rollback;

  9. Enter the command:     select * from stock;      Has the delete been rolled back?  Why or why not?