数据库表单权限开放

/查询未公开的表名/
INSERT INTO LW_SYNONYM_LOG
SELECT SYNONYM_SEQ.NEXTVAL ID,
‘synonym_table’ type,‘create public synonym ‘|| table_name ||’ for ‘||owner||’.’||table_name||‘;’ create_sql,sysdate create_dte,0 status
from dba_tables where owner in(‘LWPRO’,‘PM_USER’)
and table_name not in (select t.TABLE_NAME from dba_synonyms t where t.TABLE_OWNER in(‘LWPRO’,‘PM_USER’,‘MS2105258004’) and owner=‘PUBLIC’);

/查询未公开的视图名/
INSERT INTO LW_SYNONYM_LOG
SELECT SYNONYM_SEQ.NEXTVAL ID,
‘synonym_table’ type,‘create public synonym ‘|| view_name ||’ for ‘||owner||’.’||view_name||‘;’ create_sql,sysdate create_dte,0 status
from all_views where owner in(‘LWPRO’,‘PM_USER’)
and view_name not in (select t.TABLE_NAME from dba_synonyms t where t.TABLE_OWNER in(‘LWPRO’,‘PM_USER’,‘MS2105258004’) and owner=‘PUBLIC’);

/查询未授权的数据表/
INSERT INTO LW_SYNONYM_LOG
SELECT SYNONYM_SEQ.NEXTVAL ID,
‘synonym_table’ type,‘grant select on LWPRO.’|| table_name ||’ to MES_READ;’ create_sql,sysdate create_dte,0 status
from dba_tables where owner IN(‘LWPRO’,‘PM_USER’,‘MS2105258004’)
and table_name not in(SELECT table_name from dba_tab_privs where grantee IN(‘MES_READ’));

/查询未授权的视图/
INSERT INTO LW_SYNONYM_LOG
SELECT SYNONYM_SEQ.NEXTVAL ID,
‘synonym_table’ type,‘grant select on LWPRO.’|| view_name ||’ to MES_READ;’ create_sql,sysdate create_dte,0 status
from all_views where owner IN(‘LWPRO’,‘PM_USER’,‘MS2105258004’)
and view_name not in(SELECT table_name from dba_tab_privs where grantee IN(‘MES_READ’));

/查询并创建函数、存储过程同义词/
INSERT INTO LW_SYNONYM_LOG
SELECT SYNONYM_SEQ.NEXTVAL ID,
‘synonym_table’ type,‘create public synonym ‘|| OBJECT_NAME ||’ for ‘||owner||’.’||OBJECT_NAME||‘;’ create_sql,sysdate create_dte,0 status
FROM SYS.ALL_OBJECTS O WHERE O.OBJECT_TYPE IN(‘PROCEDURE’,‘FUNCTION’) AND O.OWNER = ‘LWPRO’
AND O.OBJECT_NAME NOT IN (SELECT T.TABLE_NAME from DBA_SYNONYMS T WHERE T.TABLE_OWNER IN(‘LWPRO’,‘PM_USER’,‘MS2105258004’) AND OWNER=‘PUBLIC’);

/查询并授权函数、存储过程给MES_READ/
INSERT INTO LW_SYNONYM_LOG
SELECT SYNONYM_SEQ.NEXTVAL ID,
‘synonym_table’ type,‘grant execute,debug on LWPRO.’|| OBJECT_NAME ||’ to MES_READ;'create_sql,sysdate create_dte,0 status
FROM SYS.ALL_OBJECTS O WHERE O.OBJECT_TYPE IN(‘PROCEDURE’,‘FUNCTION’) AND O.OWNER = ‘LWPRO’
AND O.OBJECT_NAME NOT IN (SELECT table_name from dba_tab_privs where grantee IN(‘MES_READ’));

/查询所有用户视图/
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE=‘DATABASE LINK’;

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE=‘TABLE’ AND OWNER=‘LWPRO’;

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE=‘VIEW’ AND OWNER=‘LWPRO’;

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE=‘TRIGGER’ AND OWNER=‘LWPRO’;

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE=‘FUNCTION’ AND OWNER=‘LWPRO’;-- JOB TRIGGER PROCEDURE VIEW

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE=‘PROCEDURE’ AND OWNER=‘LWPRO’;-- JOB PROCEDURE

select * from dba_tab_privs where grantee=‘user_name’–查看该用户下所有视图信息

select * from dba_dependencies where referenced_name=‘table_name’–查看该表创建了哪些视图

select * from dba_tab_privs where owner=‘Username’ and table_name=‘view_NAME’–查看该表或者视图授予给哪些用户