Permission to search for users in Oracle

We know that Oracle has a very small division of Oracle permissions. Therefore, if you do not have the required permissions, errors may occur. only the user sys generated by default after the database is created has the highest permissions and permissions. if Arwen is a user, how can we find all the permissions of the user? We know that system and object permissions are the most common. in fact, some special permissions are not classified into these two categories strictly. system Privileges: System permissions allow users to perform specific operations. this operation is not intended for a specific object. for example, common system permissions include: create table, create view, create session... to view all System permissions, we can use the following statement to find all System privileges defined in the database. SELECT * FROM SYSTEM_PRIVILEGE_MAP to view all the system permissions of the user, you can use the following statement to find all the system privileges SELECT * FROM DBA_SYS_PRIVS where grantee = 'arwen'; or SELECT * FROM USER_SYS_PRIVS; or SELECT * FROM SESSI ON_PRIVS; // the current session permission. sometimes some permissions may only apply to the current session. all the results of this statement may be more than those of the first two statements. in most cases, they are equal. we can see that there is a column named ADMIN_OPTION, and the value of this column is yes or no. if yes, this user can also grant this permission to other users. If no, it cannot. for example, grant create table to ARWEN with admin option; // admin_option is yes. ARWEN can inherit from other users to grant permissions. grant create table to weiwenhp. grant create table to ARWEN; // admin_option is no. you may ask how to remove a user who has the admin option permission. revoke create table from arwen; // you must first revoke the gran T create table to arwen; if the user has the create table permission, but wants to grant the admin option permission. you can -- revoke create table from arwen; // not a required grant create table to arwen with admin option. The object permission is related to a specific object, the permission is much more detailed than the system privileges permission. many object permissions are actually contained in a system privileges. common Object permissions include. select on table_tmp; execute on package_tmp; read on directory_tmp .......... SELECT * FROM DBA_TAB_PRIVS where grantee = 'arwen'; or SELECT * FROM US ER_TAB_PRIVS where grantee = 'arwen'; one column is grantable, which is basically the same as admin_option in system permission. if you do not explicitly specify the tablespace when creating a table, the user's default tablespace is used. however, you can also explicitly specify the tablespace when creating a table. if you do not have access to a tablespace, an error is returned. for example, create table tmp (id int) tablespace system; // ORA-01950: no privileges on tablespace 'system' we can grant the permission to alter user arwen QUOTA 1 k on system through the following statement; // you can use 1 K space in the tablespace system. alter user arwen quota unlimited on system; // you can move the table to another tablespace without limit. It is already in a tablespace. How can I move it to another tablespace? If it is only a common table, there are no columns like lob and no indexes. alter table tab_tmp move tablespace system; // move table tab_tmp to TABLE space system if the txt column is of the lob type. alter table tab_tmp move lob (txt) store as (tablespace system); if there is an index. you must first use select index_name from ind where table_name = 'tab _ TMP 'and index_type = 'normal'; to find the INDEX name, if it is ind1 alter index ind1 rebuild tablespace system; you can use the following statement to find the tablespace that you have permission to use SELECT * FROM USER_TABLESPACES; the default tablespace SELECT DEFAULT_TABLESPACE FROM USER_USERS where username java permission if you want to use resources related to java. you can use the following statement to check whether you have the relevant permissions: SELECT * FROM USER_JAVA_POLICY; or SELECT * FROM DBA_JAVA_POLICY where grantee = 'arwen'; Use the role (role) the indirect permission is obtained. If you use dba_sys_privs to query the permission, the returned result may be blank. but the user actually has a lot of permissions. this is because the user indirectly obtains many permissions through role. we must first check which roles the user has and what permissions the role has. SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS where grantee = 'arwen' to view which permissions the role has: SELECT privilege FROM ROLE_SYS_PRIVS where role = 'role1 '; but what if the role with the transfer function is neat? For example, if role one is given to role two, then role two is given to role three. in this case, three owns one and two at the same time. in this case, you must use start with to repeat all the role. the role password is well understood by specifying the user name and password when creating a login user. however, you can specify a password when creating a role. (usually seems seldom used ). we can also simply understand that we have opened a dual password for security reasons. just as if you have a safe deposit box, you need to enter another password to open it. assume that the role role1 create role1 identified by pwd is created; grant select any table to role1; grant role1 to arwen; /////// use arwen to connect to a session ///// in fact, arwen does not have the role 1 permission. you need to execute the following statement in this session: set role role1 identified by pwd; // if the password is null, set role role1 identified by null. In this case, the session requires the permission of role1. however, if you reconnect to a session, you still do not have the role 1 permission. if you want to make it easier for users to lose their passwords. you can set role1 as the default role and no longer need to enter the password each time. alter user arwen default role 'role1'; // This way, each arwen login has the permissions of ROLE1, and no password is required.