Wednesday, March 7, 2012

Permission tracking

Hi all,
i'm facing the following situation: user rights must be granted for a very
short period of time, so that the user does his insert/update/delete job.
The problem is that there are many users who have requests and giving &
revoking permissions are taking a lot of time. Did anybody implemented an
automated tracking system, and if yes, could you give me a hint please? A
good way to implement permission tracking would be triggers but unluckily,
in Sql 2000, they can't be used on system tables...:-(
--
Tudor Sofron
MCSE, MCSA
Ipsos- NMRTudot
I am not sure what did you mean?
Do you want the users to be able INSERT/UPDATE/DELETE operations for a short
time?
What is a short time ( one an hour, two minutes) ?
I suggest you using STORED PROCEDURES for security reasons. Don't grant
access on underlying tables ,instead grant EXECUTE permissions for the users
on stored procedures that they need to be run.
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> i'm facing the following situation: user rights must be granted for a very
> short period of time, so that the user does his insert/update/delete job.
> The problem is that there are many users who have requests and giving &
> revoking permissions are taking a lot of time. Did anybody implemented an
> automated tracking system, and if yes, could you give me a hint please? A
> good way to implement permission tracking would be triggers but unluckily,
> in Sql 2000, they can't be used on system tables...:-(
> --
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
>|||Well...
this is the table i've created:
CREATE TABLE [User_RightsGranted] (
[User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[User_ID] [smallint] NULL ,
[User_SID] [varbinary] (85) NULL ,
[DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
[Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[Db_Owner] [tinyint] NULL ,
[Data_Reader] [tinyint] NULL ,
[Data_Writer] [tinyint] NULL ,
[Exec] [tinyint] NULL ,
[Select] [tinyint] NULL ,
[Insert] [tinyint] NULL ,
[Update] [tinyint] NULL ,
[Delete] [tinyint] NULL ,
[DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_DateS
tart]
DEFAULT (getdate()),
[DateEnd] [datetime] NULL ,
[OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDate]
DEFAULT
(getdate()),
[OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
host_name())
) ON [PRIMARY]
GO
The column names are pretty explicit so it's no need to explain their
function. The problem I have is that I can't automatize the whole process,
so I have to complete the table manually. So...did anybody faced such
problems, and if yes, how did you solved them? See my comments to your post
below...
Thanks,
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> Tudot
> I am not sure what did you mean?
> Do you want the users to be able INSERT/UPDATE/DELETE operations for a
short
> time?
yes. I grant users INSERT/UPDATE/DELETE rights and complete the above table.
> What is a short time ( one an hour, two minutes) ?
about 30 minutes, after that I revoke the granted permissions...but I have
to do that manually...and update a similar table...

> I suggest you using STORED PROCEDURES for security reasons. Don't grant
> access on underlying tables ,instead grant EXECUTE permissions for the
users
> on stored procedures that they need to be run.
well...it's not that easy to implement the use of sp's...
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:uc9kgMXxEHA.1988@.TK2MSFTNGP12.phx.gbl...
very[vbcol=seagreen]
job.[vbcol=seagreen]
an[vbcol=seagreen]
A[vbcol=seagreen]
unluckily,[vbcol=seagreen]
>|||Tudor
I am sorry but the table looks like a mess.
There is no primary key, lots of colums does allow NULL's

> The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually
You mean that you would like to insert into the table all DML that users do?
If so, create a trigger on this table and start to manipuilate with DELETED
and INSERTED virtual tables
In my opinion I'd create an AUDIT table that will be gathering all info
about new/old columns
Something like that
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
> Well...
> this is the table i've created:
> CREATE TABLE [User_RightsGranted] (
> [User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

,
> [User_ID] [smallint] NULL ,
> [User_SID] [varbinary] (85) NULL ,
> [DB_Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
> [Object_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Db_Owner] [tinyint] NULL ,
> [Data_Reader] [tinyint] NULL ,
> [Data_Writer] [tinyint] NULL ,
> [Exec] [tinyint] NULL ,
> [Select] [tinyint] NULL ,
> [Insert] [tinyint] NULL ,
> [Update] [tinyint] NULL ,
> [Delete] [tinyint] NULL ,
> [DateStart] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_Da
teStart]
> DEFAULT (getdate()),
> [DateEnd] [datetime] NULL ,
> [OpDate] [datetime] NOT NULL CONSTRAINT [DF_User_Rights_OpDat
e] DEFAULT
> (getdate()),
> [OpUser] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> CONSTRAINT [DF_User_Rights_OpUser] DEFAULT (suser_sname() + '.' +
> host_name())
> ) ON [PRIMARY]
> GO
> The column names are pretty explicit so it's no need to explain their
> function. The problem I have is that I can't automatize the whole process,
> so I have to complete the table manually. So...did anybody faced such
> problems, and if yes, how did you solved them? See my comments to your
post
> below...
> Thanks,
> Tudor Sofron
> MCSE, MCSA
> Ipsos- NMR
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eUVFySXxEHA.2040@.tk2msftngp13.phx.gbl...
> short
> yes. I grant users INSERT/UPDATE/DELETE rights and complete the above
table.
> about 30 minutes, after that I revoke the granted permissions...but I have
> to do that manually...and update a similar table...
>
grant[vbcol=seagreen]
> users
> well...it's not that easy to implement the use of sp's...
> very
> job.
&[vbcol=seagreen]
> an
please?[vbcol=seagreen]
> A
> unluckily,
>|||well...the table design is in 'development phase' :-)...but i hope that soon
this will be done.
Tudor Sofron
MCSE, MCSA
Ipsos- NMR
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuqlkwXxEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Tudor
> I am sorry but the table looks like a mess.
> There is no primary key, lots of colums does allow NULL's
>
>
> You mean that you would like to insert into the table all DML that users
do?
> If so, create a trigger on this table and start to manipuilate with
DELETED
> and INSERTED virtual tables
> In my opinion I'd create an AUDIT table that will be gathering all info
> about new/old columns
> Something like that
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
>
>
> "Tudor Sofron" <tsofron@.cluj.astral.rom> wrote in message
> news:%23QH8akXxEHA.3896@.TK2MSFTNGP10.phx.gbl...
NULL[vbcol=seagreen]
> ,
,[vbcol=seagreen]
,[vbcol=seagreen]
process,[vbcol=seagreen]
> post
> table.
have[vbcol=seagreen]
> grant
a[vbcol=seagreen]
giving[vbcol=seagreen]
> &
implemented[vbcol=seagreen]
> please?
>

No comments:

Post a Comment