Skip to Content

Activation of dbroles with passwords

Aug 31, 2017 at 07:53 AM


avatar image


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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Bart Van Kuijk
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

10 |10000 characters needed characters left characters exceeded
Per Otto Westheim 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

10 |10000 characters needed characters left characters exceeded
Bart Van Kuijk
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?



10 |10000 characters needed characters left characters exceeded