程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 動態DDL語句執行和Schema Objects的顯示授權問題

動態DDL語句執行和Schema Objects的顯示授權問題

編輯:關於SqlServer

這是一個大家常見的問題,也是很容易忽略的問題,尤其是在Procedure裡面動態執行DDL語句的時候發生. 我們必須使用顯示授權方式來保證DDL的執行權限, 而不能通過role的傳遞方式,下面是一個簡單的測試過程來驗證這一點.

參考說明:

If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior. Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notifIEd only that the item was not declared

 

Test steps:

SQL> create user chris identifIEd by pass;

 

User created.

 

SQL> grant dba to chris;

 

Grant succeeded.

 

SQL> connect chris/pass;

Connected.

SQL> create table test1 (name varchar2(10));

Table created.

 

--Success here.

SQL> drop table test1;

 

Table dropped.

 

SQL> create or replace procedure chris_cr_tbl

  2  IS

  3  begin

  4  execute immediate 'create table test1 (name varchar2(10))';

  5  end;

  6  /

 

Procedure created.

 

SQL> exec chris_cr_tbl;

BEGIN chris_cr_tbl; END;

 

*

ERROR at line 1:

ORA-01031: insufficIEnt privileges

ORA-06512: at "CHRIS.CHRIS_CR_TBL", line 4

ORA-06512: at line 1

 

--Failed Here

SQL> connect test/Oracle@ta_confdb

Connected.

SQL> grant create table to chris;

 

Grant succeeded.

--Explicit Grant

SQL> connect chris/pass@ta_confdb

Connected.

SQL> exec chris_cr_tbl;

 

PL/SQL procedure successfully completed.SQL> select table_name from all_tables where owner='CHRIS';

 

TABLE_NAME

------------------------------

TEST1

 

SQL> drop table test1;

 

Table dropped.


  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved