Oracle Database 11g : Database Fundamentals - Work with Object and System Privileges, Introduce Yourself to the Grid

10/19/2012 9:20:52 PM

1 . Work with Object and System Privileges

It’s next to impossible to work with data in Oracle Database 11g without looking at object privileges. In this section, we are going to look at these privileges as well as a suite of system privileges closely related to managing Oracle Database 11g. The four main object privileges are select, insert, update, and delete, all discussed in the next four sections. Oracle Database 11g uses the term grant when referring to giving out both object and system privileges.


This is the primary and most commonly used privilege, permitting other users to view your data. There are three parts to grant statements:

  • The keywords grant select on.

  • The name of the object upon which the privileges are being given out.

  • The recipient of the grant.

Progress Check Answers
1.Installation, upgrades, tuning, and environment setup are four of many tasks performed by the DBA.
2.A private synonym can only be referenced in a SQL statement by the user who created and owns the synonym. A public synonym, created by a centralized user such as a DBA, is available to all users.
3.The default tablespace is the one within which users occupy space by default, unless another tablespace is mentioned as a table is created.
4.Quota on tablespaces is usually given out using bytes or megabytes as units of measurement.
5.The DBA goes to MetaLink to request assistance from Oracle’s support organization.
6.Triggers cannot exist on their own without association with an Oracle Database 11g table.


This privilege allows users to create rows in tables belonging to other users. The creator of new rows in other users’ objects is bound by the same rules used if they owned the objects themselves. They must adhere to the boundaries defined by the data types of the columns in the rows they create. For example, when rows are inserted into a table that has a column defined as type DATE, they must ensure that valid date type data is placed in the column so defined. As rows are created in an Oracle Database 11g table, the transaction must be committed to the database before the row becomes part of the information available to other users. With Oracle Database 11g, we use the term commit the same way the word save is used with other types of software.


This privilege allows a person to change the contents of columns in rows belonging to other tables. The SQL update statement can change the value of data in one or more columns. As with insert activity, the update transactions need to commit their work to make it permanent in the Oracle Database 11g files.


Delete operations interact with one or more rows in Oracle Database 11g tables and must be followed by a commit as well to write the results of the transaction to the database files.

SQL statements are subject to rigorous syntax requirements which, if not followed, return an assortment of Oracle errors. Just as with other programming languages you may be familiar with, the SQL statement processing engine is very strict with reserved words and the placement of the pieces that come together to form an SQL transaction. Let’s briefly discuss system privileges that allow certain users of Oracle Database 11g to perform secure activities.

System Privileges

Classically, secure operations are performed by the DBAs; however, one can grant system privileges to specified users so that they can perform selected activities themselves. The following list illustrates a few examples of these secure operations:

  • Alter system There are a number of modes that Oracle Database 11g can operate from. The modes are toggled using alter system. For example, this privilege can be given out to Jane by issuing the command grant alter system to jane.

  • Create user/alter user Often, the DBA wants to partition some of the user creation activities between a handful of users of Oracle Database 11g. This is done by giving out the create user system privilege. Once new users are created, you often don’t want to tweak their environment; this can be accomplished by issuing the grant alter user statement to one or more users of the database.

  • Create session/table/trigger Sometimes when new users are created, they are given the create session system privilege which allows them to connect to Oracle Database 11g. In many cases, depending on how new users are created, they are not allowed to build any objects until they receive the create table system privilege. As well, many users are not capable of defining triggers until they receive the create trigger system privilege.

System privileges were introduced with early releases of Oracle7 (circa 1993) and have played a useful role in the division of labor in the database since their inception. 

2.  Introduce Yourself to the Grid

As many have heard, the “g” in Oracle Database 11g stands for grid. Grid computing is a technology that allows for seamless and massively scalable access to a distributed network of diverse yet homogenous computer types. Oracle Database 11g is the glue permitting different vendors’ computers to work together providing a seemingly endless supply of shared computer resources. Oracle sees the grid as revolutionizing the way companies go about doing their business. Grid computing targets the delivery of information as a utility, similar to the way electrical and telephone services are currently delivered to the public—hence the term grid. The industry as a whole, but Oracle in particular, sees a delivery method from the grid such that consumers will only pay for what they use. Interlaced computers will allow idle capacity to be leveraged by the grid to provide for a form of parallel processing on steroids. The following are the major players that enable the Oracle grid technology:

