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

1. View all users:

Select * from dba_users

Select * from all_users

Select * from user_users

two。 View user or role system permissions (system permissions assigned directly to a user or role):

Select * from dba_sys_privs

Select * from user_sys_privs; (view the permissions of the current user)

3. View the permissions contained in roles (you can only view roles owned by logged-in users)

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. Check which users have sysdba or sysoper system permissions (appropriate permissions are required for query)

Select * from V$PWFILE_USERS

View the permissions of a user in 8.SqlPlus

SQL > select * from dba_sys_privs where grantee='username'

The username, that is, the user name, must be capitalized.

For example:

SQL > select * from dba_sys_privs where grantee='TOM'

9. Oracle method to delete all tables of a specified user

Select 'Drop table' | | table_name | |'; 'from all_tables

User name to be deleted by where owner=' (uppercase)'

10. Delete a user

Drop user user_name cascade

Such as: drop user SMCHANNEL CASCADE

11. Get all the tables under the current user: select table_name from user_tables

12. Delete all table data under 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 the table user_constraints. Where constraint_type='R' represents a foreign key constraint.

The command to enable foreign key constraints is: alter table table_name enable constraint constraint_name

The command to disable foreign key constraints is: alter table table_name disable constraint constraint_name

Then use SQL to find out the constraint names of all foreign keys in 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 scripts

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

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.