Oracle select any dictionary & oracle authorization details

When you connect to a 10 Gb database using the oracle 9I em console client and use the normal connection identity, a message indicating a lack of select any dictionary permissions is displayed. However, you can connect to the cluster by using the sysdba identity or the normal identity of PL/SQL ~

This is a bug in oracle9I, and oracle is not going to solve this problem.

In fact, this problem may occur when you link from a lower version to a higher version.

 

 

# Create a user

Create user angeos identified by angeos;

Set the user name to angeos and the password to angeos.

# User authorization

Grant connect, resource to angeos;

The connection is granted to the angeos user.DatabaseAnd Resource Access Permissions

# User authorization

Grant create session, dba to angeos;

Create session is a system privilege that provides users with the ability to connect to databases.

DBA is a role with over 120 SYSTEM privileges, So it allows users to complete almost anyWork.

# Changing a user's password

Alter user angeos identified by oracle; change the password of user angeos to: oracle.

# Deleting a user

Angeos

Drop user angeos;

Because angeos has a table table1, You need to specify the keyword CASCADE When deleting a user.

Drop user angeos cascade;

# Create table as select statement

You can create a table by querying a table and materialized the query result set into a regular table.
Copy the table structure, but the constraints, indexes, triggers, and other objects are not put into the new table.

Example:

Create table emp_copy

Select * from scott. emp;

 

Alter any cluster: modify the permissions of any cluster
Alter any index
Alter any role Modify permissions of any role
Alter any sequence
Alter any snapshot
Alter any table
Alter any trigger
Alter cluster Modify permissions on clusters
Alter database: modify database Permissions
Alter procedure: modify stored procedure Permissions
Alter profile: Permission to modify the resource limit table
Alter resource cost
Alter rollback segment: Modify rollback segment Permissions
Alter sequence
Alter session modify Database session Permissions
Alter sytem: modify the permissions set by the database server
Alter table Modify table Permissions
Alter tablespace: Permission to modify tablespaces
Alter user: Modify user permissions
Analyze uses the analyze command to analyze any tables, indexes, and clusters in the database.
Audit any sets audit options for any database objects
Audit system allows system Operation audit
Backup any table
Become user permission for switching user status
Commit any table: table submission permission
Create any cluster: Permission to create a cluster for any user
Create any index is used to create an index for any user.
Create any procedure: Permission to create a stored procedure for any user
Create any sequence permission to create a sequence for any user
Create any snapshot: Permission to create snapshots for any user
Create any synonym permission to create a synonym for any user
Create any table: Permission to create a table for any user
Create any trigger: Permission to create a trigger for any user
Create any view: Permission to create a view for any user
Create cluster: Permission to create clusters for users
Create database link permissions created for users
Create procedure permission to create a stored procedure for users
Create profile: create resource limit table Permissions
Create public database link: Permission to create a public database link
Create public synonym
Create role: Permission to create a role
Create rollback segment permission for creating rollback segments
Create session
Create sequence: Permission to create sequences for users
Create snapshot: Permission to create snapshots for users
Create synonym: Permission to create a synonym for a user
Create table: Permission to create tables for users
Create tablespace permission for creating a tablespace
Create user: create user Permissions
Create view permission for creating a view
Delete any table: Permission to delete any table row
Delete any view permission to delete any view row
Delete snapshot: delete the row permission of the snapshot.
The delete table permission is used to delete table rows.
Delete view is the permission to delete view rows.
Drop any cluster permission to delete any cluster
Drop any index permission to delete any index
Drop any procedure permission to delete any stored procedure
Drop any role
Drop any sequence: Permission to delete any sequence
Drop any snapshot permission to delete any snapshot
Drop any synonym permission to delete any synonymous name
Drop any table permission to delete any table
Drop any trigger permission to delete any trigger
Drop any view permission to delete any view
Drop profile: Permission to delete a Resource Restriction table
Drop public cluster permission to delete public Clusters
Drop public database link: Permission to delete a public Data link
Drop public synonym
Drop rollback segment permission to delete rollback segments
Drop tablespace permission to delete a tablespace
Drop user: delete user Permissions
Execute any procedure permission to execute any stored procedure
Execute function
Execute package: permission to run the Storage package
Execute procedure
Force any transaction manages the output permissions of uncommitted arbitrary transactions
Force transaction manages the output permissions of uncommitted user transactions
Grant any privilege grant privileges to any system
Grant any role grant permissions to any role
Index table: Permission to add an index to a table
Insert any table insert row permission to any table
Insert snapshot
Insert table permission to insert rows into a user table
Insert view permission to insert rows into user view
Lock any table to lock any table
Manager tablespace Management (Backup availability) tablespace permission
References table reference table Permissions
Restricted session permission for creating a limited database session
Select any sequence permission to use any sequence
Select any table permission
Select snapshot permission to use snapshots
Select sequence permission to use user sequence
Select table User table permission
Select view permission
Unlimited tablespace does not limit the table space size
Update any table modify the row permissions of any table
Update snapshot: Modify the row permissions of a snapshot.
Update table: Modify row permissions in user table
Update view: Modify the row permissions in the view.
Enqueue any queue is the permission to add messages to all queues;
Dequeue any queue is the permission to send messages to all queues;
Manage any queue is the permission to run DBMS_AQADM in all solutions.

