Dbas generally allocate system privileges any user

This preview shows page 76 - 79 out of 111 pages.

DBAs generally allocate system privileges; any user who owns an object can grant object privileges.GRANT selectON employeesTO sue, rich;GRANT update (department_name, location_id)ON departmentsTO scott, manager;Passing On privilegesA privilege that is granted with the WITH GRANT OPTION clause can be passed on to other users and rolesby the grantee. Object privileges granted with the WITH GRANT OPTION clause are revoked when thegrantor’s privilege is revoked.An owner of a table can grant access to all users by using the PUBLIC keyword.GRANT select, insertON departmentsTO scottWITH GRANT OPTION;GRANT selectON alice.departmentsTO PUBLIC;Confirming Privileges GrantedIf you attempt to perform an unauthorized operation, such as deleting a row from a table for which you do nothave the DELETE privilege, the Oracle server does not permit the operation to take place.If you receive the Oracle server error message “Table or view does not exist,” then you have done either ofthe 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 appropriateprivilege.You can access the data dictionary to view the privileges that you have.Page76of111
2-L1-Controlling User AccessData Dictionary ViewDescriptionROLE_SYS_PRIVSSystem privileges granted to rolesROLE_TAB_PRIVSTable privileges granted to rolesUSER_ROLE_PRIVSRoles accessible by the userUSER_TAB_PRIVS_MADEObject privileges granted on the user’s objectsUSER_TAB_PRIVS_RECDObject privileges granted to the userUSER_COL_PRIVS_MADEObject privileges granted on the columns of the user’s objectsUSER_COL_PRIVS_RECDObject privileges granted to the user on specific columnsUSER_SYS_PRIVSSystem privileges granted to the userRevoking Object PrivilegesYou can remove privileges granted to other users by using the REVOKE statement. When you use theREVOKE statement, the privileges that you specify are revoked from the users you name and from any otherusers to whom those privileges were granted by the revoked user.In the syntax-CASCADE is required to remove any referential integrity constraints made to theCONSTRAINTS object by means of the REFERENCES privilege.REVOKE select, insertON departmentsFROM scott;[CASCADE CONSTRAINTS];Page77of111
2-L1-Controlling User AccessSummaryDBAs establish initial database security for users by assigning privileges to the users.The DBA creates users who must have a password. The DBA is also responsible for establishing theinitial system privileges for a user.After the user has created an object, the user can pass along any of the available object privileges toother users or to all users by using the GRANT statement.A DBA can create roles by using the CREATE ROLE statement to pass along a collection of systemor object privileges to multiple users. Roles make granting and revoking privileges easier to maintain.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 111 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
N/A
Tags
Salary, Quotation mark

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture