Oracle Object Permissions

Object permissions refer to the right to access other schemes, and the user directly accesses their own schema objects, but if you want to access objects of other scenarios, you must have permission to the object.

For example, if a Smith user wants to access the Scott.emp table (Scott: scheme, EMP: table), you do not need to scott.emp permissions on the table to have objects.

Common Object permissions:

Alter--Modify (modify table structure) Delete--delete

Select--Query Insert--add

Update--Modify (update data) index--Index

References--Invoke execute--execute

Show object Permissions

The Data dictionary view allows you to display the object permissions that a user or role has: dba_tab_privs;

Grant Object permissions

Before Oracle9i, the grant of object permissions is done by the owner of the object and, if manipulated by another user, requires the user to have the corresponding (with GRANT OPTION) permission, starting with Oracle9i, the DBA user (Sys,system) You can grant object permissions on any object to other users, and the grant object permission is done with the grant command.

Object permissions can be granted to users, roles, and public. When granting permissions, you can delegate this permission to other users with the WITH GRANT option, but be aware that the WITH GRANT option cannot be granted a role.

Small case

1.monkey user to manipulate the Scott.emp table, you must grant the appropriate object permissions

A) Hope monkey can query the table data of scott.emp, how to operate?

First build a monkey user

sql> create user monkey identified by m123;

User created

Authorization to Monkey Users

Sql> Grant create session to Monkey;

Grant succeeded

You can view monkey users have permission to connect to the database

Sql> Conn monkey/m123;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as Monkey

Use Monkey user to view the EMP Table of Scott scheme and get a hint of failure

Sql> select * from Scott.emp;

SELECT * FROM Scott.emp

ORA-00942: Table or view does not exist

Connect to Scott users and let Scott users authorize monkey users

Sql> Conn Scott/tiger;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as Scott

Sql> Grant Select on EMP to Monkey;

Grant succeeded

Here are the results we want:

Sql> select * from Scott.emp;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

8881 test user MANAGER 7782 2010-12-21 23.00 23.00 10

......

Rows selected

b) Hope monkey can modify the data of the Scott.emp table, how to operate?

sql> Grant Update on EMP to monkey;

c) Want monkey can delete the data of scott.emp table, how to operate?

sql> Grant Delete on the EMP to monkey;

d) Grant all permissions to the data operations of the Scott.emp table at once monkey

Sql> Grant All on the MEP to monkey;

2. Ability to more granular control over monkey Access (grant column permissions)

A) Hope monkey can only modify the Sal field of the Scott.emp table, how to do it?

Grant Update on EMP (SAL) to monkey;

b) Hope monkey can only query the ename,sal data of scott.emp table, how to operate?

Grant Select on EMP (ename,sal) to monkey;

3. Grant ALTER permission

If the monkey user wants to modify the structure of the SCOTT.EMP table, the Alter object permission must be granted

Sql> Conn Scott/tiger;

Sql> Grant alter on EMP to monkey;

Of course, it can be done by Sys,system.

4. Grant Execute permission

If the user wants to execute packages/procedures/functions for other scenarios, they must have execute permission.

For example, to allow monkey users to execute dbms_transaction, you can grant execute permissions

Sql> Conn System/manger;

Sql> Grant execute on dbms_transaction to monkey;

5. Grant the index permission

If you want to index on a table in another scenario, you must have the Index object permission, for example, to give the monkey user the object permission to index on the child scott.emp

Sql> Conn Scott/tiger;

Sql> Grant Index on scott.emp to monkey with GRANT option

6. Using the WITH GRANT option

This option is used to delegate object permissions, but this option can only be granted to the user, not the role

Sql> Conn Scott/tiger;

Sql> Grant SELECT on EMP-Monkey with GRANT option;

Sql> Conn monkey/m123;

Sql> Grant Select on Scott.emp to anybody;

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).

Note: After object permissions are retracted, the user cannot execute the corresponding SQL command, and the object permissions can be cascade recycled.

Syntax: Revoke object permission on object from user;

1. Definition

2. What are the object permissions

How to assign permissions to objects

System permissions

