Sybase IQ Database Administration and User Security

1. 10 Administration etc.

2. User Admin. And Security

3. Data Security • Sybase IQ allows privacy of appropriate information within the database • Security is implemented at various levels: • Users • Groups • Object/command-level permissions

4. Users • You should set up individual user IDs for each user • Lets you tell which connections are which users • DBA (Database Administrator or database owner) has authority to add new users

5. Users and Groups • User • A login can access a database • Groups • Enables a collective name for a set of users • Simplifies administration • Users in groups • SYS group • PUBLIC group

6. Special User • DBA User • This is the initial user in a database • Password is SQL • Responsible for the overall management of the database • Adds new users and allocates permissions • Authority to create all database objects • Has DBA Authority • Has Resource Authority

7. Authorities • DBA Authority • Overall authority for database management • Manages users and permissions in the database • Required for Create, Backup and Restore database commands • May be granted to other users • Resource Authority • Required to create objects in a database • May be granted to other users by a user with DBA Authority

8. Adding Users to the Server • There are three methods to add users to your server • Grant Connect • Sybase Central Wizard • sp_iqaddlogin • Any of these methods may be used • sp_iqaddlogin provides more functionality for user and password management

9. Creating Users - Grant Connect • DBISQL • Syntax: • GRANT CONNECT TO userid [,...] IDENTIFIED BY password [,...] • Example: • GRANT CONNECT TO laura_smith IDENTIFIED BY secret;

10. Changing a Password • Syntax: • GRANT CONNECT TO userid [,...] IDENTIFIED BY password [,...] • Notice this is the same command to add a new user • For this reason, if you inadvertently enter the user ID of an existing user when you mean to add a new user, you are actually changing the password of the existing user • No warning is issued since this behavior is considered normal

11. Creating Users with Sybase Central • 1. Connect to the database • 2. Click Users and Groups folder • 3. Double-click Add User • 4. Follow wizard Continued …

12. Managing User Accounts • Stored Procedures for Managing User Accounts • sp_iqaddlogin • sp_iqprocess_login • sp_iqdroplogin • sp_iqlistexpiredpasswords • sp_iqlistlockedusers • sp_iqlistpasswordexpirations • sp_iqlocklogin • sp_iqmodifyadmin • sp_iqmodifylogin • sp_iqpassword • Examples of some to follow….

13. Add New User - sp_iqaddlogin • Syntax: • sp_iqaddlogin (loginname, password, [number_of_connections] [,password_expiration])

14. sp_iqaddlogin • This procedure has several functions • Add a new user and assign a password • Limit the number of connections for a user (optional) • 0 is default (no limit) • Establish a password expiration interval (optional) • Interval is in days • 0 is default (no expiration) • Allowable range is 0 to 32,767

15. Managing Users • You may enable user administration to provide more control over users • To enable this functionality execute: sp_iqmodifyadmin enable • After 'enabling', users added with sp_iqaddlogin will be listed in a user administration table: IQ_USER_LOGIN_INFO_TABLE

16. IQ_SYSTEM_LOGIN_INFO_TABLE • Contains one row with the default values for IQ User Administration • Used for connection and password control • The user_admin_enable field indicates if the IQ User Administration is enabled

17. sp_iqmodifyadmin • Enables user administration and modifies account information in IQ_SYSTEM_LOGIN_TABLE • Syntax: sp_iqmodifyadmin ({[enable|disable|user_connections| db_connections|password_expiration|password_warning]},[value])

18. sp_iqmodifylogin • Manipulates data in the IQ_USER_LOGIN_INFO_TABLE • Syntax: • sp_iqmodifylogin ({loginname | all overrides }, option, value)

19. Managing Password Expiration • Two stored procedures • To add or change a password for an IQ user account • sp_iqpassword(caller_password, new_password [,loginname]) • caller_password is the old password or the DBA’s password • To list all users with expired passwords • sp_iqlistexpiredpasswords

