none
關於USER權限的問題 可以建立資料表,但只限制可以存取自己建立或指派的table RRS feed

  • 問題

  • MSSQL2008 裏

    有個user定期需要create table ,procedure

    但不想給他太大權限

    可是如果只開

    grant create table,create procedure,create view to TheUser

    好像也不能建table

    由於table時間還沒到,還沒建立也沒法指定 權限給user

    然後如果給

    grant select,insert,execute,alter on schema::dbo  to TheUser
    這樣一來全部都開了,我也想過用schema來處理,很多舊程式只能存取dbo的

    不知各位先進有無好方法

    可以開放 create table,create procedure,create view to TheUser

    然後讓他只能存取固定的table

    2012年6月11日 上午 06:01

解答

  • MSSQL2008 裏

    有個user定期需要create table ,procedure

    但不想給他太大權限

    可是如果只開

    grant create table,create procedure,create view to TheUser

    好像也不能建table

    由於table時間還沒到,還沒建立也沒法指定 權限給user

    然後如果給

    grant select,insert,execute,alter on schema::dbo  to TheUser
    這樣一來全部都開了,我也想過用schema來處理,很多舊程式只能存取dbo的

    不知各位先進有無好方法

    可以開放 create table,create procedure,create view to TheUser

    然後讓他只能存取固定的table

    關於只能存取自己建立的Table,你可以透過DDL Trigger來達成。在DDL Trigger中指定Create Table的事件,透過eventdata()組成GRANT權限的SQL statement,就可以達到動態指定自訂物件的能力。以下DDL Trigger給您參考:

     

    create trigger [trgDDLChange]
    on database

    with ENCRYPTION ,
    execute as caller
    for alter_table, create_table, drop_table, drop_index, create_index, alter_index

    as
    declare @data xml
    declare @original_login sysname
    declare @login_name sysname
    declare @user_name sysname
    declare @event_type nvarchar(100)
    declare @object_name nvarchar(200)
    declare @sql nvarchar(max)
    set @original_login = ORIGINAL_LOGIN()
    set @login_name = SUSER_NAME()
    set @user_name = CURRENT_USER
    set @data  = eventdata()

    insert into msdb.dbo.DDL_EVENT_LOG(POST_TIME, ORI_LOGIN_NAME, LOGIN_NAME, [USER_NAME], EVENT_TYPE, SQL_CMD, EVENT_DATA)
    values(
    getdate(),
    @original_login,
    @login_name,
    @user_name,
    @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(4000)'),
    @data
    )
    set @event_type = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')
    set @object_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
    if @event_type = 'CREATE_TABLE' 
    begin

    set @sql = 'grant select, insert, update, delete on ' + @object_name + ' to MyUser'
    exec sp_executesql @sql

    end


    • 已編輯 zerome 2012年6月13日 上午 02:32
    • 已標示為解答 chk 2012年6月13日 上午 02:34
    2012年6月13日 上午 02:31

所有回覆

  • You may specify dbo as default schema for the user, but difficult to limit permission to specific table that's not exist yet.
    2012年6月11日 下午 03:05
  • MSSQL2008 裏

    有個user定期需要create table ,procedure

    但不想給他太大權限

    可是如果只開

    grant create table,create procedure,create view to TheUser

    好像也不能建table

    由於table時間還沒到,還沒建立也沒法指定 權限給user

    然後如果給

    grant select,insert,execute,alter on schema::dbo  to TheUser
    這樣一來全部都開了,我也想過用schema來處理,很多舊程式只能存取dbo的

    不知各位先進有無好方法

    可以開放 create table,create procedure,create view to TheUser

    然後讓他只能存取固定的table

    關於只能存取自己建立的Table,你可以透過DDL Trigger來達成。在DDL Trigger中指定Create Table的事件,透過eventdata()組成GRANT權限的SQL statement,就可以達到動態指定自訂物件的能力。以下DDL Trigger給您參考:

     

    create trigger [trgDDLChange]
    on database

    with ENCRYPTION ,
    execute as caller
    for alter_table, create_table, drop_table, drop_index, create_index, alter_index

    as
    declare @data xml
    declare @original_login sysname
    declare @login_name sysname
    declare @user_name sysname
    declare @event_type nvarchar(100)
    declare @object_name nvarchar(200)
    declare @sql nvarchar(max)
    set @original_login = ORIGINAL_LOGIN()
    set @login_name = SUSER_NAME()
    set @user_name = CURRENT_USER
    set @data  = eventdata()

    insert into msdb.dbo.DDL_EVENT_LOG(POST_TIME, ORI_LOGIN_NAME, LOGIN_NAME, [USER_NAME], EVENT_TYPE, SQL_CMD, EVENT_DATA)
    values(
    getdate(),
    @original_login,
    @login_name,
    @user_name,
    @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(4000)'),
    @data
    )
    set @event_type = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')
    set @object_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
    if @event_type = 'CREATE_TABLE' 
    begin

    set @sql = 'grant select, insert, update, delete on ' + @object_name + ' to MyUser'
    exec sp_executesql @sql

    end


    • 已編輯 zerome 2012年6月13日 上午 02:32
    • 已標示為解答 chk 2012年6月13日 上午 02:34
    2012年6月13日 上午 02:31