Logins and Roles

Login's and Role's

The process of adding new logins to adaptive server is adding user to database and granting them permissions to access database objects divided among the roles

• Systems security officer
• system administrator
• database owner

Roles

• sa_role :: system administrator role
• sso_role :: system security office role
• oper_role :: operator role
• sybase_ts_role :: sybase technical support role

sa_role

• managing disk space
• monitoring adaptive server by automatic recovery procedure
• fine tuning adaptive server by changing configurable system parameters
• backing up and loading databases
• granting and revoking the system admin role
• modifying and droping several login accounts
• granting permissions to adaptive server users
• creating user database and granting ownership of them
• setting upgrades which can be used for granting and revoking permissions

sso_role

• creating server login accounts which includes assigning initial passwords
• changing the password of any length
• granting and revoking the SSO and OPERATOR role's
• setting the password expiration interval
• creating granting and revoking user defined roles
• managing the audit systems

oper_role

• Dumps and Loads

sybase_ts_role

• to run any dbccdb command we should have Sybase_ts_role
• sso uses sp_addlogin to create a server login
• sa or database owner uses sp_adduser to add a user to a database
• sa or dbo grants the user or a group to access specific commands and database objects

NOTE: Adding Login is Server wide entry

Adding User is database wide entry

To create a Login

Syntax sp_addlogin loginame, passwd [, defdb][, deflanguage] [, fullname] [, passwdexp]

[, minpwdlen] [, maxfailedlogins] [, auth_mech]

Parameters

• loginame is the user’s login name. Login names must conform to the rules for identifiers.
• passwd is the user’s password. Passwords must be at least 6 characters long. If you specify a shorter password, sp_addlogin returns an error message and exits. Enclose passwords that include characters besides A – Z, a – z, or 0 – 9 in quotation marks. Also enclose passwords that begin with 0-9 in quotation marks.
• defdb is the name of the default database assigned when a user logs into Adaptive Server. If you do not specify defdb, the default, master, is used.

** If we don't mention the default database it takes master database which is not recommended

• deflanguage is the official name of the default language assigned when a user logs into Adaptive Server. The Adaptive Server default language, defined by the default language id configuration parameter, is used if you do not specify deflanguage.
• fullname is the full name of the user who owns the login account. This can be used for documentation and identification purposes.
• passwdexp specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive.
• minpwdlen specifies the minimum password length required for that login. The values range between 0 and 30 characters.
• maxfailedlogins is the number of allowable failed login attempts. It can be any whole number between 0 and 32767.
• auth_mech defines the authentication mechanism.

Example

sp_addlogin robertw, terrible2, public_db, null, "Robert Willis"

here Adaptive Server login for “robertw.”

His password is “terrible2.”

his default database is public_db,

his full name is “Robert Willis.” and language is given as null (should not be enclosed in

quotes)

** for values which are not known can be assigned as null

To Modify login

Modifies the default database, default language, default role activation, login script, full name, the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified Adaptive Server login account.

Syntax sp_modifylogin loginame,option,value

Example sp_modifylogin claire, deflanguage, "french"

Parameters

• loginame is the login account to be modified.
• option specifies the name of the option to be changed. Table 1-16 lists valid options and their descriptions.
• value is the value of the option you specified for the option parameter. The value parameter is a character datatype; therefore, quotes are required for positive and negative numeric values.

To lock login

Locks an Adaptive Server account so that the user cannot log in, or displays a list of all locked accounts.

Syntax sp_locklogin login | all | NULL | wildcard_string , "lock" |"unlock",

[ except_login_name | except_role_name ]

Example sp_locklogin charles, "lock"

Parameters

• sp_locklogin without any parameters, displays all locked logins.
• loginame is the name of the account to be locked or unlocked.
• all Locks all logins except the sa_role.
• wildcard_string is any string with wildcards that identifies a set of logins.
• lock | unlock specifies whether to lock or unlock the account.
• except_login_name is the name of login that is exempted from being locked.
• except_role_name is the name of role that is exempted from being locked. For example, all logins in a role that are to be exempted.

To drop a login

Before dropping the login we should drop the related user from that particular database Drops an Adaptive Server user login by deleting the user’s entry from master.dbo.syslogins.

Syntax sp_droplogin loginame

