While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. Please re-enable javascript in your browser settings.
Grant Privileges on Table You can grant users various privileges to tables. ALL All privileges on table. Revoke Privileges on Table Once you have granted privileges, you may need to revoke some or all of these privileges. A user can receive a privilege in two different ways:. Because roles allow for easier and better management of privileges, you should normally grant privileges to roles and not to specific users.
Oracle9i Database Administrator's Guide for a complete list of all system and schema object privileges, as well as instructions for privilege management. A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type.
For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges. There are over 60 distinct system privileges. You can grant or revoke system privileges to users and roles.
If you grant system privileges to roles, then you can use the roles to manage system privileges. For example, roles permit privileges to be made selectively available. In general, you grant system privileges only to administrative personnel and application developers. End users normally do not require the associated capabilities.
A schema object privilege is a privilege or right to perform a particular action on a specific schema object:. Different object privileges are available for different types of schema objects. For example, the privilege to delete rows from the departments table is an object privilege. Some schema objects, such as clusters, indexes, triggers, and database links, do not have associated object privileges.
Their use is controlled with system privileges. A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym.
For example, assume there is a table jward. The user swilliams can query jward. If you grant object privileges on a table, view, sequence, procedure, function, or package to a synonym for the object, the effect is the same as if no synonym were used.
For example, if jward wanted to grant the SELECT privilege for the emp table to swilliams , jward could issue either of the following statements:. If a synonym is dropped, all grants for the underlying schema object remain in effect, even if the privileges were granted by specifying the dropped synonym. Schema object privileges can be granted to and revoked from users and roles. If you grant object privileges to roles, you can make the privileges selectively available.
Object privileges for users and roles can be granted or revoked using the following:. A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. Otherwise, the grantee can use the privilege, but cannot grant it to other users.
Oracle9i SQL Reference. Grant these privileges only to users and roles that need to query or manipulate a table's data. All other columns receive NULL or the column's default value. Alternatively, a view that excludes the salary column could satisfy this need for additional security. Because these privileges allow other users to alter or create dependencies on a table, you should grant privileges conservatively. A user attempting to perform a DDL operation on a table may need additional system or object privileges.
This action is controlled with a special privilege because the presence of foreign keys restricts the data manipulation and table alterations that can be done to the parent key. Chapter 21, "Data Integrity" for more information about primary keys, unique keys, and integrity constraints.
Schema object privileges for views allow various DML operations, which actually affect the base tables from which the view is derived. DML object privileges for tables can be applied similarly to views. To use a view, you require appropriate privileges only for the view itself. You do not require privileges on base objects underlying the view. Views add two more levels of security for tables, column-level security and value-based security:.
This view combines both column-level security and value-based security. Grant this privilege only to users who need to execute a procedure or compile another procedure that calls it. A user with the EXECUTE object privilege for a specific procedure can execute the procedure or compile a program unit that references the procedure.
No runtime privilege check is made when the procedure is called. Additional privileges on referenced objects are required for invoker-rights procedures, but not for definer-rights procedures. A user of a definer-rights procedure requires only the privilege to execute the procedure and no privileges on the underlying objects that the procedure accesses, because a definer-rights procedure operates under the security domain of the user who owns the procedure, regardless of who is executing it.
The procedure's owner must have all the necessary object privileges for referenced objects. Fewer privileges have to be granted to users of a definer-rights procedure, resulting in tighter control of database access.
You can use definer-rights procedures to control access to private database objects and add a level of database security. By writing a definer-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 runtime, the privileges of the owner of a definer-rights stored procedure are always checked before the procedure is executed.
If a necessary privilege on a referenced object has been revoked from the owner of a definer-rights procedure, then the procedure cannot be executed by the owner or any other user. Trigger execution follows the same patterns as definer-rights procedures. The user executes a SQL statement, which that user is privileged to execute. As a result of the SQL statement, a trigger is fired.
The statements within the triggered action temporarily execute under the security domain of the user that owns the trigger. An invoker-rights procedure executes with all of the invoker's privileges. Roles are enabled unless the invoker-rights procedure was called directly or indirectly by a definer-rights procedure. A user of an invoker-rights procedure needs privileges either directly or through a role on objects that the procedure accesses through external references that are resolved in the invoker's schema.
The invoker needs privileges at runtime to access program references embedded in DML statements or dynamic SQL statements, because they are effectively recompiled at runtime. Therefore, the user of an invoker-rights procedure needs no privileges on external references outside DML or dynamic SQL statements. Alternatively, the developer of an invoker-rights procedure only needs to grant privileges on the procedure itself, not on all objects directly referenced by the invoker-rights procedure.
You can create a software bundle that consists of multiple program units, some with definer rights and others with invoker rights, and restrict the program entry points controlled step-in. A user who has the privilege to execute 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 must have been explicitly granted the necessary privileges system or object on all objects referenced by the procedure.
You cannot have obtained the required privileges through roles. Triggers also require that privileges to referenced objects be granted explicitly to the trigger owner.
Retrieving all user privileges within Oracle can range from a simple task using a basic SQL query to an advanced script, depending primarily on how involved the roles and privileges are configured within the server.
For example, a DBA wishing to view all system privileges granted to all users would issue the following query:. 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.
0コメント