Creating a Read-Only Database User

From NewHaven Software Wiki

Jump to: navigation, search

Overview

Your CMS database is pre-configured with two database user accounts, dbo and cmsuser. The dbo (admin) user allows full access to the database including the ability to create/edit/drop tables, views, stored procedures, etc. The cmsuser database user, on the other hand, is a normal user with read/write permissions but cannot alter the database structure.

In some cases you may wish to create an additional user that has just read-only access and no write permissions. Typically this would be done for report writing although in many report writing situations you'd also want that user to have Execute permissions to make use of functions and stored procedures in the database. The SQL provided below can be run in Sybase's utility called dbisqlc.exe found in the \NewHaven Software\sa12\bin\ directory. Running this statement requires you be connected as the dbo database user.

SQL to Create the Read-Only Database User

Note the username and password around the 7th line. You may alter these to use whatever username and password you like. 

BEGIN
declare username varchar(128);
declare pass  varchar(128);
declare tablename varchar(255);
declare procname varchar(255);
 
SET username = 'CMSVIEW';
SET pass = 'CMSVIEW';
 
IF NOT EXISTS (SELECT 1 FROM sysuser WHERE user_name = username) then
  EXECUTE IMMEDIATE 'CREATE USER ' + username + ' IDENTIFIED BY ' + pass;
end IF;
 
FOR t AS table_cursor INSENSITIVE CURSOR FOR
SELECT ST.* 
FROM SYSTABLE ST 
JOIN SYSUSER SU ON SU.USER_ID = ST.CREATOR 
WHERE SU.USER_NAME = 'dbo'
DO  
  SET tablename = TABLE_NAME;
  IF EXISTS (SELECT 1 FROM SYSTABLEPERM p
      JOIN SYSUSER SU ON SU.USER_ID = p.grantee
      JOIN SYSTABLE ST ON st.table_id = p.stable_id 
      WHERE su.user_name = username AND st.table_name = tablename) then
    MESSAGE 'TABLE REVOKE ALL ON ' + tablename + ' FROM ' + username TO CLIENT;
    EXECUTE IMMEDIATE 'REVOKE ALL ON ' + TABLE_NAME + ' FROM ' + username;
  end IF;
  MESSAGE 'TABLE GRANT SELECT ON ' + tablename + ' TO ' + username TO CLIENT;
  EXECUTE IMMEDIATE 'GRANT SELECT ON ' + TABLE_NAME + ' TO ' + username;
END FOR;
 
FOR p AS proc_cursor INSENSITIVE CURSOR FOR
SELECT SP.* 
FROM SYSPROCEDURE SP 
JOIN SYSUSER SU ON SU.USER_ID = SP.CREATOR 
WHERE SU.USER_NAME = 'dbo'
DO
  SET procname = PROC_NAME;
  IF EXISTS (SELECT 1 FROM SYSPROCPERM  p
      JOIN SYSUSER SU ON SU.USER_ID = p.grantee
      JOIN SYSPROCEDURE sp ON sp.proc_id = p.proc_id
      WHERE su.user_name = username AND sp.proc_name = procname) then
    MESSAGE 'PROC REVOKE EXECUTE ON ' + procname + ' FROM ' + username TO CLIENT;
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON ' + PROC_NAME + ' FROM ' + username;
  end IF;
  MESSAGE 'PROC GRANT EXECUTE ON ' + procname + ' TO ' + username TO CLIENT;
  EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' + PROC_NAME + ' TO ' + username;
END FOR;
END
Retrieved from "http://wiki.newhavensoftware.com/index.php/Creating_a_Read-Only_Database_User"
Personal tools