Oracle View user Information

1. View All Users:
SELECT * from Dba_users;
SELECT * from All_users;
SELECT * from User_users;
2. View user or role system permissions (System permissions that are directly assigned to a user or role):
SELECT * from Dba_sys_privs;
SELECT * from User_sys_privs; (View the permissions that the current user has)
3. View the permissions that are included in the role (can only view the roles owned by the logged-on user)
Sql>select * from Role_sys_privs;
4. View User Object permissions:
SELECT * from Dba_tab_privs;
SELECT * from All_tab_privs;
SELECT * from User_tab_privs;
5. View all roles:
SELECT * from Dba_roles;
6. View the roles owned by the user or role:
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;
7. See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying)
SELECT * FROM V$pwfile_users
View a user's permissions in 8.SqlPlus
Sql>select * from Dba_sys_privs where grantee= ' username ';
The username is the user name to capitalize.
Like what:
Sql>select * from Dba_sys_privs where grantee= ' TOM ';

9. How Oracle Deletes all tables for the specified user
Select ' Drop table ' | | table_name| | '; ' from All_tables
Where owner= ' user name to delete (note to capitalize) ';
10. Delete Users
Drop user user_name cascade;
such as: Drop user Smchannel CASCADE
11. Get all tables under current User: Select table_name from User_tables;
12. Delete all table data from a user: Select ' truncate table ' | | table_name from User_tables;
13. Prohibit foreign keys
The foreign KEY constraint names in the Oracle database can be found in table user_constraints. where constraint_type= ' R ' indicates a foreign KEY constraint.
The command to enable the foreign KEY constraint is: ALTER TABLE TABLE_NAME ENABLE constraint constraint_name
The command to disable a FOREIGN KEY constraint is: ALTER TABLE table_name DISABLE constraint constraint_name
Then use SQL to isolate the constraint name of the key outside the database:
Select ' ALTER TABLE ' | | table_name| | ' Enable constraint ' | | constraint_name| | '; ' from user_constraints where constraint_type= ' R '
Select ' ALTER TABLE ' | | table_name| | ' disable constraint ' | | constraint_name| | '; ' from user_constraints where constraint_type= ' R '
14. Oracle disables/enables foreign keys and triggers
--Enable scripting
SET serveroutput on SIZE 1000000
BEGIN
For C in (select ' ALTER TABLE ' | | table_name| | ' ENABLE CONSTRAINT ' | | constraint_name| | ' ' As V_sql from user_constraints where constraint_type= ' R ') loop
Dbms_output. Put_Line (C.v_sql);
Begin
EXECUTE IMMEDIATE C.v_sql;
Exception when others then
Dbms_output.put_line (SQLERRM);
End
End Loop;
For C in (select ' ALTER TABLE ' | | tname| | ' ENABLE all TRIGGERS ' as v_sql from tab where tabtype= ' TABLE ') loop
Dbms_output.put_line (C.v_sql);
Begin
Execute immediate c.v_sql;
Exception when others then
Dbms_output.put_line (SQLERRM);
End
End Loop;
End
/
Commit
--Disable script
SET serveroutput on SIZE 1000000
BEGIN
For C in (select ' ALTER TABLE ' | | table_name| | ' DISABLE CONSTRAINT ' | | constraint_name| | ' ' As V_sql from user_constraints where constraint_type= ' R ') loop
Dbms_output. Put_Line (C.v_sql);
Begin
EXECUTE IMMEDIATE C.v_sql;
Exception when others then
Dbms_output.put_line (SQLERRM);
End
End Loop;
For C in (select ' ALTER TABLE ' | | tname| | ' DISABLE all TRIGGERS ' as v_sql from tab where tabtype= ' TABLE ') loop
Dbms_output.put_line (C.v_sql);
Begin
Execute immediate c.v_sql;
Exception when others then
Dbms_output.put_line (SQLERRM);
End
End Loop;
End
/
Commit

Oracle View user Information