1. View All Users:
Select * from dba_users;
Select * from all_users;
Select * from user_users;
2. View user or role system permissions:
Select * from dba_sys_privs;
Select * from user_sys_privs;
3. View user object permissions:
Select * from dba_tab_privs;
Select * from all_tab_privs;
Select * from user_tab_privs;
4. view all roles:
Select * from dba_roles;
5. view the roles owned by a user or role:
Select * from dba_role_privs;
Select * from user_role_privs;

6. Note:

(1) The following statements can be used to view the system permissions provided by Oracle:

Select name from sys. system_privilege_map

(2) view all system permissions of a user (including the system permissions of the role)

Select privilege from dba_sys_privs where grantee = 'user _ name'
Union
Select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee = 'user _ name ');

 

1. Permission is the user's power to execute a function. In Oracle, permissions are divided into system permissions and entity permissions based on different system management methods. System permission refers to whether authorized users can connect to the database and perform system operations in the database. The entity permission refers to the user's permissions on the specific schema object. This may be a bit fuzzy. For example, the select any table is a system permission, which indicates that any table can be viewed. Select on table1 is the object permission, indicating the query permission for table 1.

2. See the table above.

Iii. Use of system permission authorization commands
Syntax:
GRANT permission name TO user | role | PUBLIC
Among them, PUBLIC indicates to grant permissions to all users in the database
For example, run the following command to grant the create table permission to USER1:
SQL> GRANT CREATE TABLE TO USER1;
The with admin option can also be added to the authorization statement, indicating that the authorized user can grant the permission to other users, for example:
SQL> GRANT CREATE TABLE, CREATE VIEW TO USER1, USER2 WITH ADMIN OPTION;
To understand the system permissions of users, You can query the data dictionaries USER_SYS_PRIVS and ROLE_SYS_PRIVS.
To REVOKE permissions, use the REVOKE command, for example:
SQL> revoke CREATE TABLE from user1;

Iv. Object permission management
Object permission refers to the operation permissions of a user on a specific schema object.
1. Object permission Classification
Different object types have different object permissions, as shown in the following table.

2. commands for granting object permissions

Syntax:
Grant object permission name | all to user | role | public
All indicates all object permissions.
For example:
SQL> grant select on books_quthors to user1;
The following statement is used to query the table's object permission authorization information:
SQL> select * From user_tab_prives
To revoke object permissions, use revoke. The syntax is as follows:
Revoke object permission name | all on Object Name from user name | role name | public.