Oracle資料庫使用者許可權控制 - Role - Synonym

為了控制使用者對Production資料庫的更改，現在建立一個新使用者Production_query和一個新角色V_TD_USR_QUERY_PRD_ROLE,該角色只對Production的table及view有查詢許可權，並且將該角色賦予給Production_query。 為了Production_query使用者查詢時方便，不用再table及view前加schema名字，可以再Production_query使用者下建立Synonyms.

步驟如下：

執行1_CREATE_QUERY_PRD_ROLE.cmd,呼叫V_TD_USR_QUERY_PRD_ROLE.sql，執行2_CREATE_SYNONYM.cmd，建立Synonym.

[@more@]

1_CREATE_QUERY_PRD_ROLE.cmd

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

sqlplus sys/password@dbtest as sysdba @./V_TD_USR_QUERY_PRD_ROLE.sql >1_CREATE_QUERY_PRD_ROLE.log

@echo off

echo.

echo Grant select on all tables and views of production schema to PRODUCTION_QUERY!

Pause

V_TD_USR_QUERY_PRD_ROLE.sql

SET PAGESIZE 0

SET LINESIZE 160

SET DEFINE OFF;

-- Create a new user

CREATE USER PRODUCTION_QUERY

IDENTIFIED BY PRODUCTION_QUERY

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

-- Create new role

CREATE ROLE V_TD_USR_QUERY_PRD_ROLE NOT IDENTIFIED;

-- Grant select on all tables and views of production schema to PRODUCTION_QUERY

SPOOL grant_select_all_tables_views.sql

SELECT 'GRANT SELECT ON PRODUCTION.'||table_name||' TO V_TD_USR_QUERY_PRD_ROLE;'

FROM dba_tables

WHERE owner='PRODUCTION';

SELECT 'GRANT SELECT ON PRODUCTION.'||view_name||' TO V_TD_USR_QUERY_PRD_ROLE;'

FROM dba_views

WHERE owner='PRODUCTION';

SPOOL OFF

@grant_select_all_tables_views.sql

GRANT V_TD_USR_QUERY_PRD_ROLE TO PRODUCTION_QUERY;

ALTER USER PRODUCTION_QUERY DEFAULT ROLE ALL;

GRANT CREATE SESSION TO PRODUCTION_QUERY;

-- Edit SQL : Create synonym for all tables and views of production schema

grant create synonym to production_query;

SPOOL create_synonym_for_tables_views.sql

SELECT 'CREATE SYNONYM '||view_name||' FOR PRODUCTION.'||view_name||';'

FROM dba_views

WHERE owner='PRODUCTION';

SELECT 'CREATE SYNONYM '||table_name||' FOR PRODUCTION.'||table_name||';'

FROM dba_tables

WHERE owner='PRODUCTION';

SPOOL OFF

SET PAGESIZE 14

SET LINESIZE 80

quit

2_CREATE_SYNONYM.cmd

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

sqlplus production_query/production_query@dbtest @./create_synonym_for_tables_views.sql > create_synonym_for_tables_views.log

@echo off

echo.

echo Create synonym for all tables and views of production schema!

pause