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

Oracle permission

Permissions allow users to access objects or execution programs that belong to other users. The ORACLE system provides three permissions: Object object level, System system level, and Role role level. These permissions can be granted to users, special users public, or roles, and if a permission is granted to a special user "Public" (the user public is predefined by oracle, and each user has the permissions that that user has), it means that the permission is granted to all users of the database.

For administrative rights, a role is a tool, permissions can be granted to one role, and roles can be granted to another role or user. Users can inherit permissions through roles, and the role service has no purpose other than administrative permissions. Permissions can be granted or revoked in the same way.

Permission classification

1. System permissions: the system stipulates the user's permission to use the database. (system permissions are for users).

2. Entity permissions: the access rights of certain users to other users' tables or views. (for tables or views).

System rights management

System permission classification:

DBA: has all privileges, is the highest privilege of the system, and only DBA can create the database structure.

RESOURCE: users with Resource permissions can only create entities, not database structures.

CONNECT: users with Connect permission can only log in to Oracle, cannot create entities, and cannot create database structures.

For ordinary users: grant connect, resource permission.

For DBA administrative users: grant connect,resource, dba permissions.

System permission authorization command:

System permissions can only be granted by DBA users: sys, system (initially only these two users)

Authorization command: SQL > grant connect, resource, dba to username 1 [, username 2].

Note: ordinary users can have the same user rights as system through authorization, but can never achieve the same permissions as sys users, and the rights of system users can also be revoked.

Example:

SQL > connect system/manager

SQL > Create user user50 identified by user50

SQL > grant connect, resource to user50

Query which permissions the user has:

SQL > select * from dba_role_privs

SQL > select * from dba_sys_privs

SQL > select * from role_sys_privs

Find out what system permissions you have

SQL > select * from session_privs

Delete user

SQL > drop user username cascade; / / plus cascade deletes the user along with everything he or she has created

System permission transfer:

Add the WITH ADMIN OPTION option, and the resulting permissions can be passed.

SQL > grant connect, resorce to user50 with admin option; / / can pass the permissions.

System permission recovery: system permissions can only be reclaimed by DBA users

SQL > Revoke connect, resource from user50

Description:

1) if you use WITH ADMIN OPTION to grant system permissions to a user, canceling that user's system permissions does not cascade revoke the same permissions for all users who are granted the same permissions by that user.

2) there is no cascade of system permissions, that is, A grants B permissions and B grants C permissions. If A withdraws the permissions of B, the permissions of C will not be affected. System permissions can be reclaimed across users, that is, A can directly withdraw the permissions of C users.

Entity rights management

Entity permission classification

Select, update, insert, alter, index, delete, all / / all includes all permissions

Execute / / permissions to execute stored procedure

User01:

SQL > grant select, update, insert on product to user02

SQL > grant all on product to user02

User02:

SQL > select * from user01.product

/ / user 02 looks up user _ tables at this time, excluding the table user01.product, but if you check all_tables, you can find it because he can access it.

Grant operation rights to the table to all users:

SQL > grant all on product to public; / / public means all users, and the all permission here does not include drop.

Entity permission data dictionary

SQL > select owner, table_name from all_tables; / / tables that users can query

SQL > select table_name from user_tables; / / user-created table

SQL > select grantor, table_schema, table_name, privilege from all_tab_privs; / / authorized access tables (authorized)

SQL > select grantee, owner, table_name, privilege from user_tab_privs; / / tables of granted permissions (granted permissions)

DBA users can manipulate any base table of all users (no authorization is required, including deletion):

DBA users:

SQL > Create table stud02.product (

Id number (10)

Name varchar2 (20))

SQL > drop table stud02.emp

SQL > create table stud02.employee

As

Select * from scott.emp

Entity permission transfer (with grant option):

User01:

SQL > grant select, update on product to user02 with grant option; / / user02 gets the permission and can be passed.

Entity permission recovery:

User01:

SQL > Revoke select, all permissions passed by update on product from user02; / / will be lost.

