Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Nested procedures, limited permissions & OBJECT_NAME

    Hello

    Can anyone explain this to me please?
    You will need:
    Some sort of sandbox db (in here it is called test)
    Mixed mode authentication, or modify the script for a windows account.

    Three code files:
    Code #1 (set up senario):
    Code:
    USE test
    GO 
    
    IF NOT EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.proc_one')) BEGIN
        EXEC('
    CREATE PROCEDURE  dbo.proc_one
    AS
    BEGIN
        SELECT @@PROCID, OBJECT_NAME(@@PROCID)
        EXEC dbo.proc_two
    END'
        )
    END
    GO
    
    IF NOT EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.proc_two')) BEGIN
        EXEC('
    CREATE PROCEDURE  dbo.proc_two
    AS
    BEGIN
        SELECT @@PROCID, OBJECT_NAME(@@PROCID)
    END'
        )
    END
    GO
    
    EXEC dbo.proc_one
    
    --DROP PROC dbo.proc_one
    --DROP PROC dbo.proc_two
    Two outputs each with one row & two populated columns.

    Code #2 (set up user with limited perms):
    Code:
    USE master
    GO 
    
    ------------------------------------------------------------
    --    VARIABLES
    ------------------------------------------------------------
    
        DECLARE      @sql                AS VARCHAR(8000)
                , @login_name        AS VARCHAR(100)
                , @user_name        AS VARCHAR(100)
                , @role_name        AS VARCHAR(100)
                , @password            AS VARCHAR(25)
                , @db                AS SYSNAME
        --Set to 1 if we are removing the user and login, 0 to create them
                , @clean_up_only    AS BIT
    
    ------------------------------------------------------------
    --    END VARIABLES
    ------------------------------------------------------------
    
    ------------------------------------------------------------
    --    SET VARIABLES
    ------------------------------------------------------------
    --SQL Server login: 
    SELECT      @login_name        = 'goPoots'
            , @user_name        = 'goPoots'
            , @role_name        = 'goPoots_role'
            , @password            = 'welovepoots'
            , @db                = 'test'
            , @clean_up_only    = 0
    ------------------------------------------------------------
    --    END SET VARIABLES
    ------------------------------------------------------------
    
    --Delete the login if it exists
    SELECT    @sql = 
    REPLACE(
    'IF EXISTS    (SELECT NULL FROM sys.server_principals WHERE name = ''[login_name]'' AND type_desc = ''SQL_LOGIN'')
        DROP LOGIN [login_name]'
    , '[login_name]'    , @login_name)
    
    EXEC    (@sql)
    
    IF @clean_up_only = 0 BEGIN--@clean_up_only = 0
    
        --Create the login
        SELECT    @sql = 
    REPLACE(REPLACE(REPLACE(
    'CREATE LOGIN [login_name] 
    WITH 
        PASSWORD = ''[password]''
        , DEFAULT_DATABASE = [db]
        , DEFAULT_LANGUAGE = british'
    , '[login_name]'    , @login_name)
    , '[password]'        , @password)
    , '[db]'            , @db)
    
        EXEC    (@sql)
    
    END--@clean_up_only = 0
    
    --remove the user from database
    SELECT    @sql = 
    REPLACE(REPLACE(REPLACE(
    'USE [db]; 
    IF EXISTS (SELECT NULL FROM sys.database_principals WHERE name = ''[user_name]'' AND type_desc = ''SQL_USER'') 
        DROP USER [user_name];
    IF EXISTS (SELECT NULL FROM sys.database_principals WHERE name = ''[role_name]'' AND type_desc = ''DATABASE_ROLE'') 
        DROP ROLE [role_name];'
    , '[user_name]'        , @user_name)
    , '[role_name]'        , @role_name)
    , '[db]'            , @db)
        
    EXEC    (@sql)
    
    IF @clean_up_only = 0 BEGIN--@clean_up_only = 0
    
        --Add the user & role to database
        SELECT    @sql = 
    REPLACE(REPLACE(REPLACE(REPLACE(
    'USE [db]; 
    CREATE USER [user_name] FOR LOGIN [login_name];
    CREATE ROLE [role_name];
    
    GRANT EXEC ON dbo.proc_one TO [user_name]    
    
    EXEC sp_addrolemember 
              @membername    =''[user_name]''
            , @rolename        = ''[role_name]'';'
    , '[login_name]'    , @login_name)
    , '[user_name]'        , @user_name)
    , '[role_name]'        , @role_name)
    , '[db]'            , @db)
    
        EXEC    (@sql)
    
    END--@clean_up_only = 0
    Our beloved poots gets limited access to [test]: exec permissions on the first of those sprocs created earlier.

    code #3 (our hero executes the sproc):
    Code:
    USE test
    GO
    
    EXECUTE AS USER = 'goPoots'
    
    EXEC dbo.proc_one
    Two outputs each with one row but the output from proc_one has the expected two populated columns, the output for proc_two has a NULL for OBJECT_NAME.

    WTF??? Anyone know why? If I grant exec to proc_two too then it works but that means exposing sprocs not meant to be exposed.
    Thank you Microshaft - I'm gonna download mySQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Oh,. man. What a long post. I think I need to take a break after slogging through all that. Oh lookie! It is lunch, now. Guess I can take that break after all. ;-)

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Noes!!!!!!
    I've done all the work for you Just paste stuff in, hit F5 and tell me what's wrong!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In order to use the OBJECT_NAME function, you need certain permissions. Probably VIEW DEFINITION, in order to see the name of proc_two.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    USE test
    go
    
    EXECUTE AS user = 'goPoots'
    GO
    SELECT has_perms_by_name('proc_one', 'OBJECT', 'EXECUTE')
    SELECT has_perms_by_name('proc_two', 'OBJECT', 'EXECUTE')
    SELECT has_perms_by_name('proc_one', 'OBJECT', 'VIEW DEFINITION')
    SELECT has_perms_by_name('proc_two', 'OBJECT', 'VIEW DEFINITION')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm.
    Code:
    USE test
    go
    
    EXECUTE AS user = 'goPoots'
    --GO
    
    SELECT    permission_name, has_perms_by_name('proc_one', 'OBJECT', permission_name)
    FROM    sys.fn_builtin_permissions('OBJECT')
    
    SELECT    permission_name, has_perms_by_name('proc_two', 'OBJECT', permission_name)
    FROM    sys.fn_builtin_permissions('OBJECT')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In case peeps can't be bothered running that, goPoots has EXEC (and nothing else) permissions on proc_one and no permissions on proc_two.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...also this implies VIEW DEFINITION is not inherited from EXEC:
    Code:
    SELECT    *
    FROM    sys.fn_builtin_permissions(DEFAULT)
    WHERE    permission_name = 'view definition'
            AND class_desc = 'OBJECT'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh you vicious ba....

    My copy of BoL might be out of date....

    Quote Originally Posted by ONLINE BoL
    Exceptions Returns NULL on error or if a caller does not have permission to view the object. If the target database has the AUTO_CLOSE option set to ON, the function will open the database.
    A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_NAME may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.
    http://technet.microsoft.com/en-us/l...1(SQL.90).aspx
    Bit in red is me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This thread is turning into a monologue. Can one of the mods tidy things up a tad? ;-)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dear Lord - this is why I advocate Instabans for this sort of thing - what more abuse must the humble servants of this board endure before sanity prevails?

    Also - GRANT VIEW DEFINITION sorts out the problem for those that made it this far.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One last post in my Blog.

    *I* think this is dead useful.

    Code:
    EXECUTE AS USER = 'goPoots'
    
    SELECT    'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.' + name + ' TO goPoots'
    FROM    sys.sql_modules
    INNER JOIN 
            sys.objects
    ON    sys.objects.object_id    = sys.sql_modules.object_id
    WHERE    has_perms_by_name(name, 'OBJECT', 'EXEC') = 0
            AND type_desc = 'SQL_STORED_PROCEDURE'
    EDIT - ok just realised that since I had to go to sys.objects for the schema I didn't need sql_modules anymore, but you get the idea.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •