SQL statements in Oracle

　　　　

1. query field name in database: Select Column_name,data_type, Data_length,data_precision,data_scale from
User_tab_columns where table_name= ' table name '; * The table name must be uppercase.

2. Querying all user information in the database: SELECT * from Dba_users;

3. Database Current User role: SELECT * from User_role_privs;

4.pl/sql in the output switch: set serveroutput on;

5. Permissions statement in database: Grant permission to user;

Grant Create session to zhangsan;//Grant Zhangsan user permission to create session, that is, login permission

　　Grant Unlimited tablespace to zhangsan;//grants Zhangsan users permission to use tablespaces

Grant CREATE table to zhangsan;//grants permissions for creating tables

　　Grante drop table to zhangsan;//grant permission to delete tables

Grant Insert table to zhangsan;//permissions for inserting tables

　　Grant Update table to zhangsan;//permissions to modify tables

Grant all to public;//this is more important, grant all permissions (all) for all users (public)

　　Oralce more strict rights management, ordinary users are also the default can not access each other, need to authorize each other

Grant SELECT on TableName to zhangsan;//Grant Zhangsan user permission to view the specified table

　　Grant drop on TableName to zhangsan;//granting permission to delete table

Grant insert on TableName to zhangsan;//permission to be inserted

　　Grant update on TableName to zhangsan;//granting permission to modify tables

Grant Insert (ID) on tablename to Zhangsan;

　　Grant Update (ID) on TableName to zhangsan;//grants insert and Modify permissions to specific fields of the specified table, note that only the INSERT and update

Grant alert all table to zhangsan;//grants Zhangsan user alert permission to any table

Revoke permissions

Basic syntax with GRANT, keyword revoke

View Permissions

SELECT * from user_sys_privs;//View all permissions for the current user

SELECT * from user_tab_privs;//View the user's permissions on the table

SQL statements in Oracle