How many object privileges in oracle 11g

See "Type Evolution" for information about how to alter a type. Just as you can create synonyms for tables, views, and various other schema objects, you can also define synonyms for object types.

Synonyms for types have the same advantages as synonyms for other kinds of schema objects: they provide a location-independent way to reference the underlying schema object. An application that uses public type synonyms can be deployed unaltered, in any schema of a database, without requiring a qualified type name with the schema name. Synonyms can be created for collection types, too.

The following example creates a synonym for a nested table type:. For example, the following statement causes syn1 to point to type typ2 instead of the type it formerly pointed to:.

You can use a type synonym anywhere that you can refer to a type. For instance, you can use a type synonym in a DDL statement to name the type of a table column or type attribute. Example uses synonym syn1 to specify the type of an attribute in type typ3 :.

In the next statement, the type synonym syn1 calls the constructor of the object type typ1 , for which syn1 is a synonym. The statement returns an object instance of typ1 :. In the following, syn2 is a synonym for a nested table type. The synonym replaces the actual type name in a CAST expression. A type that directly or indirectly references a synonym in its type declaration is a dependent of that synonym.

Thus, in the following line from Example , type typ3 is a dependent type of synonym syn1. Other kinds of schema objects that reference synonyms in their DDL statements also become dependents of those synonyms. An object that depends on a type synonym depends on both the synonym and the underlying type of the synonym. The dependency relationships of a synonym affect your ability to drop or rename the synonym.

Dependent schema objects are also affected by some operations on synonyms. The following sections describe these various ramifications. You can replace a synonym only if it has no dependent tables or valid user-defined types. Replacing a synonym is equivalent to dropping it and then re-creating a new synonym with the same name.

You cannot drop a type synonym if it has table or valid object types as dependents unless you use the FORCE option. The FORCE option causes any columns that directly or indirectly depend on the synonym to be marked unused, just as if the actual types of the columns were dropped. A column indirectly depends on a synonym if, for instance, the synonym is used to specify the type of an attribute of the declared type of the column. Any dependent schema objects of a dropped synonym are invalidated.

They can be revalidated by creating a local object or a new public synonym with the same name as the dropped synonym.

Dropping the underlying base type of a type synonym has the same effect on dependent objects as dropping the synonym.

Renaming a synonym is equivalent to dropping it and then re-creating it with a new name. You cannot rename a type synonym if it has dependent tables or valid object types. The following example fails because synonym syn1 has a dependent object type:. You cannot create a local schema object that has the same name as a public synonym if the public synonym has a dependent table or valid object type in the local schema that will hold the new schema object.

Nor can you create a local schema object that has the same name as a private synonym in the same schema. Skip Headers. The DBA role includes all of these privileges. Define a column in a relational table. Declare a variable or parameter of the named type. Using Types in New Types or Tables In addition to the permissions detailed in the previous sections, you need specific privileges to: Create types or tables that use types created by other users.

Grant use of your new types or tables to other users. A user with the EXECUTE object privilege for a specific procedure can execute the procedure or compile a program unit that references the procedure.

Privileges to run procedures can be granted to a user through roles. The owner of a procedure, called the definer , must have all the necessary object privileges for referenced objects. If the procedure owner grants to another user the right to use that procedure, then the privileges of the procedure owner on the objects referenced by the procedure apply to the grantee user's exercise of the procedure. The privileges of the procedure's definer must be granted directly to the user, not granted through roles.

These are termed definer's rights. The user of a procedure who is not its owner is called the invoker. Additional privileges on referenced objects are required for invoker's rights procedures, but not for definer's rights procedures. A user of a definer's rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses. This is because a definer's rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it.

The owner of the procedure must have all the necessary object privileges for referenced objects. Fewer privileges have to be granted to users of a definer's rights procedure. This results in stronger control of database access. You can use definer's rights procedures to control access to private database objects and add a level of database security. By writing a definer's rights procedure and granting only EXECUTE privilege to a user, the user can be forced to access the referenced objects only through the procedure.