Example sp_droplogin victoria

Parameters

• loginame is the name of the user, as listed in master.dbo.syslogins.

To see details of login

Displays information about a login account. Also displays information about the hierarchy tree above or below the login account when you so specify.

Syntax sp_displaylogin [loginame [, expand_up | expand_down]]

Parameters

• loginame is the user login account about which you want information if it is other than your own. You must be a System Security Officer or System Administrator to get information about someone else’s login account.
• expand_up specifies that Adaptive Server display all roles in the role hierarchy that contain the loginame role.
• expand_down specifies that Adaptive Server display all roles in the role hierarchy that are contained by the loginame role.

Example Displays information about your server login account:

1> sp_displaylogin 'sa'

2> go

Suid:1

Loginame: sa

Fullname:

Default Database: master

Default Language:

Auto Login Script:

Configured Authorization:

sa_role (default ON)

sso_role (default ON)

oper_role (default ON)

sybase_ts_role (default ON)

Locked: NO

Date of Last Password Change: Jul 26 2005 10:42AM

Password expiration interval: 0

Password expired: NO

Minimum password length: 6

Maximum failed logins: 0

Current failed login attempts:

Authenticate with: NONE

(return status = 0)

To Change the password for a login

Syntax sp_password caller_passwd, new_passwd,login name,immediate

Example sp_password test123 ,firstest

Parameters

• caller_passwd is your password. When you are changing your own password, this is your old password. When a System Security Officer is using sp_password to change another user’s password, caller_passwd is the System Security Officer’s password.
• new_passwd is the new password for the user, or for loginame. Configure the minimum password length with sp_configure minimum password length. The default is 6 bytes. Enclose passwords that include characters besides A-Z, a-z, or 0-9 in quotation marks. Also enclose passwords that begin with 0-9 in quotes.
• loginame the login name of the user whose account password is being changed by the System Security Officer.
• immediate specifies whether a password immediately takes effect on users who are logged in. The values are:

0 – users who are logged in keep their old passwords until they reconnect

1 – the password changes immediately in the syslogins table, and users who are logged in get their passwords updated while they are still logged in as

*** If we don't give the login name then it reflects to sybase server so be cautious while changing passwords

Adding a user to group

Adds a new user to current database

Syntax sp_adduser <loginame >,<user name or name_in_db >,<group name >

Example sp_adduser haroldq, harold, fort_mudge

parameters

• loginame is the user name in master.dbo.syslogins
• name_in_db is a new name for the user in current database
• grpname adds the user to an existing group in the database

*** By default if we don't specify a name the user will be created under public group

To Drop a user

To drop a user from a particular database we should be in the database where the user exists

Syntax sp_dropuser <username>

Example sp_dropuser majera01

** first we have to drop the user and then login only

Notes :

• sp_dropuser drops a user from the current database by deleting the user’s row from sysusers.
• You cannot drop a user who owns objects in the database.
• You cannot drop a user who has granted permissions to other users.
• You cannot drop the Database Owner from a database.
• If other users are aliased to the user being dropped, their aliases are also dropped. They no longer have access to the database.
• You cannot drop a user from a database if the user owns a stored procedure that is bound to an execution class in that database

To create a group

Groups are used as collective names in granting and revoking priilieges

Syntax sp_addgroup<grpname>

Example sp_addgroup accounting

• sp_addgroup adds the new group to a database’s sysusers table. Each group’s user ID (uid) is 16384 or larger (except “public,” which is always 0).
• A group and a user cannot have the same name.
• Once a group has been created, add new users with sp_adduser. To add an existing user to a group, use sp_changegroup.
• Every database is created with a group named “public”. Every user is automatically a member of “public”. Each user can be a member of one additional group.

To drop a group

Groups are used as collective names in granting and revoking priilieges

Syntax sp_dropgroup<grpname>

Example sp_dropgroup accounting

• Executing sp_dropgroup drops a group name from a database’s sysusers table.
• You cannot drop a group if it has members. You must execute sp_changegroup for each member before you can drop the group.

To get information about a group

sp_helpgroup

Reports information about a particular group or about all groups in the current database.

Syntax sp_helpgroup [grpname]

Examples

Displays information about all groups in the current database:

sp_helpgroup

Group_name Group_id

--------------- --------