Description

1) if you revoke the object permissions of a user, the same permissions will also be revoked for those users who use WITH GRANT OPTION to grant permissions, that is, they will be cascaded when the authorization is revoked.

Oracle role Management

A role is a collection of permissions, and when a role is assigned to a user, that user has all the permissions in that role. System predefined roles are commonly used roles that the system automatically creates after the database is installed. The following is a brief introduction to these scheduled roles. The permissions contained in the role can be queried with the following statement:

Sql > select * from role_sys_privs where role=' role name'

CONNECT, RESOURCE, DBA: these predefined roles are mainly for backward compatibility. It is mainly used for database management. Oracle recommends that users design their own database administration and secure permission plans, rather than simply using these scheduled roles. These roles may not be predefined roles in future releases.

DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE: these roles are mainly used to access data dictionary views and packages.

EXP_FULL_DATABASE, IMP_FULL_DATABASE: these two roles are used for the use of data import and export tools.

AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE:AQ:Advanced Query . These two roles are used for oracle's advanced query capabilities.

SNMPAGENT: for oracle enterprise manager and Intelligent Agent

RECOVERY_CATALOG_OWNER: used to create users with recovery libraries. For information about the recovery library, refer to the oracle document "Oracle9i User-Managed Backup and Recovery Guide"

HS_ADMIN_ROLE:A DBA using Oracle's heterogeneous services feature needs this role to access appropriate tables in the data dictionary.

Administrative role

Build a character.

Sql > create role role1

Delegate to the role

Sql > grant create any table,create procedure to role1

Grant roles to users

Sql > grant role1 to user1

View the permissions contained in the role

Sql > select * from role_sys_privs

Create a role with a password (you must provide a password when a role with a password is in effect)

Sql > create role role1 identified by password1

Modify roles: whether a password is required

Sql > alter role role1 not identified

Sql > alter role role1 identified by password1

Set the role that the current user wants to take effect

Note: what is the concept of the effect of a role? Suppose that user a has three roles, b1 and b2, b3, then if b1 does not take effect, the permissions contained in b1 do not have for a, only when the role takes effect, the permissions in the role act on the user, and the maximum number of valid roles is set by the parameter MAX_ENABLED_ROLES; after the user logs in, oracle assigns all the permissions directly assigned to the user and the permissions in the user's default role to the user. )

Sql > set role role1; / / make role1 effective

Sql > set role role,role2; / / make role1,role2 effective

Sql > set role role1 identified by password1; / / use role1 with password to take effect

Sql > set role all; / / all roles that use this user take effect

Sql > set role none; / / set all roles to fail

Sql > set role all except role1; / / all other roles for this user except role1 take effect.

Sql > select * from SESSION_ROLES; / / View the effective roles of the current user.

Modify the specified user to set its default role

Sql > alter user user1 default role role1

Sql > alter user user1 default role all except role1

See the oracle reference documentation for details

9. Delete a role

Sql > drop role role1

After the role is deleted, the user who originally used the role will no longer have the role, and the corresponding permissions will be lost.

Description:

1) you cannot use WITH GRANT OPTION to grant object permissions to a role

2) you can use WITH ADMIN OPTION to grant system permissions to roles, but not cascading when canceled

The data dictionary tables related to permission security are:

ALL_TAB_PRIVS

ALL_TAB_PRIVS_MADE

ALL_TAB_PRIVS_RECD

DBA_SYS_PRIVS

DBA_ROLES

DBA_ROLE_PRIVS

ROLE_ROLE_PRIVS

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS

SESSION_PRIVS

SESSION_ROLES

USER_SYS_PRIVS

USER_TAB_PRIV

Oracle system and object permission list

Alter any cluster modifies the permissions of any cluster

Permission of alter any index to modify any index

Alter any role modifies the permissions of any role

Permission of alter any sequence to modify any sequence

Alter any snapshot modifies the permissions of any snapshot

Permission of alter any table to modify any table

Alter any trigger modifies the permissions of any trigger