At run time, Oracle Database checks whether the privileges of the owner of a definer's rights stored procedure allow access to that procedure's referenced objects, before the procedure is executed. If a necessary privilege on a referenced object was revoked from the owner of a definer's rights procedure, then the procedure cannot be run by the owner or any other user. An invoker's rights procedure executes with all of the invoker's privileges. Oracle Database enables the privileges that were granted to the invoker through any of the invoker's enabled roles to take effect, unless a definer's rights procedure calls the invoker's rights procedure directly or indirectly.

A user of an invoker's rights procedure needs privileges granted to the user either directly or through a role on objects that the procedure accesses through external references that are resolved in the schema of the invoker.

The invoker needs privileges at run time to access program references embedded in DML statements or dynamic SQL statements, because they are effectively recompiled at run time. Therefore, the user of an invoker's rights procedure does not need privileges on external references outside DML or dynamic SQL statements. Alternatively, the developer of an invoker's rights procedure must only grant privileges on the procedure itself, not on all objects directly referenced by the invoker's rights procedure.

You can create a software bundle that consists of multiple program units, some with definer's rights and others with invoker's rights, and restrict the program entry points controlled step-in.

A user who has the privilege to run an entry-point procedure can also execute internal program units indirectly, but cannot directly call the internal programs. The user who owns the procedure also must have privileges for schema objects referenced in the procedure body. To create a procedure, you need to have been explicitly granted the necessary privileges system or object on all objects referenced by the procedure. You cannot obtain the required privileges through roles. You can recompile procedures in your own schema without any privileges.

A user with the EXECUTE object privilege for a package can execute any public procedure or function in the package, and can access or modify the value of any public package variable. Therefore, you may find it useful to consider two alternatives for establishing security when developing procedures, functions, and packages for a database application. The following examples describe these alternatives.

Example shows four procedures created in the bodies of two packages. This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.

The following sections describe the use of privileges for types, methods, and objects:. The DBA role includes all of these privileges. To create a type, you must meet the following requirements:. These privileges can be acquired explicitly or through a role.

The owner cannot obtain the required privileges through roles. If not, then the type owner has insufficient privileges to grant access on the type to other users.

To create a table using types, you must meet the requirements for creating a table and the following additional requirements:. The owner cannot exercise the required privileges if these privileges were granted through roles.

If not, then the table owner has insufficient privileges to grant access on the table. Existing column-level and table-level privileges for DML statements apply to both column objects and row objects. Similar table privileges and column privileges apply to column objects. Retrieving instances does not in itself reveal type information. However, clients must access named type information to interpret the type instance images. The second query, however, does not involve named types, so Oracle Database does not check type privileges.

In addition, by using the schema from the previous section, user3 can perform the following queries:. Oracle Database checks privileges on the following events, and returns an error if the client does not have the privilege for the action:. Modifying an existing object or flushing an object from the object cache causes Oracle Database to check for the UPDATE privilege on the destination object table.

Modifying the attributes of an object in a client third-generation language application causes Oracle Database to update the entire object. Having the UPDATE privilege on only certain columns of the object table is not sufficient, even if the application only modifies attributes corresponding to those columns. Therefore, Oracle Database does not support column-level privileges for object tables.

As with stored objects, such as procedures and tables, types being referenced by other objects are called dependencies. There are some special issues for types on which tables depend. Because a table contains data that relies on the type definition for access, any change to the type causes all stored data to become inaccessible. Changes that can cause this are when necessary privileges required to use the type are revoked, or the type or dependent types are dropped. If these actions occur, then the table becomes invalid and cannot be accessed.

A table that is invalid because of missing privileges can automatically become valid and accessible if the required privileges are granted again.

A table that is invalid because a dependent type was dropped can never be accessed again, and the only permissible action is to drop the table.

This means that if the named type in either statement has table or type dependents, then an error is received and the statement cancels. If there are depended-upon tables, then they are invalidated.

It is also possible to grant roles to a user connected through a middle tier or proxy. The following privileges are required:. The grantee can grant or revoke the system privilege or role to or from any other user or role in the database.

Users cannot revoke a role from themselves. Because of these powerful capabilities, use caution when granting system privileges or roles with the ADMIN option. These privileges are usually reserved for a security administrator, and are rarely granted to other administrators or users of the system.

To grant an object privilege, you must fulfill one of the following conditions:. This privilege enables you to grant and revoke privileges on behalf of the object owner. To grant all object privileges on the salary view to user jfee , use the ALL keyword as shown in the following example:.

