ORACLE: OCA-047-question and experiment (6)-session_privs content

Question:

Answer:

SQL> select * from session_privs; PRIVILEGE----------------------------------------ALTER SYSTEMAUDIT SYSTEMCREATE SESSIONALTER SESSIONRESTRICTED SESSIONCREATE TABLESPACEALTER TABLESPACEMANAGE TABLESPACEDROP TABLESPACEUNLIMITED TABLESPACECREATE USERBECOME USER... ...

The official documents describe session_privs as follows:

SESSION_PRIVSDescribes the privileges that are currently available to the user.

It does not mean system or object permissions. The above query results show that these are system permissions, not object-level permissions. What are object-level permissions?

 

Complete permission descriptions are as follows (taken from the Internet: http://hi.baidu.com/zhaojing_boy/blog/item/0ffe95091266d939e824885f.html ):

1. Permission is the user's power to execute a function. In Oracle, permissions are divided into system permissions and object 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 object permission refers to the user's permissions on the specific schema object (schema. 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. That is, the object permission is assigned to an object, such as tables, indexes, sequences, and so on;

Ii. Use of system permission authorization commands
Syntax:
Grant permission name to user | role | public
Among them, "public" means 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;

Iii. Object permission management
Object permission refers to the operation permissions of a user on a specific schema object.
Object permission granting command
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.

View user permissions in Oracle

In Oracle, data dictionary Views are divided into three categories, with different prefixes: User, all, and DBA. Many data dictionary views contain similar information.

User _ *: Information about objects owned by users, that is, information about objects created by users

All _ *: Information about objects that can be accessed by users, that is, information about objects created by users plus objects created by other users, which can be accessed by users.

DBA _ *: Information about objects in the entire database

(Here, * Can be tables, indexes, objects, and users.

1. View All Users:
Select * From dba_user;
Select * From all_users;
Select * From user_users;
2. view the user's system permissions:
Select * From dba_sys_privs;
Select * From all_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 the user:
Select * From dba_role_privs;
Select * From user_role_privs;

6. view the default tablespace of the current user
Select username, default_tablespace from user_users;

7. view the specific permissions of a role, such as grant connect, resource, create session, and create view to test;

View the permissions of a resource, and use select * From dba_sys_privs where grantee = 'resource ';