Alter cluster modifies permissions for owning clusters

Permissions for alter database to modify the database

Alter procedure modifies stored procedure permissions

Permission of alter profile to modify resource restriction profile

Alter resource cost sets permissions for resource overhead of the story

Permission for alter rollback segment to modify rollback segment

Alter sequence modifies sequence permissions

Permissions for alter session to modify a database session

Permissions for alter sytem to modify database server settings

Alter table modifies the permissions of the table

Alter tablespace permissions to modify tablespaces

Alter user modifies the user's permissions

Analyze uses the analyze command to analyze arbitrary tables, indexes, and clusters in the database

Audit any sets audit options for arbitrary database objects

Audit system allows system operation audit

Permission for backup any table to back up any table

Permissions for become user to switch user statu

Permission for commit any table to submit table

Create any cluster permission to create clusters for any user

Create any index's right to create an index for any user

Permission for create any procedure to create stored procedures for any user

Create any sequence's right to create a sequence for any user

Permission for create any snapshot to create snapshots for any user

Create any synonym's right to create synonyms for any user

Permission for create any table to create tables for any user

Create any trigger's right to create triggers for any user

Permission for create any view to create views for any user

Create cluster permission to create clusters for users

Permissions created by create database link for the user

Create procedure permission to create stored procedures for users

Permission for create profile to create resource restriction profile

Permission for create public database link to create a public database link

Permission for create public synonym to create public synonyms

Permissions for create role to create roles

Permission for create rollback segment to create rollback segment

Permission for create session to create session

Create sequence permission to create sequences for users

Permission for create snapshot to create snapshots for users

Create synonym permission to create synonyms for users

Permission for create table to create tables for users

Permissions for create tablespace to create tablespaces

Permissions for create user to create a user

Create view permission to create views for users

Delete any table permission to delete any table row

Delete any view permission to delete any view row

Delete snapshot permission to delete rows in a snapshot

Delete table gives users permission to delete table rows

Delete view gives users permission to delete view rows

Permission for drop any cluster to delete any cluster

Drop any index's permission to delete any index

Drop any procedure removes permissions for any stored procedure

Drop any role deletes permissions for any role

Permission for drop any sequence to delete any sequence

Permission for drop any snapshot to delete any snapshot

Permission for drop any synonym to delete any synonymous name

Drop any table permission to delete any table

Drop any trigger removes the permission of any trigger

Permission for drop any view to delete any view

Permission for drop profile to delete resource restriction profile

Permission for drop public cluster to delete public clusters

Permission for drop public database link to delete public data link

Drop public synonym permission to delete public synonyms

Permission for drop rollback segment to delete rollback segment

Drop tablespace permissions to delete tablespaces

Drop user removes the user's permissions

Permission for execute any procedure to execute arbitrary stored procedures

Permissions for execute function to execute stored functions

Permission for execute package to execute storage package

Permissions for execute procedure to execute user stored procedures

Force any transaction manages output permissions for any uncommitted transaction

Force transaction manages output permissions for uncommitted user transactions

Privileges granted by grant any privilege to any system privilege

Permissions granted to any role by grant any role

Permission for index table to index a table

Insert any table's permission to insert rows into any table

Permission for insert snapshot to insert rows into a snapshot

Insert table's permission to insert rows into the user table

Insert view's permission to insert lines into the user's view

Lock any table has the right to lock any table

Permissions for manager tablespace management (backup availability) tablespaces

Permissions for the references table reference table

Permission for restricted session to create restricted database sessions

Select any sequence uses the permissions of any sequence

Permission for select any table to use any table

Select snapshot uses the permissions of the snapshot

Select sequence uses user sequence permissions

Select table uses permissions from the user table

Select view uses permissions for the view

Unlimited tablespace unrestricted permissions on tablespace size

Update any table permissions to modify rows in any table

Update snapshot modifies the permissions of rows in a snapshot

Permission for update table to modify rows in the user table

Update view modifies the permissions of rows in the view

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.