Create Role and assign permissions in Sybase

Roles are collections of privileges that allow the role user to perform their jobs. Roles can be created and granted to users, login profiles, or other roles by a system security officer. There are 16 default roles in ASE.

sa_role
sso_role
oper_role
sybase_ts_role
navigator_role
replication_role
dtm_tm_role
ha_role
mon_role
js_admin_role
messaging_role
js_client_role
js_user_role
webservices_role
keycustodian_role
sa_serverprivs_role

Syntax to Create Role:

create role role_name [with passwd "password" [, {passwd expiration | min passwd length | max failed_logins} option_value]]

1. Create Role

create role test_role go

2. Grant SELECT permissions on table emp to newly created role

grant SELECT on emp to test_role go

3. Add role to user

grant role test_role to k2admin go sp_modifylogin 'k2admin', 'add default role', 'test_role' go

4. Check login information.

Suid: 3 Loginame: k2admin Fullname: k2admin Default Database: master Default Language: Auto Login Script: Configured Authorization: test_role (default ON) Locked: NO Date of Last Password Change: Sep 14 2017 12:42AM Password expiration interval: 0 Password expired: NO Minimum password length: 6 Maximum failed logins: 3 Current failed login attempts: 0 Authenticate with: AUTH_DEFAULT Login Password Encryption: SHA-256 Last login date: Sep 14 2017 1:01AM Exempt inactive lock: 0 (return status = 0)

5. Verification:

a) Logon to the server with above login
b) use data_name
c) run the select command on table emp
1> select * from emp
2> go

empno ename -------------------- ---------------------------------------- (0 rows affected)

Finally check roles assigned to users with below query.

select r1.name, r3.name
from syslogins r1, sysloginroles r2, syssrvroles r3
where r2.suid = r1.suid and
r3.srid = r2.srid
order by 1,2
go

Create Role and assign permissions in Sybase

By default, user-defined roles that are granted are not activated at login, but system roles that are granted are automatically activated, if they do not have passwords associated with them.

To set up a role to activate at login:
sp_modifylogin loginname, “add default role”, role_name

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: