Oracle10 Permissions Role

Manage permissions and Roles
Introduced

In this section we'll look at how Oracle manages permissions and roles, and the difference between permissions and roles is there. When a user has just been created, the user does not have any permissions and cannot perform any action, and theOracle database automatically creates a scheme with the same schema name and user name as the data objects (tables, views, triggers, stored procedures). If you want to perform a specific database operation, you must grant it permissions to the system, and if the user wants to access data objects for other scenarios (other users), you must grant them permission to the object . To simplify the management of permissions, you can use roles ( a role can contain the corresponding system and object permissions

  public   synonym    Synonyms CREATE PROCEDURE build process, function, package    Create trigger  trigger Create cluster cluster display system permissions Oracle offers over 100 systems , and the higher the Oracle version, the more system permissions are available, and we can query the data dictionary view System_privilege_map to show all system permissions.  select  * from  System_privilege_ Map order by name; 

Grant system permissions in general, the grant of system permissions is done by the DBA, and if you use another user to grant system permissions, the user is required to have the system permissions of grant any privilege. When granting system permissions, you can have the WITH ADMIN option option so that the user or role that is granted permission can also grant other users or roles the system permission (which can only be granted by someone else, and not be granted to him ). To give you a quick understanding, let's illustrate:1. Create two user ken,tom. They do not have any permissions at the initial stage, and if they log in they will give the wrong information. Create user Ken Identfied by123456; 2Grant to user Ken1). Grant create session, create table to Ken with admin option; 2). Grant CREATE view to Ken; 3to the user Tom authorized we can grant Tom by Ken, because with the admin option is added. Of course, we can authorize Tom through the DBA, and we'll use Ken to give Tom authorization:1. Grant create session, create table to Tom;2.  Grant CREATE view to Ken; --ok? Not OK

Recycle system permissions in general, the recovery system permissions are DBA to complete, if other users to reclaim system permissions, require that the user must have the appropriate system permissions and the option to delegate system permissions (with admin option).   The recycle system permissions are done using revoke. When the system permissions are reclaimed, the user cannot perform the appropriate action, but notice that the SYSTEM permission cascade recovery problem ?   --------->ken----------> from Ken; --

object permissions refer to the right to access other schema objects (other users) , and users can access their own schema objects directly, but if you want to access objects of other scenarios, you must have permission to the object. For example, Smith users to access the Scott.emp table (Scott: Scheme , EMP: table) commonly used are: Alter modify DELETE deleteSelectQuery Insert Add Update Modify index index references reference execute to perform display object permissions: The data field view allows you to display the object permissions that the user or role has. View as Dba_tab_privs SQL> Conn system/Manager; SQL>SelectDistinct privilege fromDba_tab_privs; SQL>SelectGrantor, owner, TABLE_NAME, privilege fromDba_tab_privswheregrantee ='BLAKE'; 1Grant object permissions before oracle9i, granting object permissions is done by the owner of the object, and if it is manipulated by other users, it requires the user to have the corresponding (with GRANT OPTION) permission, starting with Oracle9i, the DBA user (SYS, System) can grant object permissions on any object to other users.   The Grant object permission is done with the grant command. Object permissions can be granted to users, roles, and public. When you grant permissions, you can delegate this permission to other users if you have the WITH GRANT option. However, be aware that the WITH GRANT option cannot be granted a role. 1. Monkey user to manipulate the Scott.emp table, you must grant the appropriate object permissions1).    Hope Monkey can query the data of Scott.emp table, how to operate? GrantSelectOn emp to monkey;2).    I hope monkey can modify the Scott.emp table data, how to do it? Grant update on EMP to monkey; 3).    I hope monkey can delete the Scott.emp table data, how to do it? Grant Delete on the EMP to monkey; 4).    Is there a simpler way to assign all permissions to monkey at once? Grant all on the EMP to monkey; 2the ability to have finer control over monkey access rights. (Grant column permissions)1).    I hope monkey can only modify the Sal field of the Scott.emp table, how to do it? Grant Update on EMP (SAL) to Monkey2). Hope Monkey can only query the ename,sal data of scott.emp table, how to do it? GrantSelectOn EMP (ename,sal) to monkey ...3. Grant ALTER permission if the black user wants to modify the structure of the Scott.emp table, you must grant the Alter object permission SQL> Conn scott/Tiger SQL>Grant ALTER on the EMP to Blake; Of course you can do it with System,sys. 4. Grant execute permissions if the user wants to execute packages for other scenariosProcessfunction, you must have execute permission. For example, to allow Ken to execute Package dbms_transaction, execute permissions can be granted. SQL> Conn system/Manager SQL>Grant execute on Dbms_transaction to Ken;5. Give index permission if you want to index on a table in another scenario, you must have the Index object permission. If you want black to be able to index on the Scott.emp table, give it the object permission SQL for index> Conn scott/Tiger SQL>Grant Index on scott.emp to Blake;6the option to use the WITH GRANT option is used to delegate object permissions. However, this option can only be granted to the user and cannot be granted to the role SQL> Conn scott/Tiger; SQL> GrantSelectOn EMP to Blake with GRANT option; SQL> Conn black/shunping SQL> GrantSelectOn Scott.emp to Jones;   Reclaim object Permissions in Oracle9i, the permission to retract an object can be done by the owner of the object, or by the DBA User (Sys,system). The point here is that the user will not be able to execute the corresponding SQL command after the object permission is retracted, but be aware that the object's permissions will be cascaded back up? "Cascade recycle" such as: Scott------------->blake-------------->JonesSelectOn EMPSelectOn EMPSelectOn EMP SQL> Conn scott/[email protected] SQL> RevokeSelectOn EMP fromBlake ask everyone to think, Jones can query scott.emp table data. Answer: Not check (and system permissions are not the same, just the opposite)