System permissions: Used to control one or a set of database operations that a user can perform. For example, when a user has CREATE TABLE permissions, tables can be built in other scenarios, and tables can be built in any scenario when the user has the Create any table permission. Oracle provides more than 100 system permissions.

Commonly used are:

Create session--Connect to database

Create view--build views

CREATE table--Build tables

CREATE PROCEDURE--build processes, functions, packages

Create trigger--key trigger

Create cluster--key cluster

Create public synonym--key synonyms

Show system permissions

You can system_privilege_map by querying the data dictionary view; You can display all system permissions:

Select * from System_privilege_map order by name;

Granting system permissions

In general, the grant of system permissions is done by the DBA, and if the system permissions are granted by another user, the user must have the system permissions of the grant any privilege, and the WITH ADMIN option option on the grant. This allows the user or role that is granted system permissions to grant the system permission to the other user account role.

Sql> Create user Ken identified by m123;

User created

Sql> Grant create session,create table to Ken with admin option;

Grant succeeded

Sql> Grant CREATE view to Ken;

Grant succeeded

Sql> Conn ken/m123;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as Ken

Reclaim System permissions

In general, the recovery system permission is done by the DBA, and if other users are to reclaim system permissions, the user must have the appropriate system permissions and the option to delegate system permissions (with admin option). The Reclaim system permissions are done using the revoke command. System permissions are not cascading collections.

Sql> revoke create session from Ken;

Revoke succeeded

Shop Sales System Design case:

A database of existing stores, recording customers and their purchases, consists of the following three tables:

Commodity Goods (product No. goodsid, commodity name Goodsname, Unit price UnitPrice, category of goods, supplier provider);

Client table Customer (customer number CustomerID, name name, address address, email email, sex sex, Provincial certificate cardid);

Purchase Purchase (Customer number CustomerID, product number GOODSID, purchase quantity nums);

Complete the following functions in SQL language:

1. Create a table and declare it in the definition:

A) primary key for each table

b) The customer's name cannot be empty

c) The unit price must be greater than 0, the purchase quantity must be between 1~30

d) e-mail cannot be duplicated

e) The gender of the client must be male or female, the default is male

Goods table

Sql> CREATE TABLE Goods (Goodsid char (8) Primary key,

2 goodsname varchar2 (30),

3 UnitPrice Number (10,2) check (UnitPrice >0),

4 category VARCHAR2 (8),

5 provider Varchar2 (30));

Table created

Customer table

Sql> CREATE TABLE Customer (CustomerId char (8) Primary key,

2 name VARCHAR2 (NOT null),

3 address VARCHAR2 (50),

4 email varchar2 (unique),

5 Sex char (2) Default ' man ' Check (sex in (' Male ', ' female ')),

6 CardId Char (18));

Table created

Purchase table

Sql> CREATE TABLE Purchase (CustomerId char (8) References customer (CUSTOMERID),

2 Goodsid char (8) References goods (GOODSID),

3 nums number Check (Nums between 1 and 30));

Table created

If you forget to establish the necessary constraints when creating the table, you can use the ALTER TABLE command to add constraints to the table after the table is built. Note, however, that when you add a NOT NULL constraint, you need to use the Modify option, and add the additional four constraints using the Add option.

2. Modify the table

A) main external code for each table

b) The customer's name cannot be empty, and the added product name cannot be empty.

Sql> ALTER TABLE goods modify goodsname NOT null;

Table Altered

c) The unit price must be greater than 0, the purchase quantity must be between 1~30

d) e-mails cannot be duplicated and additional provincial certificates cannot be duplicated.

Sql> ALTER TABLE customer add constraint cardunique unique (cardId);

Table Altered

e) The gender of the client must be male or female, the default is male

f) To increase the customer's address can only be Haidian, Chaoyang, Dongcheng, Xicheng, Tongzhou, Chongwen.

Sql> ALTER TABLE customer add constraint addresscheck check (address in (' Dongcheng ', ' Xicheng ', ' Haidian ', ' Chaoyang ', ' Tongzhou ', ' Chongwen '));

Table Altered

Oracle Object Permissions