[Ad-Oracle-Sql] 11. Dependencies and Privileges

📋 This is my note-taking from what I learned in the class “Advanced Database Concepts”

Objective

• Direct and indirect dependencies
• Data dictionary information on dependencies
• The unique nature of package dependencies
• Avoiding recompilation errors
• Granting program unit privileges

Program Unit Dependencies

• Relationships or dependencies determine the validity of any program unit after modifications to database objects that the program unit references
• This validity determines the need for recompilation

• A procedure calls a function

  The procedure is a dependent object and the function is the referenced object

Brewbean’s Challenge

• Need to take any steps possible to make the execution more efficient
• Users have been hitting some unexpected errors related to recent modifications to the database and program units
• In this light, need to review database dependencies and their impact

Local Dependency Activity

• Status of program unit can be checked using USER_OBJECTS
• When a referenced object is modified, the status of the dependent object changes to INVALID
• INVALID status indicates need for recompilation
• ALTER COMPILE command used to recompile a program unit

Object Status

SELECT object_name, status
  FROM user_objects
  WHERE object_name = 'ORDER_TOTAL_SP';

Output:

OBJECT_NAME	STATUS
ORDER_TOTAL_SP	INVALID

Automatic Recompilation

• Upon execution of a program unit with an INVALID status, the system will automatically recompile

• Drawbacks

  • Recompilation of dependent objects tests the changes to the referenced objects, which could raise errors at run time

  • Recompilation processing occurs during run time

Direct & Indirect Dependencies

• Direct – a procedure calls a function

• Indirect – a procedure calls a procedure which calls a function

  The dependency between the first procedure and the function is indirect

• Indirect dependencies have same affect as direct dependencies

Data Dictionary

• USER_DEPENDENCIES identify direct dependencies
• Use WHERE clause on name column to analyze a particular object
• DBA_DEPENDENCIES will identify direct dependencies of objects in all schemas

![img]

Package Dependencies

• Modifications to package specification will change status of dependent objects
• Modifications to only the package body do NOT change status of dependent objects

• Separation of code in packages

  • Minimizes recompilation needs

  • Dependent objects to be developed prior to the package body being created

Avoiding Recompilation Errors

• Use %TYPE and %ROWTYPE attributes
• Use the * notation in queries to select all columns
• Use a column list in INSERT statements

Program Unit Privileges

GRANT SELECT, INSERT, UPDATE ON bb_basket TO scott;

System Privilege	Explanation
CREATE PROCEDURE	Allows users to create, modify, and drop program units in their own schemas
CREATE ANY PROCEDURE	Allows users to create program units in any schema; doesn’t allow modifying or dropping the program units
ALTER ANY PROCEDURE	Allows users to modify program units in any schema
DROP ANY PROCEDURE	Allows users to drop program units in any schema
EXECUTE ON program_unit_name	Allows users to run a specific program unit
EXECUTE ANY PROCEDURE	Allows users to run program units in any schema

Privileges - Data Dictionary

View Name	Description	Column Information
SESSION_PRIVS	Shows all privileges of the current schema, direct and indirect	PRIVILEGE (name of the privilege granted)
SESSION_ROLES	Shows all roles granted to the current schema	ROLE (name of the role granted)
USER_SYS_PRIVS	Shows only direct privileges of the current schema	USERNAME (recipient of the privilege), PRIVILEGE (name of the privilege granted), and ADMIN_OPTION (Yes or No to indicate whether privileges were granted with WITH ADMIN OPTION)
USER_ROLE_PRIVS	Shows only direct roles granted to the current schema	USERNAME (recipient of the privilege), GRANTED_ROLE (name of the role granted), ADMIN_OPTION (Yes or No to indicate whether privileges were granted with WITH ADMIN OPTION), DEFAULT_ROLE (Yes if it’s the user’s default role; otherwise, No), OS_GRANTED (Yes if the OS manages the roles; otherwise, No)

Summary

• Program unit status changes when referenced object is modified
• INVALID status indicates a need for recompilation
• Direct and indirect dependencies both affect status
• Dependency tree utility allows mapping of both direct and indirect dependencies
• Packages minimize recompilation needs
• Appropriate privileges needed to create and use program units

---

Back to Top

See other articles in Category Ad-Oracle-Sql