20. Locking User Accounts • To lock an IQ user account so that user cannot log in use: • sp_iqlocklogin (loginname, [lock | unlock ]) • To list user accounts that are locked out of the database: • sp_iqlistlockedusers

21. Failed Connection

22. sp_iqdroplogin • To drop an IQ user account from the database and User Administration • sp_iqdroplogin (userid)

23. Groups • Groups are commonly used in Sybase IQ databases • A Group typically owns all (or some) database objects • Rather than the DBA • Facilitates managing permissions

24. Special Groups • SYS group • Owner of all system tables and views for the database • Automatically created, no password • Cannot connect to the database as SYS • PUBLIC group • Member of the SYS group with read access for some of the system tables and views • Any new user is automatically a member of the PUBLIC group • This is configurable • Automatically created with connect permissions to the database • Can use REVOKE to remove membership in these groups

25. Creating Groups • DBISQL • Syntax: • GRANT GROUP TO userid • Allows the user to have members • Example: • GRANT CONNECT • TO marketing; • GRANT GROUP TO marketing;

26. Adding a User to a Group • DBISQL • Syntax: • GRANT MEMBERSHIP IN GROUP group-name TO userid • Example: • GRANT MEMBERSHIP IN GROUP marketing TO laura_smith • Must have already granted connect permission to laura_smith

27. Table and View Permissions • Syntax: • GRANT { ALL | ALTER | DELETE | INSERT | REFERENCES • | UPDATE | SELECT [ ( column-name [,...])] • ... ON table-name TO userid [,...] • [WITH GRANT OPTION] • Example: • GRANT SELECT ON customer • TO laura_smith

28. Stored Procedure Permissions • Must have EXECUTE permission to execute a stored procedure • Similar to granting permissions on tables and views • Example: • GRANT EXECUTE • ON my_procedure • TO laura_smith • Procedures execute with the permissions of their owners • Must be done individually on each stored procedure • i.e. Cannot use wildcards

29. REVOKE Statement • The REVOKE statement is the exact converse of the GRANT statement • Example: • REVOKE SELECT ON customer FROM laura_smith

30. Permissions Hierarchy

31. Database Object Names and Prefixes • Every database object is an identifier; up until now all database objects have been referred to using their simple name • Example: • SELECT * from employee • Tables, procedures and views all have an owner • Example: • SELECT * from "DBA".employee • When referring to a database object, a prefix is required unless: • You are the owner of the object • The database object is owned by a group ID of which you are a member

32. Backup

33. Backup • Three Types of Backup • FULL backup • INCREMENTAL backup • INCREMENTAL-since-FULL backup • All will backup the IQ Store and the Catalogue Store • The IQ Temporary Store is never backed up

34. Backup Types • FULL causes a full backup of the Catalogue and IQ Stores • INCREMENTAL makes a full backup of the Catalogue Store, and backs up all changes to the IQ Store since the last IQ backup of any type • INCREMENTAL SINCE FULL makes a full backup of the Catalogue Store, and backs up all changes to the IQ Store since the last FULL IQ backup

35. 7b. 7a. 6. 5. 4. 2. 3. 1. If there are – backup the changed pages Then go to 5. If there are no committed TX Checkpoint,Delete Log and Stop Check for Completed TX. Write Catalog to Tape(s) Write Marker into Log Write Marker into Log Write (Changed) Data To Tape(s) Issue Checkpoint Backup Processing • Backup backups committed data only • Backup begins and ends with a checkpoint • Any data that is committed during the backup process will be included in the backup • The transaction log is not backed up, it is deleted after a successful backup IQ Store Catalog Store Trans. Log Tape Drive(s)

36. Transaction Log • The log is deleted or renamed after a backup completes • Not needed for a Restore • If there are ASA tables in the catalog store (other than system tables) you must retain the log to recover transactions for this data

37. Restore • Before Restore • You must have dba authority • No users on the database • The database server must be running • To ensure exclusive access start the server with the -gd option (-gd dba)

38. Restore Restrictions • For incremental restores there must be no user access between restores • Restore will create and drop dbspaces as required

39. When to do Restores • If the database is corrupt - you must do a restore • If the last backup was FULL restore the FULL backup only • IF you have an INCREMENTAL SINCE FULL backup that precedes the database failure then restore the FULL followed by the INCREMENTAL SINCE FULL

40. Checking the Database

41. sp_iqcheckdb • sp_iqcheckdb ‘mode target […] [resources resource-percent]’ • Checks the consistency of (and optionally repair) a specified table, index, or the entire database • Parameter string must be enclosed in single quotes (<255 bytes) • mode: • allocation | check | verify | repair • target: • database | table table-name | index index-name • resource-percent: (optional) • The resources percentage allows you to limit the CPU utilization of the database consistency checker Continued …

42. sp_iqcheckdb - Check Mode • Reads all data pages and checks allocation problems and index inconsistencies • Target may be the database, a table, an index • If a table, it will check all indexes for that table • Examples: • sp_iqcheckdb ‘check database’ • sp_iqcheckdb ‘check table t1’ • sp_iqcheckdb ‘check index t1c1hng’ Continued …

43. sp_iqcheckdb - Verify Mode • Performs inter-index consistency check as well as all checks performed in ‘check’ mode • Contents of non-FP indexes checked against FP indexes • Runs longer than Check Mode • Examples: • sp_iqcheckdb ‘verify database’ • sp_iqcheckdb ‘verify table t1’ • sp_iqcheckdb ‘verify index t1c1hng’ Continued …

44. sp_iqcheckdb - Allocation Mode • Checks that each database block is allocated correctly according to internal page mapping structures (blockmaps) • Does not check index consistency or all allocation structures • Should be run after a forced recovery • Runs fast than Check Mode • Examples: • sp_iqcheckdb ‘allocation database’ • sp_iqcheckdb ‘allocation table t1’ • sp_iqcheckdb ‘allocation index t1c1hng’ Continued …

45. sp_iqcheckdb - Repair Mode • Runs detailed index checking and can repair many types of index inconsistencies • No allocation checking • Will report indexes repaired • Must issue a COMMIT after running to complete repairs • Database backup is recommended before running • Examples: • sp_iqcheckdb ‘repair database’ • sp_iqcheckdb ‘repair table t1’ • sp_iqcheckdb ‘repair index t1c1hng’

46. sp_iqcheckdb - Output Report • Reports an extensive list of statistics and all errors • Errors are flagged with ‘*****’ • If errors are reported some statistics may be inaccurate • Output is reported to the IQ Message File • “Future Version Errors” message • This message indicates DDL was performed during the check • Leaked Blocks will not be reported and any new objects will not be checked • “Leaked Blocks” are blocks that are allocated but not used

47. Checkdb Output DB Statistics Value Flags ---------------------------------- -------------- IQ DBCC Verification Summary Total Blocks In Database 20480 Blocks Marked as In Use 1026 Blocks Verified as In Use 1026 Blocks Detected as Duplicates 0 Blocks Detected as Unowned 0 Blocks Detected as Leaked 0 Tables Verified 9 Indexes Verified 74 Join Indexes Verified 0

48. Database Administration • New Option for IQ Monitor output files • You may specify a directory for IQ Monitor output files • DBCC Progress Messages • Messages report progress of sp_iqcheckdb in theIQ Message File • New database option DBCC_Log_Progress = 'On' • Dbspace Management Enhancements • Details will following slides

49. Checking the database • There are 3 procedures that check the system “metadata” in addition to SP_IQCHECKDB • These are • SP_IQDBSIZE • SP_IQTABLESIZE • SP_IQINDEXSIZE • These procedures not only report on sizes, they check on the objects, and will fail if there is an error • At this point the procedure SP_IQCHECKDB should be executed

50. Warning • Just because the procedures execute cleanly does not guarantee that the object is clean, but it is a good quick check • If you suspect there is corruption in the database you should run SP_IQCHECKDB first • But this will take time • If SP_IQCHECKDB finds corruption then run the other procedures to try and localize the corruption – or run SP_IQCHECKDB with the fix options on