Jurisdiction:

　　create session  Allow users to login to database privileges

　　create table   Allows users to create table permissions

　　unlimited tablespace  Allows users to build tables in other table spaces.

Role:

　　connect  

　　resource

　　dba

 

　　CONNECTRole: –It is a typical right to end-users, the most basic power.Ability to connect to the ORACLE database and do SELECT, UPDATE, INSERTT, etc. when you have access to other users’tables.
        ALTER SESSION –Modify the session
        CREATE CLUSTER –Cluster
        CREATE DATABASE LINK –Building database links
        CREATE SEQUENCE –Set up a sequence
        CREATE SESSION –Setting up a conversation
        CREATE SYNONYM –create synonym
        CREATE VIEW –Build a view
       RESOURCERole: –It is granted to developers.You can create tables, sequences, views and so on in your own plan.
        CREATE CLUSTER –Cluster
        CREATE PROCEDURE –Process of establishment
        CREATE SEQUENCE –Set up a sequence
        CREATE TABLE –Build a table
        CREATE TRIGGER –Building trigger
        CREATE TYPE –Type of establishment

　　DBArole，It is granted to the system administrator.The user who owns the role can become a system administrator. It has all the system privileges.

　　Example:

　　#sqlplus /nolog

　　SQL> conn / as sysdba;

　　SQL>create user username identified by password  –username/passwordUser defined.

　　SQL> grant dba to username;

　　SQL> conn username/password

　　SQL> select * from user_sys_privs;

　　We’ll start with the creation of Oracle user permission tables, and then explain general actions such as login to give you a deeper understanding of Oracle user permission tables.

　　1. Creation

　　sys;//System administrator has the highest permissions.

　　system;//Local administrators, sub high privileges

　　scott;//For ordinary users, the password is tiger by default, and the default is not unlocked.

　　Two, landing

　　sqlplus / as sysdba;//Landing sys account

　　sqlplus sys as sysdba;//Ditto

　　sqlplus scott/tiger;//Landing common user Scott

　　Three. Manage users

　　create user zhangsan;//Create user Zhangsan under administrator account

　　alert user scott identified by tiger;//Change Password

　　Four, grant authority.

　　1、The default common user Scott is unlocked by default, cannot be used that way, and the new user does not have any permissions and must be granted permissions.

　　

　　grant create session to zhangsan;//Grant Zhangsan user permission to create session, that is, login permission, allowing users to log in to database.

　　grant unlimited tablespace to zhangsan;//Grant Zhangsan users permission to use tablespace

　　grant create table to zhangsan;//Grant permission to create tables

　　grante drop table to zhangsan;//Grant permission to delete tables

　　grant insert table to zhangsan;//Permission to insert tables

　　grant update table to zhangsan;//Modify table permissions

　　grant all to public;//This is more important, granting all permissions (all) to all users (public).

　　2、oralcePrivilege management is more rigorous, ordinary users are also default can not access each other, need to authorize each other

　　

　　grant select on tablename to zhangsan;//Grant Zhangsan users permission to view specified tables

　　grant drop on tablename to zhangsan;//Grant permission to delete tables

　　grant insert on tablename to zhangsan;//Grant permission to insert

　　grant update on tablename to zhangsan;//Grant permission to modify tables

　　grant insert(id) on tablename to zhangsan;

　　grant update(id) on tablename to zhangsan;//Grants permission to insert and modify specific fields in specified tables. Note that only insert and update are allowed.

　　grant alert all table to zhangsan;//Grant Zhangsan user alert any table’s permission.

　　Five. Revocation of authority

　　The basic syntax is the same as grant, and the key word is revoke.

　　Six. View permissions

　　select * from user_sys_privs;//View all current user rights

　　select * from user_tab_privs;//View user access to the table

　　Seven, user table of operation table

　　

　　select * from zhangsan.tablename

　　Eight. Permission transfer

　　That is, the user A grants the permission to B, and B can grant the permission of the operation to C again. The command is as follows:

　　grant alert table on tablename to zhangsan with admin option;//Keyword with admin option

　　grant alert table on tablename to zhangsan with grant option;//Keyword with grant option effect similar to admin

　　Nine, the role

　　Role is a set of permissions that can be granted to a user.

　　create role myrole;//Create a role

　　grant create session to myrole;//Grant permission to create session to myrole

　　grant myrole to zhangsan;//Grant the role of Zhangsan user myrole

　　drop role myrole;Deleting a role