This special privilege allows the grantee several expanded privileges:. If both of the following conditions are true, then the grantee can create views on the table, and grant the corresponding privileges on the views to any user or role in the database:. This privilege provides a convenient means for database and application administrators to grant access to objects in any schema without requiring that they connect to the schema. Login credentials do not need to be maintained for schema owners who have this privilege, which reduces the number of connections required during configuration.

For example, consider the following scenario. He does not possess any other grant privileges. He issues the following statement:. In the following example, object privilege for the ename and job columns of the emp table are granted to the users jfee and tsmith :. Oracle Label Security Administrator's Guide. Any user with the ADMIN option for a system privilege or role can revoke the privilege or role from any other database user or role.

The revoker does not have to be the user that originally granted the privilege or role. To revoke an object privilege, you must fulfill one of the following conditions:. You can only revoke the privileges that you, the person who granted the privilege, directly authorized. However, there is a cascading effect. This can be illustrated by continuing the example started in "Granting Object Privileges on Behalf of the Object Owner".

Only the object privilege for user clark granted by user blake is removed. The grant by the object owner, HR , remains. Instead, the grantor must first revoke the object privilege for all columns of a table or view, and then selectively repeat the grant of the column-specific privileges that the grantor intends to keep in effect. Depending on the type of privilege, there may be cascading effects when a privilege is revoked. This is discussed in the following sections:.

There are no cascading effects when revoking a system privilege related to DDL operations, regardless of whether the privilege was granted with or without the ADMIN option. For example, assume the following:. The table created by user jfee continues to exist. You can observe cascading effects when you revoke a system privilege related to a DML operation. Revoking an object privilege can have cascading effects. Remember the following:. For example, assume that the body of the test procedure includes a SQL statement that queries data from the emp table.

If the SELECT privilege on the emp table is revoked from the owner of the test procedure, then the procedure can no longer be executed successfully. This user now creates a foreign key on the deptno column in the emp table that references the deptno column of the dept table. Any objects that depend on the revoked SELECT privilege of user1 and user2 can also be affected, as described earlier. For example, if the INDEX privilege is revoked from a user that created an index on a table that belongs to another user, then the index continues to exist after the privilege is revoked.

Security administrators and database users should grant a privilege or role to PUBLIC only if every database user requires the privilege or role. This recommendation reinforces the general rule that, at any given time, each database user should have only the privileges required to accomplish the current group tasks successfully. Roles can be administered using the operating system and passed to Oracle Database when a user creates a session.

As part of this mechanism, the default roles of a user and the roles granted to a user with the ADMIN option can be identified. If the operating system is used to authorize users for roles, then all roles must be created in the database and privileges assigned to the role with GRANT statements.

The advantage of using the operating system to identify the database roles of a user is that privilege management for an Oracle database can be externalized. The security facilities offered by the operating system control user privileges. This option may offer advantages of centralizing security for several system activities, such as the following situation:. The main disadvantage of using the operating system to identify the database roles of a user is that privilege management can only be performed at the role level.

Individual privileges cannot be granted using the operating system, but they can still be granted inside the database using GRANT statements. A second disadvantage of using this feature is that, by default, users cannot connect to the database through the shared server or any other network connection if the operating system is managing roles.

When a user tries to create a session with the database, Oracle Database initializes the user security domain using the database roles identified by the operating system.

To identify database roles for a user, the operating system account for each Oracle Database user must have operating system identifiers these may be called groups, rights identifiers, or other similar names that indicate which database roles are to be available for the user. Role specification can also indicate which roles are the default roles of a user and which roles are available with the ADMIN option. No matter which operating system is used, the role specification at the operating system level follows the format:.

ID has a definition that varies on different operating systems. This allows the user to grant the role to other roles only. Roles cannot be granted to users if the operating system is used to manage roles. For example, an operating system account might have the following roles identified in its profile:. When the corresponding user connects to the payroll instance of Oracle Database, role3 and role4 are defaults, while role2 and role4 are available with the ADMIN option.

When you use operating system-managed roles, remember that database roles are being granted to an operating system user. Any database user to which the operating system user is able to connect will have the authorized database roles enabled.

