Permission settings

Permission settings

Post by <ra.. » Fri, 06 Mar 1998 04:00:00



I have a large database with plenty of objects (tables, sp's, ... more than
1600). Now want to setup a user group that is only allowed to select data
from a small subset of the whole database. If I create a new group
g_readonly, it gets by default the permissions of the public group. Does
this mean I have to manually revoke all UPDATE, DELETE, INSERT, EXECUTE
permissions for each single database object. Is there a possibility to do
this at once. Even with the server manager this seems to be a tedious
process.

I also had the idea to write a stored procedure that goes through all
objects in sysobjects and makes the required grant/revoke statements for
each object. But with this procedure I have to problem of how to put
together the grant statement with a variable tablename. Any idea?

Andy


 
 
 

Permission settings

Post by Matthew Roc » Fri, 06 Mar 1998 04:00:00



> I also had the idea to write a stored procedure that goes through all
> objects in sysobjects and makes the required grant/revoke statements for
> each object. But with this procedure I have to problem of how to put
> together the grant statement with a variable tablename. Any idea?

You could write the following select statement and then pass its results
back to the server (either manually or, with a bit more work,
automatically):

select "revoke all on "+name+" from g_readonly" from sysobjects where
type in ("U","P","V")

 
 
 

1. SQL 7 object permission setting

I am a novice user of SQL 7 but I want to restrict access to a
particular table to read only for a number of users.  The same table
will be fully available to other users.  The database is accessed
using Access 97 as a fronted linked via ODBC links.

When I make the permission changes to a particular user role by
checking the SELECT box, the user still has full access to the table!

Another strange note is every user's permission screen has every box
unchecked yet they can all access the database.

(Security is via SQL & NT, using sa without password)

Can anyone please help

2. Resizing a grid - a question

3. Multiple shared-memory segments / processor affinity

4. Do permission settings for tables - what is the DRI column considered for ?

5. Any Third Party Books For Paradox Engine?

6. Setting Global Permissions in SQL 2000

7. ODBC with output CHAR parameters

8. setting table permissions after CREATE TABLE

9. fails while setting role permissions using DSO

10. Permission/role setting?

11. Setting permissions