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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12472709/viewspace-1036315/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12472709/viewspace-1036315/