15: Role Roles are a collection of commands related to permissions, and the primary purpose of using roles is to simplify the management of permissions, assuming that there are user a,b,c in order for them to have permissions1. Connect to the database2. select,insert,update on the Scott.emp table. If you take a direct authorization operation, you need to authorize 12 times. Because of the 12 authorization operation, so it is more troublesome!   What to do? If we take a role, we can simplify it: first creat session,Selecton Scott.emp,insert in Scott.emp, update on SCOTT.EMP grants the role and then grants the role to the A,b,c user, so that three authorizations can be done. Roles are divided into pre-defined and custom roles : Predefined roles predefined roles are those provided by Oracle, each of which is used to perform specific administrative tasks, and we describe common predefined roles connect,resource,dba1The Connect role connect role has most of the permissions that a general application developer needs, and when a user is established, in most cases, as long as the user is granted the Connect and resource roles, what system permissions does the connect role have? Alter session modify sessions create cluster modify a cluster CREATE DATABASE link modify a DB connection create session Connect database Creating table creates a view creat E Sequence Create sequence2The . Resource role Resource role has additional permissions that the application developer needs, such as setting up stored procedures, triggers, and so on. It is important to note that the resource role implies unlimited tablespace system permissions. The resource role contains the following system permissions: Create cluster create indextype CREATE TABLE create sequence create type CREATE PROCEDURE create Trigg ER3The DBA role has all system permissions, and with the admin option option, the default DBA user is SYS and system, and they can grant any system permissions to other users.  Note, however, that the DBA role does not have the privileges of SYSDBA and sysoper (starting and shutting down the database). Custom roles are defined by their own definition, as the name implies. Typically a DBA is established, and if it is established with another user, the system permissions that have the Create role are required. You can specify authentication methods (no validation, database validation, and so on) when you establish a role. 1. Establish a role (not verified) if the role is a public role, you can establish the role in an unauthenticated manner. Create role name not identified; 2the role name and password are stored in the database when the role is established (database validation) in this way. When the role is activated, the password must be supplied. When you establish this role, you need to provide it with a password ( the permissions to modify the role later to provide a password ). Create role name identified by password; 

role authorization When a role is established, the role does not have any permissions, and in order for the role to complete a specific task, it must be granted the appropriate system and object permissions. 1there is not much difference between granting permissions to roles and authorizing users, but be aware that the WITH GRANT option option for unlimited tablespace and object permissions for system permissions is not available in the grant role only for Grant users. SQL> Conn system/Manager; SQL>Grant create session to Role name with admin option (create session is Connect database permissions, System permissions ) SQL> Conn scott/[email protected]; ( preferably whose table gives the role the appropriate permissions ) SQL> GrantSelectOn scott.emp to role name; SQL>grant INSERT, UPDATE, delete on scott.emp to role name; The above steps give the role authorization. 2assigning roles to a user typically assigns roles to the DBA, and if you want to assign roles as other users, the user must have system permissions for the grant any role. SQL> Conn system/Manager; SQL>Grant role name to Blake with admin option; Because I gave the WITH admin option option, Blake can assign the role assigned to it to another user (Blake can connect to the database,  And can query the Scoot EMP table ). The delete role uses drop roles, which is typically performed by the DBA, and if other users require that the user have drop any role system permissions. SQL> Conn system/Manager; SQL>drop role name; question: If the role is deleted, does the user who is granted the role also have permissions in the previous role? Answer: Do not have a

Oracle10 Permissions Role