Oracle explicit authorization and implicit authorization

Source: Internet
Author: User
Tags dba

Authorization for Oracle Objects

Oracle Licensing

I. Authorization syntax

Grant Syntax:

1. Explicit authorization (authorization of objects directly to the user)

GRANT privilege [, ...]  On object [, ...] to {public| Group | Username|role} [with GRANT OPTION]

Example Grant Read,write on directory dir_home to public

2. Implicit authorization (by delegating the role to the user)

GRANT role to {public| Group | Username|role}

Example Grant Connect,resource to Oltp_user

Syntax Description:

Privilege (permissions)

The possible permissions are:

select--Access all columns/fields of the declared table/view.

insert-inserts all columns/fields on the declared table.

update-updates all columns/fields on the declared table.

delete--deletes all columns/fields to the declared table.

Rule--Defines rules on tables/views (see the Create RULESET statement).

All--Give all permissions.

object to which the permission is given.

The possible objects are:

Table (Tables)

View (views)

Sequence (sequence)

Index (indexed)

Directory (directory)

The public representative is a shorthand for all users.

Group of groups to be assigned permissions. In the current version, the group must be explicitly created with the following method.

Username the name of the user who will be given permission. Public is shorthand for all users.

Role in a persona (e.g., DBA Connect Resource)

With GRANT option allows the same permissions to be granted to others, and authorized users can continue to authorize them.

Describe

After an object is created, other people do not have permission to access the object except the creator, unless the creator gives (grant) permissions.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Grant allows the creator of an object to give some specific permission to a user or to a group or to all users (public). The creator is not required to give permissions to the (Grant) object, and the creator defaults to owning all the permissions of the object, including the right to delete it.

Description

Oracle does not allow objects that are not explicitly authorized to be used in stored procedures. To use another user's object, you must display authorization to yourself through another user.

Because Oracle does not check the roles owned by a defined person while compiling a stored procedure, only checks for permissions that are explicitly granted, and DBA is a role, even DBAs need explicit authorization.

Second, the way of authorization (explicit and implicit)

There are two modes of object authorization, explicit and implicit:

The difference between showing authorization and implicit authorization is that explicit authorization is the direct authorization of an object to the user, and that implicit authorization is the way in which a user is granted a role to achieve authorization.

1. Explicit authorization is authorized directly with the GRANT statement.

Syntax: Grant some permission to user

Such as:

CONN User1/password

GRANT SELECT on TABLE1 to USER2; Displays the SELECT permission for the User1 table TABLE1 to User2

GRANT UPDATE on TABLE1 to USER2; Displays the update permission for the User1 table TABLE1 to User2

Note: The System/manager login is not authorized, so that USER2 users can access the USER1 user's table in the stored procedure, you must log in with the USER1 user (the user has DBA authority), and then authorize it.

Sql>grant Select on USER1. Ma_userinfo to USER2

2. Implicit authorization is authorized by role.

Syntax: Grant a role to user

Such as:

CONN USER1

GRANT SELECT on TABLE1 to ROLE1; Displays the SELECT permission for the USER1 table TABLE1 to Role1

CONN SYSTEM switch to Administrator grant role

GRANT ROLE1 to USER2; Give the USER2 permission to grant Role1.

Iii. Withdrawal of authority

Grammar:

Explicit CLAIM: revoke permission from user; Reclaim permissions from the user

Implicit right of access: revoke role from user; To retract a role from a user

Example:

Revoke privilege1 from USER2; Reclaim permissions from the user

Revoke select on Table1 from User1; Recover permissions for a query select table

Revokeall on table1 from User1; Reclaim all permissions for table table1 from user user1

Revoke ROLE1 from USER2; To retract a role from a user

Grant connect to Xujin; Grant connect role to user Xujin

Revoke connect from Xujin back connect role from user Xujin

End

Author:51cto Blog Oracle Little Bastard

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.