Skip to Content
avatar image
Former Member

Activation of dbroles with passwords


After a Audit of our Sybase ASE enviroment we have got a couple of findings. One is enabling password for roles. This to prevent users being able to grant their own granted roles to other users. It thought this was simply as adding a password to existing roles and it would be transparent and no user action required. What happen was that users got permission denied accessing the objects defined in the roles. I thought then that sa or a user with sa_role/sso_role could do it, but after digging into this it seems like to activate roles with passwords must be done by the users them self by running set role <rolename> with password 'somepw' on. That break the idea that password should be secret for the users. One option is to log in as the users with their password and activate the role, but of course this would be a major security breach and not really be practically doable. I have looked at login profiles but not really find how to achieve this. Also looked at granular permission, but this require a ASE_PRIVACY license which we do not have.

Any suggestions to solve this would be appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Aug 31, 2017 at 11:23 AM

    Hi Per

    Leaving the password issue aside, I don't think this statement is correct :

    "users being able to grant their own granted roles to other users."

    As far as I know there is no cascading permission that a login with a role can assign that role. It needs sso_role to assign roles.

    See sample below :

    isql -Usa (sa having sa_role / sso_role)

    sp_addlogin test_role, sybase
    sp_addlogin test, sybase
    create role role1
    grant role role1 to test_role
    alter login test_role add auto activated roles role1
    -- login test_role has got role1 automatically activated

    isql -Utest_role


    Role Name

    grant role role1 to test

    Msg 10353, Level 14, State 2:
    Server 'SP139', Line 1:
    You must have any of the following role(s) to execute this command/procedure:
    'sso_role' . Please contact a user with the appropriate role for help.

    Kind regards

    Bart van Kuijk

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 31, 2017 at 12:09 PM

    Hi Bart,

    Thanks for the answer. I was also thinking it was far-fetched that a user without sso_role could grant roles to other users. But since our Sybase environment is small and very stable I'm not longer 100% updated with what's possible or not after early ASE 15 versions. I have sent an email to the Auditor to get a confirmation where this requirement is defined or if it is somehow inherit from SQL Server or some other vendor. I also questioned if this finding applies to both system - and/or local customer created roles. That said, since there is a password option what could it be used and meant for, what could it really preventing ... what's the idea behind it.

    Below the finding from Auditor:


    Roles without passwords


    During our audit of settings for Sybase database, we have observed that roles in database is not protected with password.


    There is a risk that roles with critical rights being granted to and activated by unauthorized persons.


    If you have not tried to set a password for a role, this is what the users are prompted for at least from isql:

    isql -Utest -w300


    Msg 11141, Level 16, State 1:

    Server 'TestASE157':

    Password required to set role 'role_pw_test_ro' on.

    1> select count(*) from testdb..items

    2> go

    Msg 10330, Level 14, State 1: Server 'TestASE157', Line 1: SELECT permission denied on object items, database testdb, owner dbo

    1> set role role_pw_test_ro with passwd '******' on

    2> go

    1> select count(*) from testdb..items

    2> go


    2152 (1 row affected)

    1> exit


    Per Otto Westheim

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 31, 2017 at 12:54 PM

    Hi Per

    The role password is one option in an array of possible options to protect data. I am not quite sure how widely this used, but I have definately seen implementations of it.

    As simple example :
    Let's say there is a Team Lead. Most of the day he/she works likes the co-workers and uses a user-defined role set on by default: admin_operator. For certain tasks and/or at certain times of the day, there is a need to activate an elevated role for the Team Lead, a super_user_role. That elevated role can be protected with additional security by adding a password and clearly separate the 'daily' role from the 'team lead' role.

    More elaborately, this is a neat trick that requires a role password to work:

    Other might be able to add other use cases.

    Does this address the issues you were asking about?



    Add comment
    10|10000 characters needed characters exceeded