An example of setting up security

Suppose you have decided to assign special roles to the users listed in Table 13-2.

**Table 13-2: Users to whom you will assign roles**
Name	Priviledges	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
Alan Johnson	dbo	ajohnson

Table 13-2: Users to whom you will assign roles

Name

Priviledges

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

Alan Johnson

dbo

ajohnson

Table 13-3 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 13-2. After logging in to the operating system, you would issue these commands using the initial “sa” account.

**Table 13-3: 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 18, “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. Alan is not granted any system-defined roles.
use master sp_addlogin ajohnson, j06n50n, @fulname = "Alan Johnson" create database sales_summary use sales_summary sp_changedbowner ajohnson sp_modifylogin ajohnson, 'defdb', sales_summary	Creates a new database sales_summary and makes Alan the owner of this database. Because he is the database owner, Alan can now create users, create new database objects and grant permissions to other users in this database.
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 13-3: 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 18, “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. Alan is not granted any system-defined roles.

use master

sp_addlogin ajohnson, j06n50n, @fulname = "Alan Johnson"

create database sales_summary

use sales_summary

sp_changedbowner ajohnson

sp_modifylogin ajohnson, 'defdb', sales_summary

Creates a new database sales_summary and makes Alan the owner of this database. Because he is the database owner, Alan can now create users, create new database objects and grant permissions to other users in this database.

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.