An example of setting up security

Suppose you have decided to assign special roles to the users listed in Table 9-3.

**Table 9-3: Users to whom you will assign roles**
Name	Role	Operating system login name
Rajnish Smith	sso_role	rsmith
Catharine Macar-Swan	sa_role	cmacar
Soshi Ikedo	sa_role	sikedo
Julio Rozanski	oper_role	jrozan

Table 9-3: Users to whom you will assign roles

Name

Role

Operating system login name

Rajnish Smith

sso_role

rsmith

Catharine Macar-Swan

sa_role

cmacar

Soshi Ikedo

sa_role

sikedo

Julio Rozanski

oper_role

jrozan

Table 9-4 shows the sequence of commands you might use to set up a secure operating environment for Adaptive Server, based upon the role assignments shown in Table 9-3. After logging in to the operating system, you would issue these commands using the initial “sa” account.

**Table 9-4: Examples of commands used to set up security**
Commands	Result
isql -Usa	Logs in to Adaptive Server as “sa”. Both sa_role and sso_role are active.
sp_audit “security”, “all”, “all”, “on”‘ sp_audit “all”, “sa_role”, “all”, “on” sp_audit “all”, “sso_role”, “all”, “on”‘	Sets auditing options for server-wide, security-relevant events and the auditing of all actions that have sa_role or sso_role active.
sp_configure “auditing”, 1	Enables auditing.
Before you enable auditing, set up a threshold procedure for the audit trail and determine how to handle the transaction log in sybsecurity. For details, see Chapter 11, “Auditing.”
sp_addlogin rsmith, js&2P3d, @fullname = "Rajnish Smith"	Adds logins and passwords for Rajnish, Catharine, Soshi, and Julio.
sp_addlogin cmacar, Fr3ds#1, @fullname = "Catharine Macar-Swan" sp_addlogin sikedo, mi5pd1s, @fullname = "Soshi Ikedo" sp_addlogin jrozan, w1seCrkr, @fullname = "Julio Rozanski"	A default database is not specified for any of these users, so their default database is master.
grant role sso_role to rsmith grant role sa_role to sikedo grant role sa_role to cmacar grant role oper_role to jrozan	Grants the sso_role to Rajnish, the sa_role to Soshi and Catharine, and the oper_role to Julio.
use sybsecurity sp_changedbowner rsmith	Grants access to the auditing database, sybsecurity, by making Rajnish, who is the System Security Officer, the database owner.
sp_locklogin sa,"lock"	Locks the “sa” login so that no one can log in as “sa”. Individuals can assume only the roles that are configured for them.
Do not lock the “sa” login until you have granted individual users the sa_role and sso_role roles and have verified that the roles operate successfully.

Table 9-4: Examples of commands used to set up security

Commands

Result

isql -Usa

Logs in to Adaptive Server as “sa”. Both sa_role and sso_role are active.

sp_audit “security”, “all”, “all”, “on”‘
sp_audit “all”, “sa_role”, “all”, “on”
sp_audit “all”, “sso_role”, “all”, “on”‘

Sets auditing options for server-wide, security-relevant events and the auditing of all actions that have sa_role or sso_role active.

sp_configure “auditing”, 1

Enables auditing.

Before you enable auditing, set up a threshold procedure for the audit trail and determine how to handle the transaction log in sybsecurity. For details, see Chapter 11, “Auditing.”

sp_addlogin rsmith, js&2P3d, @fullname = "Rajnish Smith"

Adds logins and passwords for Rajnish, Catharine, Soshi, and Julio.

sp_addlogin cmacar, Fr3ds#1, @fullname = "Catharine Macar-Swan"
sp_addlogin sikedo, mi5pd1s, @fullname = "Soshi Ikedo"
sp_addlogin jrozan, w1seCrkr, @fullname = "Julio Rozanski"

A default database is not specified for any of these users, so their default database is master.

grant role sso_role to rsmith
grant role sa_role to sikedo
grant role sa_role to cmacar
grant role oper_role to jrozan

Grants the sso_role to Rajnish, the sa_role to Soshi and Catharine, and the oper_role to Julio.

use sybsecurity
sp_changedbowner rsmith

Grants access to the auditing database, sybsecurity, by making Rajnish, who is the System Security Officer, the database owner.

sp_locklogin sa,"lock"

Locks the “sa” login so that no one can log in as “sa”. Individuals can assume only the roles that are configured for them.

Do not lock the “sa” login until you have granted individual users the sa_role and sso_role roles and have verified that the roles operate successfully.