hackers 16384

public 0

Displays information about the group “hackers”:

sp_helpgroup hackers

Group_name Group_id Users_in_group Userid

----------- --------- -------------- ------

hackers 16384 ann 4

hackers 16384 judy 3

• To get a report on the default group, “public,” enclose the name “public” in single or double quotes (“public” is a reserved word).
• If there are no members in the specified group, sp_helpgroup displays the header, but lists no users, as follows:

Group_name Group_id Users_in_group Userid

----------- --------- -------------- ------

To Create a Guest account

whenever a login is created in order to connect to database it checks for the user ,user has been created or not .

If the user is not created the login ,next check for the the guest user .By default the guest user has the privileges of dbo and is created in public group and you need to drop the quest user if not required anymore then use the database in which it is created and drop from it .

To create a guest account we need to use the database in which it is required

use <database>

go

create.................................

1>sp_adduser guest

2>go

The guest user can be removed with sp_dropuser, as discussed in “Dropping users”.

If you drop the guest user from the master database, server users who have not yet been added to any databases cannot log in to Adaptive Server.

Adding Alias account

Allows an Adaptive Server user to be known in a database as another user.

Normally we give dbo permissions to the login by using following

Syntax : sp_addalias loginame, name_in_db

Example : There is a user named “albert” in the database’s sysusers table and a login for a user named “victoria” in master.dbo.syslogins. This command allows “victoria” to use the current database by assuming the name “albert”:

sp_addalias victoria, albert

Parameters

• loginame is the master.dbo.syslogins name of the user who wants an alternate identity in the current database and this user must have an account in adaptive serer but cannot be a user in the current database
• name_in_db is the database user name to alias loginame to. The name must exist in both master.dbo.syslogins and in the sysusers table of the current database.

** whenever an alias is added to login ,a row will be updated in sysalternates table

Dropping alias

• After a user alias is dropped the user no longer has access to database
• we cannot drop an alias for a user who owns objects in the database that were created with version 12.0 later .
• You must drop the objects before you can drop alias

Syntax : sp_dropalias loginame

Example : Assuming that “victoria” was aliased (for example, to the Database Owner) in

he current database, this statement drops “victoria” as an aliased user from the database:

sp_dropalias victoria

Parameters

• loginame is the name (in master.dbo.syslogins) of the user who was aliased to another user.

Access and permissions

Two levels of access permission

• Server level → Create Login's
• Database level → Create User's

Server Level

• sp_addlogin
• we can assign role to login

Database Level

• db_name..sysusers
• db_name—sysalternates
• sp_adduser
• we will grant permission to the objects
• we assign groups in the database to design permission level

• when ever you create a login, an entry will be made in Master..syslogins
• while creation of user's in a database
  • file the login needs to be created
  • by default group is public ,this has some limited permissions

Granting and Revoking roles

To grant or revoke a role ,one should be “SA” or “SSO”

Syntax sp_role {"grant" | "revoke"}, rolename, loginame

Example Grants the System Administrator role to the login account named “alexander”:

sp_role "grant", sa_role, alexander

Parameters

• grant | revoke specifies whether to grant the role to or revoke the role from loginame.
• rolename is the role to be granted or revoked.
• loginame is the login account to or from which the role is to be granted or revoked.

To check the different types of roles available in the server

select * from syssrvroles

Creation of user defined roles

Use the create role command to create a role. The syntax is:

create role role_name [with passwd "password"]

Parameter

• role_name – is the name of a new role.
• password – is an optional password that must be specified by the user who will use the role.

Example

To create the intern_role without a password,

create role intern_role

To create the doctor_role and assign the password “physician”, enter:

create role doctor_role with passwd "physician"

To Check what roles are enabled for a login

sp_displaylogin “ loginid ”

go

Creating a group and Granting | Revoking of permission to a user in a particular database

Step 1: Create the group

sp_addgroup < group_name >

go

Step 2: Granting permission

Grant “ permission ”on “table ”to “user|group”

grant “ select ” on <table name > to read

grant “select,insert,delete,update” on “table ”to readwrite

grant “ all ” on <table name >to “user |group”

Checking what permission granted to a user

sp_helprotect <user id> or sp_helprotect <table name>

go

Checking what permission granted to an object

sp_helprotect <object name >

go