However, they are still listed in the data dictionary. Only the role grants to users made at the operating system level apply. Users can still grant privileges to roles and users. This still applies, even if the role was defined to require a password or operating system authorization. If you specify such a role, then Oracle Database ignores it. Remember that this number includes other roles that may have been granted to the role. If you have the operating system manage roles, then, by default, users cannot connect to the database through the shared server.

This restriction is the default because a remote user could impersonate another operating system user over an unsecure connection.

The change takes effect the next time you start the instance and mount the database. Depending on what is granted or revoked, a grant or revoke takes effect at different times:. All grants and revokes of roles to anything users, other roles, PUBLIC take effect only when a current user session issues a SET ROLE statement to reenable the role after the grant and revoke, or when a new user session is created after the grant or revoke.

During the user session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. Example enables the role clerk , which you have already been granted, and specifies the password. Replace password with a password that is secure. When a user logs on, Oracle Database enables all privileges granted explicitly to the user and all privileges in the default roles of the user.

Example sets the default roles payclerk and pettycash for user jane :. When you first create a user, the default user role setting is ALL , which causes all roles subsequently granted to the user to be default roles. A user can enable no more than roles. You can grant a user as many roles as you want, but you should restrict the number of roles granted to a user to the minimum roles the user needs.

See "Guidelines for Securing Roles" for additional guidelines on granting roles to users. Configuring fine-grained access control for users and roles that need to access external network services from the database. This way, specific groups of users can connect to one or more host computers, based on privileges that you grant them.

Typically, you use this feature to control access to applications that run on specific host addresses. Configuring fine-grained access control to Oracle wallets to make HTTP requests that require password or client-certificate authentication. For example, you can configure applications to use the credentials stored in the wallets instead of hard-coding the credentials in the applications.

For more information about how you can use wallets to store passwords and credentials, see Oracle Database Advanced Security Administrator's Guide. This guide explains how to use these packages to create and manage the access control list. This guide explains how to manage access control to both versions. When a user accesses Web pages that are protected by a remote Web server, the user can authenticate himself or herself by supplying the passwords and client certificates that are stored in an Oracle wallet.

The Oracle wallet provides secure storage of user passwords and client certificates. An Oracle wallet. The HTTP request will use the external password store or the client certificate in the wallet to authenticate the user.

An access control list to grant privileges to the user to use the wallet. A way to associate the wallet with the access control list. The use of wallets is beneficial because it provides secure storage of passwords and client certificates necessary to access protected Web pages. If you have upgraded from a release before Oracle Database 11 g Release 1 Use the procedures in this section to reconfigure the network access for the application.

When you create access control lists for network connections, you should create one access control list dedicated to a group of common users, for example, users who need access to a particular application that resides on a specific host computer. For ease of administration and for good system performance, do not create too many access control lists. Network hosts accessible to the same group of users should share the same access control list.

It contains a name of the access control list, a brief description, and privilege settings for one user or role that you want to associate with the access control list. In an access control list, privileges for each user or role are grouped together as an access control entry ACE. An access control list must have the privilege settings for at least one user or role. Include the. Enter the name of the user account or role in case sensitive characters.

Typically, user names and roles are stored in upper-case letters. This setting is case sensitive, so always enter it in lowercase. The connect privilege grants the user permission to connect to a network service at an external host.

The resolve privilege grants the user permission to resolve a network host name or an IP address. You can use the data dictionary views described in "Finding Information About Access Control Lists Configured for User Access" to find more information about existing privileges and network connections.

FF TZR. When specified, the access control entry will be valid only on or after the specified date. The default is null.

For example, to set a start date of February 28, , at a. See Oracle Database Reference for more information. When specified, the access control entry expires after the specified date. For example, to set an end date of December 10, , at p. The syntax is as follows:. Because you now are adding more than one user or role, you may want to consider setting their precedence. At this stage, you have created an access control list that defines the privileges needed to connect to a network host.

After you create the access control list, then you are ready to assign it to one or more network host computers. This setting can be a name or IP address of the network host. Host names are case insensitive. See the following sections for more information about how network host computers in access control list assignments work:.

Use this setting for the connect privilege only; omit it for the resolve privilege. The default is null , which means that there is no port restriction that is, the ACL applies to all ports. The range of port numbers is between 1 and Use this setting for connect privileges only; omit it for resolve privileges. The resolve privilege in the access control list takes no effect when a port range is specified in the access control list assignment. Only one access control list can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range.

When you assign a new access control list to a network target, Oracle Database unassigns the previous access control list that was assigned to the same target. However, Oracle Database does not drop the access control list. Depending on how you create and maintain the access control list, the two steps may overlap.

For example, you can create an access control list that has privileges for five users in it, and then apply it to two host computers. Later on, you can modify this access control list to have different or additional users and privileges, and assign it to different or additional host computers. All access control list changes, including the assignment to network hosts, are transactional. They do not take effect until the transaction is committed.

You can find information about existing privileges and network connections by using the data dictionary views described in Table , "Data Dictionary Views That Display Information about Access Control Lists".

This method lets you grant access to the passwords and client certificates that are stored in an Oracle wallet to users to authenticate themselves to an external Web server. This enables the user to retrieve protected Web pages from the Web server.

To create the wallet, you can use either the mkstore command-line utility or the Oracle Wallet Manager user interface. To store passwords in the wallet, you must use mkstore. You can use both standard and PKCS11 wallet types, and the wallet can be an auto-login wallet if you want. Make a note of the directory in which you created the wallet.

You will need this directory path when you complete the procedures in this section. After you have created the wallet, you are ready to create the access control list that will assign the password or client certificate privilege the user needs to use password credentials in the wallet for HTTP authentication. Enter this name using case sensitive characters. Remember that the privilege name is case-sensitive. In this step, you assign this access control list to the wallet you created earlier.

When you specify the wallet path, you must use an absolute path and include file: before this directory path. This object stores a randomly-generated numeric key that Oracle Database uses to identify the request context. You will refer to this object later on, when you set the user name and password from the wallet to access a password-protected Web page.

Ensure that this path is the same path you specified when you created access control list in Step 3: Assign the Access Control List to the Wallet in the previous section.

You must include file: before the directory path. The default is NULL , which is used for auto-login wallets. This object prevents the wallet from being shared with other applications in the same database session. You should use a request context to hold the wallet when the database session is shared with other applications. Use this scheme only if you are configuring access to the Amazon. Contact Amazon for more information about this setting.

The use of the user name and password in the wallet requires the use-passwords privilege to be granted to the user in the ACL assigned to the wallet. The following examples demonstrate how to create access control lists. Example shows how you would create an access control list called us-example-com-permissions. This example creates the us-example-com-permissions. The XML file appears as follows:. The xmlns and xsi elements are fixed and should not be modified, for example, in a text editor.

Example shows how to create a slightly more complex version of the us-example-com-permissions. In this example, you specify multiple role privileges and their precedence position, and assigned to multiple host computers.

These roles use the use-passwords privilege to access passwords stored in the wallet. In this example, the wallet will not be shared with other applications within the same database session. Example is almost the same as Example , except that it configures the wallet to be used for a shared database session; that is, all applications within the current database session will have access to this wallet.

The asterisk wildcard must be at the beginning, before a period. Be aware that the use of wildcard characters affects the order of precedence for multiple access control lists that are assigned to the same host computer.

You cannot use wildcard characters for IPv6 addresses. This package considers an IPv4-mapped IPv6 address or subnet equivalent to the IPv4-native address or subnet it represents.

For example, ::ffff For multiple access control lists that are assigned to the host computer and its domains, the access control list that is assigned to the host computer takes precedence over those assigned to the domains. The access control list assigned to a domain has a lower precedence than those assigned to the subdomains. For example, Oracle Database first selects the access control list assigned to the host server. If additional access control lists were assigned to the sub domains, their order of precedence is as follows:.

Similarly, for multiple access control lists that are assigned to the IP address both IPv4 and IPv6 and the subnets it belongs to, the access control list that is assigned to the IP address takes precedence over those assigned to the subnets. You can check the official documentation for more information about the columns returned from this query, but the critical columns are:.

While the above methods will work for basic system configurations, things start to become messy in Oracle when many roles exist which are in turn granting role privileges to other roles, and so on down the rabbit hole.

In either case, the purpose of these scripts is to allow you to recursively locate all privileges granted to a particular user. When the script locates a role for the user, it recursively searches for other roles and privileges granted to that role, repeating the process all the way down the chain. The results of the script can be output to the screen or to a file as desired.