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

Authorization is divided into: grantee (grantor), grantee (grantee)

Grant and revoke permissions

Permissions are divided into system permissions and object permissions

I. system permissions

There are about 200 system permissions, most of which affect the operation of the data dictionary. Common permissions are as follows:

CREATE SESSION (create session)-allows users to connect

RESTRICTED SESSION (restricted session)-if you start the database with STARTUP RESTRICT or adjust using ALTER SYSTEM ENABLE RESTRICTED SESSION, only users with this permission can connect.

ALTER DATABASE (change database)-allows access to multiple commands needed to modify the physical structure

ALTER SYSTEM (change system)-allows you to control instance parameters and memory structure

CREATE TABLESPACE (create tablespaces)-ALTER TABLESPACE and DROP TABLESPACE permissions allow users to manage tablespaces

CREATE TABLE (create table)-- you need to change and delete the table, allow the SELECT and DML commands on it, and create, change, or delete indexes on it.

GRANT ANY OBJECT PRIVILEGE (Grant permissions to any object)-allows the licensee to grant object permissions to others for objects that he does not own, but not to himself.

CREATE ANY TABLE (create any table)-an authorized person can create a table that belongs to another user.

DROP ANY TABLE (delete any table)

INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE (insert any table, update any table, delete any table)

SELECT ANY TABLE (Select any table) the authorized person can perform select operations on any table in the database.

1. The syntax for granting permissions is as follows:

GRANT privilege [, privilege...] TO username

After the user account is created, the following command grants the system permissions that are normally assigned to the user (the user involved in application development)

Grant create session, alter session, create table, create view, create synonym, create cluster,create database link, create sequence, create trigger, create type, create procedure, create operator TO username

2. A variation of this syntax allows the licensee to pass permissions to a third party, for example:

SQL > connect system/oracle

SQL > grant create table to scott with admin option

SQL > connect scott/tiger

SQL > grant create table to jonce

Note: if a user is granted permission with ADMIN OPTION, then even if he is revoked, any user who has granted permission will retain the right.

The revocation of system permissions is not cascaded (unlike revoking object permissions).

II. Object permissions

Object permissions allow SELECT,INSERT,UPDATE and DELETE commands to be executed against table-related objects, as well as PL/SQL objects.

Note: ANY permissions are granted for each object in each user account in the database, and are system permissions, not object permissions.

1. The syntax is as follows:

GRANT privilege ON [schema.] object TO username [WITH GRANT OPION]

EG. Grant select on store.customers to scott

Changes include ALL, which applies all relevant permissions to object types, and specific columns of named views or tables

SQL > grant select on store.orders to scott

SQL > grant update (order_status) on store.orders to scott

SQL > grant all on store.regions to scott

Using WITH GRANT OPTION, you can enable users to transfer their object permissions to third parties.

III. Revocation of authority

Revoke object permission syntax:

REVOKE privilege [, privilege...] On tablename from username

Syntax for revoking system permissions:

REVOKE privilege [, privilege...] From username

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.