Ask the Expert

Q:Name the four main object privileges used in Oracle Database 11g.
A:The four most common privileges are select, insert, update, and delete.
Q:Placing an Oracle Database 11g in a state where it can be accessed by applications is referred to as what activity?
A:Putting an Oracle Database 11g in a normal operating mode for day-to-day access by a company’s applications is referred to as startup.
Q:How many integer and decimal digits can a field defined in the data dictionary as number(10,2) accommodate?
A:The field would be able to store up to eight integer digits and two decimal digits.
Q:When Oracle Database 11g is passed the value “Beginner “for storage in a varchar2 column, how does it deal with trailing insignificant spaces?
A:The trailing spaces are trimmed before the information is stored in the database. Though not as common as varchar2, the char data type can be used to store trailing spaces.
Q:What would Oracle Database 11g store as a value in a number(6,2) field when passed the value 9.8882?
A:It would store 9.89 in a number(6,2) field when passed 9.8882.

  • Real Application Clusters (RAC) Involves a suite of networked computers sharing a common Oracle Database 11g and running platform-independent clusterware, the glue that makes the interconnect between the clustered nodes so transparent.

  • Automatic Storage Management (ASM) A front-end management system that can group disks from an assortment of manufacturers together to form a suite of disks that is available to all computers on the grid. ASM encapsulates the complete life cycle of disk management and allocation into a centralized GUI interface.

  • Oracle Resource Manager Provides a framework within which administrators can control the computing resources of nodes on the grid.

  • Oracle Scheduler Allows the handing out of jobs to members of the grid to facilitate the execution of business tasks anywhere and everywhere where idle resources exist.

  • Oracle Streams Assists the processing requirements whereby copies of data need to be streamed between nodes in the grid, providing the mechanisms to keep data in sync on one database with the database from which the data originated. Oracle Streams’ tight integration with the Oracle Database 11g engine facilitates this synchronization and delivers a preferred method of replication.

Figure 1 illustrates the primary differences between grid computing and traditional approaches to providing computer services.

Figure 1. Differences between traditional and grid computing

The following points reinforce the details of the two scenarios depicted in Figure 1:

  • The three applications at the top of the figure each have a dedicated server, each with its own dedicated disk. If the Linux server were to go out of service, the pension application would grind to a halt. There is no built-in mechanism for pension system processing to carry on another server.

  • The three applications at the bottom are interlaced with one another. The benefits application can be hosted on from one to three of the available servers. As well, the database files that support these three applications can reside upon (and be read from) any of the nine disks in the grid’s disk farm.

The browser-based OEM Grid Control holds the whole thing together. With the implementation of the ASM component of Oracle Database 11g, disks are managed by OEM, database instances are managed by OEM, clusterware is managed through OEM; the list is endless. Figure 2 shows the first OEM screen that appears after entering appropriate login credentials.

Figure 2. OEM startup


There is an OEM configuration program (called emca) that must be successfully run before you can access the browser-based OEM. The screen shown in your version of Oracle Database 11g may be somewhat different than the one shown in Figure 1. The look and feel of the OEM Grid Control screens can change significantly between minor releases of the software.

  •  Oracle Database 11g : Database Fundamentals - Become Familiar with Other Important Items in Oracle Database 11g
  •  Microsoft ASP.NET 4 : Repeated-Value Data Binding (part 2) - Creating a Record Editor
  •  Microsoft ASP.NET 4 : Repeated-Value Data Binding (part 1)
  •  Microsoft ASP.NET 4 : Single-Value Data Binding
  •  SQL Server 2005 : Dynamic T-SQL - Dynamic SQL Security Considerations
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 4) - sp_executesql: A Better EXECUTE
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 3) - SQL Injection
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 2) - Going Dynamic: Using EXECUTE
  •  SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 1) - Optional Parameters via Static T-SQL
  •  SQL Server 2005 : Dynamic T-SQL - Why Go Dynamic?
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone