Shulou(Shulou.com)06/01 Report--

We will start with the creation of the Oracle user rights table, and then learn general actions such as login to give you an in-depth understanding of Oracle user rights allocation control.

First, create

Sys; / / system administrator with the highest privileges

System;// local administrator, second highest privilege

Scott; / / ordinary user. The password defaults to tiger and is not unlocked by default.

II. Landing

Sqlplus / as sysdba; / / Log in to sys account

Sqlplus sys as sysdba;// ditto

Sqlplus scott/tiger; / / Log in to ordinary user scott

III. Manage users

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

Alert user scott identified by tiger;// change password

Fourth, grant authority

1. The default ordinary user scott is not unlocked by default and cannot be used. The newly created user does not have any permissions and must be granted permissions.

Grant create session to zhangsan; / / Grant zhangsan users the right to create session, that is, login permission

Grant unlimited session to zhangsan;// grants zhangsan users permission to use tablespaces

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

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

Permissions for grant insert table to zhangsan;// to insert tables

Permissions for grant update table to zhangsan;// to modify tables

Grant all to public; / / this is more important, granting all permissions (all) to all users (public)

2. Oralce has strict rights management, and ordinary users cannot access each other by default, so they need to authorize each other.

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

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

Insert permission granted by grant insert on tablename to zhangsan;//

Grant update on tablename to zhangsan;// grants permission to modify the table

Grant insert (id) on tablename to zhangsan

Grant update (id) on tablename to zhangsan;// grants insert and modify permissions to specific fields of the specified table. Note that it can only be insert and update

Grant alert all table to zhangsan;// grants permissions to zhangsan users to alert any table

V. revocation of authority

The basic syntax is the same as grant, and the keyword is revoke

VI. View permissions

Select * from user_sys_privs;// to view all permissions of the current user

Select * from user_tab_privs;// to view the permissions of the user used on the table

7. The table of the user who operates the table

Select * from zhangsan.tablename

VIII. Authority transfer

That is to say, user A can grant the permission of operation to C if user A grants the permission to Bmai B. the command is as follows:

Grant alert table on tablename to zhangsan with admin option;// keyword with admin option

The effect of the grant alert table on tablename to zhangsan with grant option;// keyword with grant option is similar to admin.

IX. Role

A role is a collection of permissions that can be granted to a user.

Create role myrole;// creates roles

Grant create session to myrole;// grants myrole the right to create a session

Grant myrole to zhangsan;// grants the role of myrole to the zhangsan user

Drop role myrole; Delete roles

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.